How to Stack Charts in Excel
Trying to show sales revenue alongside a profit margin percentage in a single Excel chart can feel like a headache. One data set is in large dollar amounts, the other is a small percentage, and putting them on the same axis makes a mess. This isn’t an Excel limitation, it’s a sign you need a combination chart, often called a stacked chart or a combo chart.
This tutorial will show you exactly how to layer different chart types - like bars and lines - into one clear, insightful visualization. We'll cover the essential step-by-step process of creating a combo chart, making sure your data tells the full story without causing confusion.
Why Combine a Bar Chart and a Line Chart?
Before diving into the "how," it's important to understand the "why." Combining charts isn't just about saving space, it's about showing relationships between different kinds of data over the same period. This technique is especially powerful when you need to compare volume and rates.
Here are a few common scenarios where a combined bar and line chart works perfectly:
Sales Revenue vs. Profit Margin: Showing total sales (big numbers, represented by bars) against the profit margin percentage for each period (small numbers, perfect for a line).
Website Traffic vs. Conversion Rate: Tracking the number of site visitors (bars) and the percentage of those visitors who convert (line).
Units Sold vs. Customer Satisfaction Score: Comparing the volume of products sold (bars) with the customer satisfaction ratings (a score or percentage, represented by a line).
In each case, one metric measures a quantity or volume, while the other measures a rate, percentage, or ratio. A standard chart would struggle to display these different scales, but a combo chart with a secondary axis handles it beautifully.
Step 1: Structure Your Data Correctly
Excel is powerful, but it relies on you to organize your data logically. The most critical step in creating a good chart is setting up a clean, simple table. For a combo chart, your table should have at least three columns:
The Label: This is your x-axis. It’s typically a time period, like Month, Day, or Quarter.
The Primary Data Series: This is the data you want to display as bars (e.g., Sales Revenue).
The Secondary Data Series: This is the data you want to display as a line (e.g., Conversion Rate %).
Here’s what your data should look like:
Notice how clean and simple this is. Each row corresponds to a single period (a month), and each column has a clear heading. Avoid merging cells or adding extra commentary within the data range you intend to chart.
Step 2: Create the Initial Combo Chart
Once your data is properly formatted, creating the basic combo chart only takes a few clicks.
1. Select Your Data Range: Click and drag to highlight your entire data range, including the headers (e.g., from A1 to C13 in our example). Starting with the headers tells Excel what to use for the chart legend.
2. Insert a Combo Chart:
Go to the Insert tab on the Ribbon.
In the Charts section, click on the small icon that shows a bar and line chart, which is the Insert Combo Chart option.
From the dropdown menu, select Create Custom Combo Chart.... This will give you the most control.
3. Configure Your Chart Type and Axes:
A new window will pop up. This is where you tell Excel how to handle each of your data series. You'll see your series names ("Sales Revenue" and "Conversion Rate %") with dropdown menus next to each.
For Sales Revenue, choose Clustered Column as the chart type.
For Conversion Rate %, choose Line as the chart type.
This is the most important part: check the box in the Secondary Axis column for your line chart data ("Conversion Rate %"). This tells Excel to create a separate vertical axis on the right side of the chart, scaled specifically for that data.
Click OK. Excel will instantly generate your combo chart.
Step 3: Customize and Refine Your Chart for Clarity
Excel’s default chart is functional, but it often needs a few tweaks to make it truly professional and easy to read. This is where a good chart becomes a great one.
Add Axis Titles
Your chart now has two different vertical axes, but nobody knows what they represent. Adding axis titles is non-negotiable.
Click anywhere on your chart to select it.
The Chart Design tab will appear in the Ribbon. Click on it.
On the far left, click Add Chart Element > Axis Titles.
Add a Primary Vertical Axis Title. Double-click the new text box that appears and name it something descriptive, like "Sales Revenue."
Repeat the process, this time selecting Secondary Vertical Axis Title. Name this one "Conversion Rate."
Improve the Chart Title and Legend
The default title is probably just your column headers meshed together. Give it a more descriptive name that tells the viewer what they're looking at, such as "Monthly Sales Revenue vs. Conversion Rate." You can also move the legend. To save space, you can position it at the top or bottom of the chart area via the Add Chart Element > Legend menu.
Add Data Labels
For more clarity, you can add data labels directly to your chart elements. This saves your audience from having to squint and guess at the exact values.
For the columns: Right-click on one of the bars and select Add Data Labels. The revenue numbers will appear on each column.
For the line: Right-click on the line itself and select Add Data Labels. The percentages will appear next to each data point. If the placement looks cluttered, you can click on an individual label and drag it to a better position.
Format the Axes
Sometimes, Excel’s default axes aren't formatted well for your data. For example, our Conversion Rate axis on the right goes up to 3.50%. We can clean that up.
Right-click on the secondary vertical axis (the percentage values).
Select Format Axis...
In the Format Axis pane that opens on the right, look for Axis Options.
Under Number, you can change the format from "Percentage" to "Custom" and set the Format Code to
0"%"to remove a decimal point if it isn’t needed, which can make the axis cleaner.
After these refinements, your chart should be much clearer and more professional. You’ve successfully turned a simple table into a compelling visual story.
Alternative: Combining a Stacked Column Chart with a Line Chart
What if you want to break down the primary metric into components? For instance, maybe you track revenue from two different products (Product A and Product B) and want to show the total while also plotting it against your overall revenue goal.
In this case, you can use a stacked column chart as your base instead of a clustered one. The process is nearly identical.
First, structure your data with columns for each component you want to stack:
Example Data Structure:
Column A: Month
Column B: Product A Sales
Column C: Product B Sales
Column D: Revenue Goal
Then, when you get to the Create Custom Combo Chart window:
Set both "Product A Sales" and "Product B Sales" to Stacked Column. Make sure they both use the Primary Axis.
Set "Revenue Goal" to Line and assign it to the Secondary Axis.
This will create a chart showing the total combined sales (with the different products color-coded in the stack) and a line overlaying that stack to show how you performed against your goal. It's a fantastic way to add another layer of detail without building a second chart.
Common Pitfalls and How to Avoid Them
Creating combo charts is straightforward, but a few common missteps can make them confusing.
Putting Too Much on One Chart: While it’s tempting, avoid plotting more than two or three series on a combo chart. A line, a second line, and some bars quickly become a tangled, unreadable mess. Always prioritize clarity.
Misleading Secondary Axis: Be very careful with the secondary axis. Unless labeled perfectly, it can create false visual correlations. An insignificant dip in the line might look catastrophic if the axis scale is too narrow. Always ensure your axes are clearly titled.
Incorrect Data Formatting Before You Start: Remember the first step? 90% of Excel charting problems stem from poorly structured data. Ensure your source table is clean, simple, and has no merged cells or blank rows before you even think about clicking "Insert Chart."
Final Thoughts
Combining columns and lines in a single Excel chart is a powerful skill for telling a more comprehensive data story. By using a custom combo chart and a secondary axis, you can effectively visualize relationships between volumetric data and rates, giving you and your stakeholders a much clearer picture of performance.
While creating these charts in Excel is an invaluable skill, the manual work of exporting data from different platforms and constantly updating spreadsheets can quickly become a bottleneck. We built Graphed to solve this by automating the entire process. Once you connect your data sources like Google Analytics, Shopify, or your CRM, you can ask questions in plain English - like "create a chart showing website sessions vs. conversion rate this year" - and get a live, auto-updating dashboard in seconds. This lets you spend your time on insights, not manual chart-building.