How to Combine Bar and Line Graph in Excel

Ever wanted to show your monthly advertising spend alongside your click-through rate in a single, clean visual? Combining a bar graph and a line chart in Excel is the perfect way to do just that, creating what's known as a combo chart. This article walks you through why these charts are so useful and gives you a clear, step-by-step guide to building your own in minutes.

Why Combine a Bar and a Line Chart?

You're not just combining charts for fun - you're telling a deeper story with your data. A combo chart is incredibly effective when you want to visualize the relationship between two different types of data over the same period.

Think of it this way: the bar chart is great for showing quantities or volumes, like the total number of sales or website visits. The line chart excels at showing trends, rates, or percentages, like your profit margin or conversion rate. Putting them together lets you see how they influence each other at a glance.

Here are a few common business scenarios where a combo chart is the best tool for the job:

• Sales Revenue vs. Profit Margin: You can show your total monthly sales as bars and overlay your profit margin percentage as a line. This quickly reveals if higher-revenue months are also your most profitable ones.

• Website Traffic vs. Conversion Rate: Display monthly website sessions as bars and the e-commerce conversion rate as a line. Did that huge spike in traffic from a viral post actually lead to more sales, or did it attract the wrong audience?

• Marketing Spend vs. Leads Generated: Plot your monthly ad spend in bars with the number of qualified leads as a line. This helps you track the efficiency of your budget and see if more spending always equals better results.

The magic of the combo chart comes from using a secondary axis. This allows you to plot data with completely different scales on the same graph without one distorting the other. For instance, your revenue might be in the tens of thousands of dollars, while your conversion rate is a small percentage (e.g., 3.5%). Without a secondary axis, your conversion rate line would look like a flat line at the bottom of the chart, providing zero insight. With it, both data sets get their own scale, making the relationship between them instantly clear.

Setting Up Your Data in Excel

Before you create a single chart, getting your data organized correctly is the most important step. A clean setup will make the entire process smooth and error-free. You need at least three columns for a standard combo chart:

1. A column for your labels (e.g., months, quarters, campaign names). This will form your horizontal axis (the X-axis).

2. A column for the data you want to display as bars (e.g., Revenue, Website Sessions).

3. A column for the data you want to display as a line (e.g., Growth Rate, Conversion Rate %).

For example, let's say you're a marketing manager tracking campaign performance. Your data should look something like this:

Column A: MonthColumn B: Ad Spend ($)Column C: Conversion Rate (%)

Pro Tip: Keep it Simple and Clean

Ensure your data is formatted correctly before you start. Check for things like:

• Consistent Labels: Make sure "January" is spelled the same way every time.

• Numbers as Numbers: Your numerical data columns (like Ad Spend and Conversion Rate) should be formatted as numbers, currency, or percentages in Excel. Avoid having text mixed in with your numbers.

• No Empty Rows or Columns: Keep your data table as a single, contiguous block. Gaps can sometimes confuse Excel when it tries to select the data range for your chart.

Creating a Combo Chart: Step-by-Step Guide

Once your data is neatly arranged, creating the chart is a straightforward process. We'll use our Ad Spend and Conversion Rate example to walk through it.

Step 1: Select Your DataClick and drag your cursor to highlight the entire data range you want to include in the chart. In our example, this would be all the cells containing the months, ad spend figures, and conversion rates, including the headers.

Step 2: Insert a Combo ChartWith your data selected, navigate to the Insert tab on Excel's top ribbon. In the Charts section, look for the small icon that looks like a bar chart with a line running through it. This is the Insert Combo Chart icon.Click it, and a dropdown menu will appear. You can choose a pre-made combo chart, but for more control, select Create Custom Combo Chart... at the bottom. This opens the Insert Chart dialog box.

Step 3: Configure Your Chart and Secondary AxisThis is where you tell Excel how to handle your different data series. You'll see a window with your data series listed ("Ad Spend" and "Conversion Rate"). For each series, you have two key options:

• Chart Type: Use the dropdown menus to assign a chart type to each data series. Following our plan, we'll set "Ad Spend" to Clustered Column and "Conversion Rate" to Line.

• Secondary Axis: This is the crucial step. Since Ad Spend is in dollars and Conversion Rate is a percentage, they need different scales. Find the "Conversion Rate" series and check the box in the Secondary Axis column next to it.

As you make these changes, you'll see a live preview of your chart at the top of the window. It should already look like a combination chart, with bars representing spend and a line representing the conversion rate. The secondary vertical axis will appear on the right side of the chart, scaled for the line graph's data.

Step 4: Create the ChartOnce you're happy with the setup in the preview, click OK. Excel will drop your brand-new combo chart directly into your worksheet. Congratulations, you've done the hard part! Now, let's make it look professional.

Customizing Your Combo Chart for Clarity

A default Excel chart gets the job done, but taking a few extra minutes to customize it can turn a good chart into a great one. A clear, well-labeled chart ensures your audience understands the story you're telling immediately, without having to ask for clarification.

Click on your new chart, and you'll see two new tabs appear on the ribbon: Chart Design and Format. You'll also see a green "+" button appear to the right of the chart - this is your best friend for quick edits.

1. Write a Clear Title

Don't stick with the default "Chart Title." Double-click the title and give it a descriptive name that tells the viewer exactly what they are looking at. Instead of "Ad Spend," try "Monthly Ad Spend vs. Conversion Rate." It provides immediate context.

2. Add Axis Titles

This is especially important for a combo chart with a secondary axis. Your audience needs to know what each of the two vertical axes represents. Click the "+" icon next to the chart and check the box for Axis Titles.

• Primary Vertical Axis (Left): Click the new title box and label it "Ad Spend ($)."

• Secondary Vertical Axis (Right): Label this one "Conversion Rate (%)."

• Horizontal Axis: Label it "Month" or whatever time period you're using.

This small step eliminates all guesswork.

3. Adjust Colors and Styles

Use the Chart Design tab to play with different color schemes and styles. Pick colors that have good contrast and are easy on the eyes. If possible, stick to branding guidelines. You can also click on individual elements (like the bars or the line) and use the Format tab to change their color manually for total control.

4. Format Your Axes Correctly

Excel sometimes gets the number formatting wrong. To fix this, right-click on an axis and select Format Axis.... A new panel will open up on the right. Here you can:

• Adjust the minimum and maximum bounds of the axis.

• Change the number format. Under the Number section, you can set the left axis to display as Currency and the right axis to display as a Percentage.

5. Add Data Labels (Sparingly)

If you want to call out the exact values, click the "+" button and check Data Labels. This adds the numerical value to each bar and line point. This can be useful, but be careful not to make your chart look too cluttered. If you have many data points, it's often better to leave the labels off and let the viewer use the axes for reference.

Final Thoughts

Creating a combo chart in Excel is a powerful skill that elevates your data reporting, allowing you to show the relationship between different metrics fluidly. By organizing your data correctly and leveraging the secondary axis, you can build clear, compelling visuals that tell a more complete story than a simple bar or line graph ever could alone.

While mastering charts in Excel is a valuable skill, the process of manually exporting data, cleaning spreadsheets, and building reports week after week can be incredibly time-consuming. We built Graphed to eliminate that friction. Instead of wrestling with menus and formatting options, you simply connect your data sources (like Google Ads or your CRM) and ask for what you need in plain English. For example, you can tell Graphed: "Create a combo chart showing my ad spend as bars and my conversion rate as a line for the past quarter," and it instantly generates a live, interactive dashboard for you.