How to Add a Line to a Bar Chart in Excel

Adding a line chart on top of a bar chart in Excel is a fantastic way to display two different types of data together. This "combo chart" lets you compare a primary value, like monthly sales, with a secondary value, like a sales target or a trend such as customer satisfaction. This article will walk you through exactly how to create this useful visualization, from setting up your data to customizing the final chart to look professional and clear.

Why Combine a Bar and Line Chart?

Before we jump into the steps, let's look at why this is so useful. Imagine you're tracking monthly website traffic. A bar chart is perfect for showing the total sessions for each month. But what if you also wanted to see the website's conversion rate for each of those months?

If you add conversion rate as another set of bars, it would be almost invisible next to the traffic numbers (e.g., 50,000 sessions vs. a 2% conversion rate). Your scales are completely different. The bar representing 2% would be so small you couldn't see it.

By putting the conversion rate on a line, you can show its trend across the months and give it its own axis to make it readable. This allows you to answer questions at a glance:

• Did our high-traffic months also have high conversion rates?

• Is there a relationship between our monthly ad spend (bars) and our return on investment (line)?

• How are our actual monthly sales (bars) performing against our sales target (line)?

This is a go-to technique for dashboard creators and analysts because it packs a lot of information into a single, easy-to-understand visual.

Step 1: Get Your Data Ready for Charting

The first and most important step is organizing your data in a simple table. Excel is smart, but it needs a clean structure to understand what you want to chart. For a combination chart, you’ll typically need at least three columns:

1. Column 1: The Labels (X-Axis). This is your time period, category, or whatever you are measuring. Examples: Month, Campaign Name, Product Category.

2. Column 2: The Bar Chart Data. These are the primary values you want to represent with bars. Examples: Total Sales, Website Sessions, Units Sold.

3. Column 3: The Line Chart Data. These are the secondary values you want to show as a line. Examples: Sales Target, Conversion Rate, Profit Margin %.

Here’s a sample data set we’ll use for this tutorial. It shows monthly sales revenue (which will be our bars) and the corresponding sales goals for each month (which will be our line).

Let's set it up in a worksheet like this:

Pro-Tip: Always use clear, simple headers in your first row. Excel will use these to automatically create legends and titles for your chart, saving you time later.

Step 2: Create a Basic Clustered Bar Chart

With your data set up, the next stage is to create a standard bar chart. Don't worry about the line just yet - we’ll transform it in the next step. For now, we just need to get the basic chart onto the worksheet.

1. Click and drag your mouse to select the entire data range, including the headers (in our example, cell A1 through C7).

2. Go to the Excel ribbon at the top of the screen and click on the Insert tab.

3. In the "Charts" section, you can click on the icon for a column/bar chart. However, the easiest way is to click on Recommended Charts. Excel is pretty good at guessing what you want.

4. A dialog box will pop up. Choose the first option, which is typically a Clustered Column chart. It will show you a preview of your chart with both "Actual Sales" and "Sales Target" as bars.

5. Click OK.

You should now see a basic bar chart on your sheet with two sets of colored bars for each month - one for sales and one for the target. It's functional, but not very clear. In the next step, we'll fix that.

Step 3: Convert One Data Series into a Line

This is where the magic happens. We're going to tell Excel to change the "Sales Target" bars into a single line that flows across the chart.

1. Right-click on one of the bars representing the data you want to turn into a line. In our case, right-click on any of the "Sales Target" bars (be careful not to click the "Actual Sales" bars).

2. From the menu that appears, select Change Series Chart Type...

3. This will open the Change Chart Type dialog box. This is the control center for creating combo charts. At the bottom, you'll see your two data series listed: "Actual Sales" and "Sales Target."

4. Next to "Actual Sales," leave the "Chart Type" as Clustered Column.

5. Go to the row for "Sales Target" and click the dropdown menu next to its name. Change it from Clustered Column to Line or Line with Markers. I recommend Line with Markers, as the markers help pinpoint the exact value for each month.

6. Click OK.

Instantly, your chart will update. You'll now see your monthly sales as vertical bars and your sales targets as a line running across them. You've successfully created a combo chart!

Step 4 (Optional but Powerful): Use a Secondary Axis

What if your two data sets use completely different scales? For our sales example, the scales are similar (both are in dollars), so a single vertical axis on the left (the Primary Axis) works perfectly fine.

But let's go back to our other example: Website Sessions (in the thousands) vs. Conversion Rate (a small percentage). If you plotted both on the same axis, the conversion rate line would be completely flat along the bottom of the chart.

The solution is a Secondary Axis - an extra vertical axis on the right side of the chart that is scaled specifically for your line data.

Here’s how to add one:

1. Right-click on your line chart and again select Change Series Chart Type... to open the combo chart dialog box.

2. At the bottom of the dialog box, where your data series are listed, you'll see a column of checkboxes named Secondary Axis.

3. For the data series you want on the new axis (in this hypothetical case, "Conversion Rate"), simply check the box in the "Secondary Axis" column.

4. Click OK.

Your chart will now have a left vertical axis for your bars (e.g., Website Sessions) and a right vertical axis for your line (e.g., Conversion Rate %). This makes both data sets easy to read and compare, even when their scales are worlds apart.

Step 5: Format Your Chart for Clarity

Your chart is now functional, but a little formatting will make it look professional and ready for any report or presentation.

Give a Descriptive Chart Title:Don’t leave it as "Chart Title." Double-click the title and change it to something that explains what the chart shows, like "Monthly Sales Performance vs. Target" or "Website Traffic and Conversion Rate."

Label Your Axes:If you're using a secondary axis, labeling both is extremely important. Click the green "+" sign next to your chart and check the box for Axis Titles. Then you can edit the text for the primary vertical axis (left), secondary vertical axis (right), and the horizontal axis (bottom).

Choose Your Colors:Right-click any of the bars or the line and select "Format Data Series." You can use the "Fill & Line" menu (it looks like a paint bucket) to change colors to match your company's branding or to improve contrast and readability.

Clean Up Clutter:Use the green "+" menu to remove elements you don't need. Sometimes, gridlines make a chart feel too busy. Unchecking the "Gridlines" box can give your visual a cleaner, more modern look.

Final Thoughts

Learning how to combine bar and line charts in Excel is a small effort that pays big dividends in your reporting. You can now clearly visualize two different metrics at once, spot trends, and communicate complex data in a simple, intuitive way for any audience.

While mastering tools like Excel is incredibly valuable, we created Graphed because we know that this process can still take time, especially when your data lives across different platforms. Instead of manually exporting data and building charts click-by-click, you can simply ask for what you want in plain English. For example, just connect your sources and say, "Show me last quarter's Shopify sales as a bar chart with a line for my sales goal from Google Sheets," and we build the live, updating dashboard for you instantly - no more manual report building needed.