What You’ll Learn
- ✓ How to build a combo chart and add a secondary axis in Excel
- ✓ How to insert and format Excel sparklines for at-a-glance trends
- ✓ How to create a dynamic chart in Excel that updates automatically
- ✓ How to save and reuse Excel chart templates across workbooks
- ✓ Chart design principles that make your data impossible to ignore
Advanced charts in Excel are the difference between a report that gets a polite nod and one that someone actually acts on. You have probably been in a meeting where one person’s chart had everyone leaning forward and another person’s chart got a quick scroll-past. The difference was almost never the data. It was the chart.
This lesson is for you if you already know how to insert a basic Excel chart but want to move past the defaults. We are covering combo charts, sparklines, dynamic charts that update themselves, chart templates, and a few design rules that will make your charts look like they came from a professional analyst. No fluff — just the techniques that actually matter.
If you are starting completely from scratch with charts, check out Lesson 20: Excel Charts Basics first, then come back. Everything here builds on that foundation.
Why Advanced Charts in Excel Change How People Read Your Data
The Problem With Default Excel Charts
Here is the thing about Excel’s default chart settings. They work. But they produce a chart that looks exactly like every other chart made by every other Excel user who also just clicked Insert and picked a bar chart. Same blue color. Same grey background. Same gridlines nobody asked for.
That is not your fault. Excel defaults are designed to be acceptable for everyone, which means they are not optimized for anyone. Advanced charts in Excel are about making intentional choices instead of accepting the defaults.
What “Advanced” Actually Means
Advanced does not mean complicated. It means deliberate. A chart where you removed the clutter, picked a purposeful color, and chose the right structure for your data is an advanced chart.
It could be a simple single-series bar chart. It could also be a combo chart with two axes showing revenue and margin side by side. The technique matters less than the intention behind it.
Excel Combo Chart — Show Two Stories in One Chart
What Is a Combo Chart and When Should You Use It?
Imagine you are presenting monthly sales data alongside the gross profit margin percentage for each month. You want both on the same chart because they tell a connected story. But sales revenue is in the hundreds of thousands of dollars, and margin is a number between 0 and 100. If you plot them both on the same axis, the margin line will sit glued to the bottom, completely unreadable.
That is exactly the problem an Excel combo chart solves. A combo chart puts two different chart types — typically a column chart and a line chart — into the same chart area. Each series gets its own format, and the one with the different scale gets its own axis on the right side of the chart.
Adding a Secondary Axis in Excel for Different Data Scales
The Excel secondary axis is a second vertical scale on the right side of the chart. It lets your line series use its own range of values, completely independent of the left axis. So your sales bars can scale from 0 to 500,000 while your margin line scales from 0 to 100 — and both look perfectly readable in the same chart.
Microsoft’s official support page covers adding a secondary axis in detail. You can find it by visiting support.microsoft.com and searching for “add or remove a secondary axis in a chart in Excel.” Worth bookmarking for reference.
How to Create a Combo Chart in Excel
Step-by-Step: Insert a Combo Chart
- Select your data range including both series and the column headers
- Go to the Insert tab on the ribbon
- In the Charts group, click Insert Combo Chart
- Choose Clustered Column and Line
- In the combo chart dialog, find the series that should go on its own axis
- Check the Secondary Axis box next to that series
- Click OK to insert the chart
- Double-click the chart to open the Format panel and start cleaning it up
💡 PRO TIP
Before inserting the chart, make sure your two data series sit in adjacent columns with a shared row of labels at the top. If they are spread out across the sheet, Excel sometimes groups them incorrectly and you end up with the wrong series on the wrong axis.
⚠️ COMMON MISTAKE
Adding a secondary axis to two data series that share the same unit and scale. If both series are in dollars, or both are percentages, a secondary axis will mislead your reader into thinking the scales are different when they are not. Only use the secondary axis when the units or value ranges are genuinely different.
Excel Sparklines — Tiny Charts With a Big Impact
What Are Sparklines and Where Do They Shine?
Think of sparklines as the thumbnail version of a chart. They live inside a single cell and show a trend at a glance, without asking your reader to leave the data table to go look at a separate chart. You have probably seen them in financial reports or executive dashboards and not even noticed them. That small squiggly line or tiny bar column sitting at the end of a data row? That is a sparkline.
They are not meant to replace full charts. They are meant to sit next to your numbers and give the reader an instant visual sense of whether things are going up, down, or staying flat.
How to Insert Sparklines in Excel
Step-by-Step: Insert Sparklines
- Click the cell where you want the sparkline to appear
- Go to the Insert tab on the ribbon
- In the Sparklines group, choose Line, Column, or Win/Loss
- In the Data Range field, select the row of numbers the sparkline should represent
- Confirm the Location Range shows the correct cell
- Click OK
To apply sparklines to an entire column of rows at once, select a full column of empty cells as your Location Range, and the matching block of data rows as the Data Range. Excel creates one sparkline per row automatically.
Formatting Sparklines to Highlight Highs, Lows, and Markers
Once your sparklines are in place, a Sparkline tab appears in the ribbon. From there you can:
- Turn on High Point and Low Point markers to visually flag the peak and valley
- Change the sparkline color and marker color independently
- Switch between Line, Column, and Win/Loss types on the fly
- Set a fixed vertical axis so sparklines across different rows are comparable
💡 PRO TIP
By default, each sparkline autoscales to its own data. A row with tiny changes and a row with wild swings will look identical. To fix this, go to the Sparkline tab → Axis → set Minimum Value for Vertical Axis to Fixed and Maximum Value for Vertical Axis to Fixed. Now your sparklines are genuinely comparable across rows.
⚠️ COMMON MISTAKE
Making the sparkline column too narrow. A squashed sparkline communicates nothing. Make the sparkline column at least double the width of your regular data columns. It takes three seconds and makes a significant difference to readability.
Dynamic Chart Excel — Charts That Update Themselves
The Easiest Method: Convert Your Data to an Excel Table
Here is one of the best hidden advantages of advanced charts in Excel. You can make a chart that never needs manual updating. Every time you add a new row of data, the chart just grows to include it automatically.
The easiest way to do this is by building your chart on top of an Excel Table. Trust me on this: once you start building charts this way, you will never want to go back to manually extending data ranges.
Step-by-Step: Create a Dynamic Chart Using an Excel Table
- Click anywhere inside your data
- Press Ctrl + T to open the Create Table dialog
- Make sure My table has headers is ticked
- Click OK
- Insert your chart from this Table as usual
From now on, any new row you add below the table automatically appears in the chart. No manual range updates required.
For a full walkthrough of Excel Tables, see Lesson 23: Tables & Structured References in Excel at XplorExcel.com.
Advanced Method: Dynamic Named Ranges With OFFSET and COUNTA
If you cannot use a Table for some reason, you can build the same dynamic behavior manually using a named range based on the OFFSET and COUNTA functions. The formula structure looks like this:
Dynamic Named Range Formula
=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)This starts at cell A2 and expands the height of the range based on how many non-empty cells exist in column A, minus the header. Assign this to a named range, then point your chart’s data source at that named range.
💡 PRO TIP
The Excel Table method is faster, simpler, and far less likely to break. Use OFFSET-based named ranges only when you genuinely cannot format your data as a Table, or when you need very precise control over which portion of a long column feeds the chart.
Excel Chart Templates — Save Your Best Work, Reuse It Anywhere
How to Save a Chart as a Template
You have just spent 45 minutes getting a chart looking exactly right. Clean colors, proper axis labels, no clutter, correct fonts. Saving that as a chart template means you never have to do that work again. One click and any future chart uses the same formatting.
Step-by-Step: Save a Chart as a Template
- Click on your finished chart to select it
- Right-click the chart border
- Choose Save as Template
- Name it something descriptive (e.g., Sales Combo Blue or Monthly KPI Dashboard)
- Click Save — Excel stores the
.crtxfile automatically
Applying a Saved Template to a New Chart
Step-by-Step: Apply a Chart Template
- Select your new data range
- Go to Insert → click the small arrow at the bottom-right of the Charts group → All Charts
- Click the Templates folder on the left panel
- Select your saved template and click OK
Sharing Chart Templates Across Your Team
Your .crtx file sits in a local folder on your machine. Copy it and send it to your team. They need to paste it into this path:
Chart Templates Folder Path
C:\Users\[YourName]\AppData\Roaming\Microsoft\Templates\ChartsOnce the file is there, it appears in their Templates folder inside the Insert Chart dialog — ready to use.
Chart Design Principles That Make Your Charts Look Professional
Remove Chart Junk — What to Delete Immediately
Chart junk is everything on your chart that does not help the reader understand the data. Default Excel charts come loaded with it. Here is the hit list — click each item and press Delete:
Choosing the Right Colors
The default Excel color scheme is recognizable to anyone who has opened a spreadsheet in the last ten years. That recognition works against you because it signals that no deliberate choices were made. Pick one or two intentional colors and use them consistently.
For single-series charts, a clean solid blue with one contrasting accent color is plenty. Use brand colors if you are building for a client. And avoid red and green together — it creates accessibility issues for colorblind readers.
Smarter Data Labels and Custom Callouts
Default data labels crowd the tops of bars and clutter the chart. For advanced charts in Excel, use custom data labels that pull values from a specific cell range instead of just showing the raw number. Right-click on a data label → Format Data Labels → tick Value From Cells → point it to your label cells.
For detailed chart design guidance, Chandoo.org’s Excel Charts resource is one of the best free references available and well worth reading alongside this lesson.
Trendlines and Error Bars — Charts for Analysis, Not Just Display
Adding and Formatting a Trendline in Excel
A trendline is a line Excel calculates and draws through your data points to show the overall direction of movement. It is useful for sales performance tracking, forecasting, and any situation where you want to show the broader pattern rather than individual highs and lows.
Step-by-Step: Add a Trendline
- Click on a data series in your chart to select it
- Right-click and choose Add Trendline
- In the Format Trendline panel, select Linear, Exponential, Moving Average, or another type
- Tick Display Equation on Chart if you need to show the formula
- Tick Display R-squared value if you need to show how well the trendline fits
When to Use Error Bars
Error bars show a range of uncertainty around each data point. In business charts, they appear in forecast charts where you want to show an expected range rather than a single projected value. To add them: click your chart → select the data series → click the Chart Elements button (the plus sign beside the chart) → tick Error Bars.
Advanced Charts in Excel — Practice Exercise
🧪 TRY IT YOURSELF
This exercise uses everything from this lesson in one mini dashboard. Allow 15–20 minutes.
- Create a table with three columns: Month (January–June), Total Sales (50,000–200,000), and Gross Margin Percent (20–45).
- Convert the data to an Excel Table using Ctrl + T.
- Insert a combo chart. Set Total Sales as a Clustered Column and Gross Margin Percent as a Line on the Secondary Axis.
- Remove the chart border, grey background, and gridlines. Set the column color to solid blue and the line to orange.
- Add a sparkline next to each month’s Total Sales value. Turn on the High Point marker in red.
- Add a row for July with new data. Watch both the chart and sparkline update automatically.
- Right-click the finished chart and save it as a template named My Dashboard Chart.
When you finish, you will have used a dynamic chart, a combo chart with a secondary axis, sparklines, design principles, and chart templates — all in one working file.
Frequently Asked Questions
What is the best chart type for comparing two metrics in Excel?
Use a combo chart with a secondary axis when the two metrics have different units or very different numerical ranges. If both metrics share the same unit and similar range, a grouped column chart is usually the cleaner choice.
Can Excel charts update automatically when I add new data?
Yes. The simplest way is to base your chart on an Excel Table (press Ctrl + T on your data before inserting the chart). Any row you add to the table automatically appears in the chart. No manual updating required.
How do I add a second Y axis in Excel?
Right-click the data series you want on the secondary axis and choose Format Data Series. Under Series Options, select Secondary Axis. You can also tick the Secondary Axis checkbox for any series when inserting a combo chart from the Insert tab.
What is the difference between a sparkline and a regular chart?
A sparkline lives inside a single cell and shows a quick trend alongside a data table. A regular chart is a standalone object with its own size, position, and formatting. Use sparklines for compact at-a-glance trends across many rows. Use regular charts when the chart itself is the main focus of the page.
📚 Further Reading
Microsoft Support — Add or Remove a Secondary Axis
Official Microsoft documentation on adding and configuring a secondary axis in Excel charts.
Visit support.microsoft.com →Chandoo.org — Excel Chart Formatting Tips
One of the most practical free resources for taking your Excel chart aesthetics and design further.
Visit chandoo.org →