How to Create a Bar and Line Chart in Excel

Combining a bar chart and a line chart in Excel lets you tell a more powerful story with your data, showing the relationship between two different kinds of metrics in one clear visual. It’s perfect for comparing a primary value, like total sales, with a secondary value, like a growth percentage or profit margin. This article will walk you through exactly how to prepare your data and build one of these combo charts, step-by-step.

Why Combine a Bar and a Line Chart in Excel?

A standard bar chart is great for comparing quantities across categories, like sales by product. A line chart is excellent for tracking a value over time, like website traffic over a year. But what happens when you want to see both at once? That’s where a combination - or “combo” - chart comes in. It lets you display two datasets on the same visual, but with different chart types and, importantly, different scales.

This is extremely useful for answering questions like:

• How did our monthly new customer count (bars) affect our customer acquisition cost (line)?

• As our sales revenue (bars) increased each quarter, what happened to our profit margin percentage (line)?

• How does our ad spend (bars) correlate with our website's conversion rate (line)?

By plotting primary 'volume' metrics as bars and secondary 'rate' or 'ratio' metrics as a line, you quickly spot trends, correlations, and potential issues that would be hidden if you looked at them separately.

Preparing Your Data for a Combo Chart

Before you create any chart in Excel, getting your data structure right is the most important step. A combo chart needs at least three columns: one for your labels (like dates or categories) and two for the numerical data you want to plot.

For a clean and easy charting process, set up your data in a simple table format. Make sure your headers are clear, as Excel will use them to automatically create and label parts of your chart. A common mistake is to have multiple header rows or merged cells, which can confuse Excel's charting engine.

Let's use a common marketing scenario: you want to track your monthly sales revenue and the corresponding profit margin percentage for the first half of the year. Your data should look like this:

Notice that Sales Revenue is in dollars (a large number) and Profit Margin is a percentage (a small number). If you tried plotting both of these on a standard bar or line chart, the profit margin numbers would barely register, looking like a flat line at the bottom. The combo chart solves this by giving each dataset its own vertical axis - one on the left for dollars and another on the right for percentages.

Step-by-Step Guide: How to Create a Bar and Line Chart in Excel

Once your data is neatly organized, creating the chart itself only takes a few clicks. Follow these steps to build your visual.

1. Select Your Data

Click and drag your cursor to highlight the entire data range you want to include in the chart. In our example, you would select cells A1 through C7, making sure you include your column headers. Including headers is a pro-tip because Excel will use them for chart labels, saving you manual work later.

2. Insert the Combo Chart

Navigate to the Excel toolbar and click on the Insert tab after selecting your data. In the Charts group, you'll find a small icon that looks like a combination of a histogram and a line chart, click it. This button is the Insert Combo Chart.

Once you click it, a dropdown menu will appear with a few combo chart options. You can hover over them to preview how they'll look with your data. The most common and useful one is labeled Clustered Column – Line on Secondary Axis. Select this one.

3. Customize Your Chart Setup

Excel will automatically open the Insert Chart dialog box, pre-selected to the "Combo" section. Here, you’ll see your data series (in our case, "Sales Revenue" and "Profit Margin %") listed with two dropdowns next to each: ‘Chart Type’ and ‘Secondary Axis’.

This screen is where you define how your chart will behave:

• For Sales Revenue, keep the Chart Type as "Clustered Column." Leave the "Secondary Axis" box unchecked. This tells Excel to use the primary vertical axis (on the left) for the dollar amounts.

• For Profit Margin %, change the Chart Type to "Line." Check the box next to it under the Secondary Axis column. This tells Excel to plot this data as a line and create a new vertical axis on the right side of the chart for the percentage values.

Once you've configured this, click OK. Excel will instantly generate your combination chart, with your sales data as bars read on the left axis and your profit margin as a line read on the right axis.

Making Your Combo Chart Easy to Understand

You have a chart, but it's not finished yet. A good chart is one that can be understood in seconds, so a little formatting goes a long way. Click anywhere on your new chart to bring up the Chart Design and Format tabs in the ribbon.

Add Clear Titles and Axis Labels

Your chart needs a clear story. Without proper labels, it’s just a collection of bars and lines.

• Chart Title: Double-click the chart title to edit it. Make it descriptive and concise, like "Monthly Sales Revenue vs. Profit Margin."

• Axis Titles: This is the most critical step for a combo chart. Click the chart, then click the green "+" icon that appears to the right. Check the box for "Axis Titles." A text box will appear for the vertical and horizontal axes.

  • Add a title for your primary vertical axis (left): "Sales Revenue ($)."

  • Add a title for your secondary vertical axis (right): "Profit Margin (%)."

This simple addition makes it immediately clear what each element represents and which scale relates to it.

Format Colors and Styles

To improve readability, make sure your bar and line colors have good contrast. You can right-click on any of the bars or the line and choose "Format Data Series." From the pane that opens, you can change the fill and line colors to match your brand or simply create stronger visual separation.

Add Data Labels

Instead of making colleagues guess exact values, add them directly to the chart. Right-click on your bars and select "Add Data Labels." Repeat the process for your line chart. The values will appear on top of or next to each data point, making your performance metrics crystal clear without needing to reference the axes.

Pro Tips for Your Excel Combo Charts

Ready to level up? Here are a couple of extra techniques to make your charts even more insightful.

Add a Target Line for Context

Comparing performance against a goal adds valuable context. What if you had a monthly sales target of $70,000? You can add this as a straight horizontal line on your chart.

To do this, simply add a new column to your source data called "Sales Target" and fill each row with the target value. Then, right-click your chart, click "Select Data," and add this new series. Excel might add it as another set of bars at first. Just right-click on the new bars, choose "Change Series Chart Type," find "Sales Target" in the list, and change its type to a "Line." Now you have a clear visual of which months met or exceeded the goal.

Use Excel Tables for Auto-Updating Charts

If you plan to add new data to your source table (e.g., adding July's data next month), turn your data range into an official Excel Table first. Select your data and press Ctrl + T. By basing your chart on a Table, it will automatically resize and include any new rows or columns you add, saving you from having to manually adjust the data source range every time.

Avoid Common Pitfalls

• Skipping the Secondary Axis: If you plot two series with vastly different scales (like dollars in the thousands and a percentage) on the same axis, one will be unreadable. Always use a secondary axis in these situations.

• Too Much Clutter: A combo chart works best with two, maybe three, data series. Any more than that and your chart becomes a tangled mess that is hard to interpret. Stick to showing the most important relationships.

Final Thoughts

Creating a bar and line combo chart in Excel is a fantastic way to compare disparate but related metrics in one simple, clear visual. It elevates your reporting from just showing numbers to revealing the story and relationships behind them, whether you're tracking sales against profit margins or ad spend against conversion rates. Mastering this skill gives you a powerful tool for any data analysis toolbox.

While being an Excel expert is useful, the reality of modern business means your data is everywhere - Google Analytics, Shopify, Salesforce, Facebook Ads, etc. Stitching that data together in a spreadsheet just to build a single chart can become a time-consuming weekly ritual. At Graphed, we automate all of that manual report-building. By connecting to your data sources directly, you can create real-time, interactive dashboards just by asking questions in plain English - no more downloading CSVs or wrestling with chart options. You can just ask "build a combo chart showing my Shopify revenue and Facebook ad spend by day for this month" and get a live, automated dashboard in seconds, letting you get straight to the insights.