Most advanced Excel charts are a standard chart from Insert → Charts with one trick added: a helper column, a series set to No Fill, an error bar or a second axis. The ten most useful are the step, milestone, waffle, tornado, Gantt, bullet, thermometer, speedometer, population pyramid and pictograph charts.
For timelines and plans, a Gantt chart is a Stacked Bar with the start-date series set to No Fill and the task axis set to Categories in reverse order. A milestone chart is a line or scatter chart whose markers drop to the axis with Minus, Percentage 100% error bars, and a step chart is a line chart in which each change date appears twice, once with the old value and once with the new.
For side-by-side comparisons, a tornado chart or population pyramid is a Clustered Bar with one side’s values entered as negatives, Series Overlap at 100% and a number format such as #,##0;#,##0 to hide the minus signs. For a single KPI against a target, use a bullet chart (stacked columns for the bands with the actual and target on top), a thermometer (one column filling a 100% outline) or a speedometer (a doughnut for the dial and a pie for the needle).
A waffle chart is not a chart at all: it is a 10 × 10 grid of cells holding 1% to 100%, with a conditional formatting rule that colours every cell up to the achieved percentage. A pictograph is a bar or column chart filled with an icon through Format Data Series → Fill → Picture or texture fill and Stack and Scale with.
Excel 2016 and later also have built-in waterfall, histogram, Pareto, box and whisker, treemap and sunburst charts, and funnel and map charts came later, so check your version under Insert → Recommended Charts → All Charts before building any of these by hand.
The speedometer is where most builds go wrong: if the needle reads high or low, the pie and the doughnut are not using the same total or the same Angle of first slice. Make the pie’s last slice the total minus the value and the needle width, keep both charts in the same units, and give both the same angle, such as 270°.
Charts are one of the most important parts of Microsoft Excel. They help us present our data in an understandable way. In Excel, we have different kinds of charts and graphs to present our data in different ways.
There are also a lot of advanced charts and graphs which we can create in Excel by making small changes to the default charts. Now, the bad news is that most Excel users don’t explore charting techniques much. But you can learn to create all those charts with a little extra effort.
The benefit of using advanced charts in Excel is that they are focused on a specific kind of data and make it more understandable. Today in this post, I have a list of the Top 10 Advanced Excel Charts and Graphs, just for you.
These charts can help you become a charting expert and a charting sensation in your office overnight, and that’s why it’s a part of our Advanced Excel Skills list.
Note: For each chart, there’s a detailed step-by-step tutorial that you can check to learn how to create it, and you can also download all the templates from here to learn more.
What is an Advanced Excel Chart or Graph?
An Advanced Excel Chart or Graph is a chart that has a specific use or presents data in a specific way. In Excel, you can create an advanced chart from the basic charts that are already there in Excel, from scratch (with cells and conditional formatting), or with pre-made templates and add-ins.
10 Advanced Excel Charts and Graphs
Below is the list of top advanced charts and graphs which are covered in this guide.
1. Step Chart
A step chart is an extended version of a line chart. It’s perfect to use when you want to show the changes that happened at irregular intervals. Unlike the line chart, it doesn’t connect data points using the shortest (sloped) line. It uses vertical and horizontal lines to connect the data points.
Step Chart’s Benefits
- It helps to show the exact time of change.
- Gives the real trend of the data throughout the period.
- In a line chart, it’s not possible to show the constant period, but in the step chart, you can present this.
- Shows you the actual numbers of the changes that happened in a period.
2. Milestone Chart
A milestone chart is also known as a timeline chart. It’s one of the favorite charts of project managers and helps them to track their project’s progress simply. It’s just like a timeline in which each stage of the project is captured with the date of completion.
Milestones are tools used in project management to mark specific points along a project timeline. These points may signal anchors such as a project start and end date, a need for external review or input and budget checks, among others. – Wikipedia
Milestone Chart’s Benefits
- It’s simple to create and easily understandable.
- You have all the important information in a single chart.
3. Waffle Chart
A waffle chart is more like a square pie chart. It’s a bunch of 100 small squares where one square represents 1% of the total value. I’m sure you have seen it in dashboards and news article graphics. And, to create it we need to use conditional formatting.
Waffle Chart’s Benefits
- Best for single-point target vs. achievement.
- Easy to create and simple to understand by the user.
4. Tornado Chart
When it comes to comparing different things with each other, a tornado chart can be useful for you. It looks like a real tornado, where the largest items are at the top and the smallest at the bottom. And, each item has an opposite item for comparison.
Tornado Chart’s Benefits
- It can easily compare items for two different time periods.
- You can also figure out the top and bottom items.
5. Gantt Chart
The Gantt chart is one of the most useful charts for project managers. It can help us to track the project’s progress. Unlike a milestone chart, a Gantt chart can track completion days, delayed days, days pending, total days, etc. In short, it’s a complete chart to track your projects with all the important details which everyone requires.
Gantt Chart’s Benefits
- It’s a complete chart with all the details to track a project.
- It makes it easy to make decisions, and we can review our project plan regularly in order to locate conflicts or other problems.
6. Bullet Chart
A bullet chart is just like a column chart, but instead of multiple columns, here we have only one. It can help us to track a single-point target vs. achievement. There are three major components of a bullet chart:
- A target marker to represent the target.
- An achievement bar to represent the actual value.
- A comparison range for the color theme.
Bullet Chart’s Benefits
- Best chart to track a single-point target vs. achievement.
- It serves as a replacement for dashboard gauges and meters.
7. Thermometer Chart
A thermometer chart is another useful chart to track a single-point target vs. achievement and can help us to present our data understandably. It looks like a thermometer where the entire chart presents the target and the filled part is the achievement. The filled part goes up as the achievement increases.
Thermometer Chart’s Benefits
- It’s simple to understand and easy to create.
- It’s a perfect chart to use in dashboards.
8. Speed-o-Meter
It’s the kind of thing that you can find in your day-to-day life [Just look at the SPEEDOMETER in your car]. It’s also known as a Gauge chart.
Speed-o-Meter’s Benefits
- It can help you present single-point data in your dashboards and KPIs.
- It’s easy to create and simple to understand for users.
9. Population Pyramid
A population chart is a two-sided bar chart that helps us compare the population of different age groups by gender.
Population Pyramid’s Benefits
- While comparing two items you can easily understand which group is growing.
- We can also use it to compare items for two different time periods.
10. Pictograph
A pictograph is not a separate kind of chart. By creating a pictograph, we can use an image in a chart to make it more presentable. Just like we have used an icon in the below chart to present the number of employees in a company in different age groups.
Pictograph’s Benefits
- Using a picture in a chart makes it more meaningful.
- It helps us to take our charting skills to a whole next level.
Advanced Charts Built into Newer Excel
Apart from the charts above, which you build yourself, newer versions of Excel have some advanced charts built in. You can find them under Insert ➜ Charts (or Insert ➜ Recommended Charts ➜ All Charts):
- Waterfall: shows how a starting value goes up and down to reach the final value (Excel 2016 and later).
- Histogram and Pareto: show how often values fall into ranges, and the Pareto chart also adds a cumulative line (Excel 2016 and later).
- Box and Whisker: shows the spread of the data with quartiles and outliers (Excel 2016 and later).
- Treemap and Sunburst: show hierarchical data, like categories and sub-categories (Excel 2016 and later).
- Funnel and Map charts: show the stages of a process and values by country or region (Excel 2019 and later, and Microsoft 365).
If you share your file with someone using an older version, these charts don’t show up for them, so the charts in the list above (built from the basic charts) are still the safer choice.
Save an Advanced Chart as a Template
Once you have built an advanced chart, you don’t need to build it again from scratch. Right-click the chart, select Save as Template, and give it a name. Next time, go to Insert ➜ Recommended Charts ➜ All Charts ➜ Templates and select it. A template saves the chart’s type and formatting, so you just need to select the new data.
Conclusion
Advanced charting techniques are one of those things which differentiate Experts from novice Excel users.
As I said, using advanced Excel charts makes your data more focused and easily understandable. You can also use them in your sales reports to make them easily understandable.
I hope the list of charts which I have shared with you will help you get better at charting, but now you have to tell me one thing.
Do you have another chart that you use frequently in your work?
Share it with me in the comment section. I’d love to hear from you, and please don’t forget to share this list with your friends. I’m sure they will appreciate it.
Frequently Asked Questions
Which advanced chart is best for target vs. achievement?
For a single value, use a bullet chart, a thermometer chart or a waffle chart. The bullet chart is the most compact and can also show performance bands.
Do these charts need any add-in?
No. All ten charts in this list are built with Excel’s own charts, formulas and conditional formatting, so they work in any recent version of Excel without add-ins.
Which advanced chart should I use for a project?
Use a milestone chart to show the key dates on a timeline, and a Gantt chart when you also need to track the duration and progress of each task.
You Must Check Out These
Interactive Charts in Excel | Add a Vertical Line in a Chart in Excel | Add a Horizontal Line in a Chart in Excel | Dynamic Chart Range in Excel | Dynamic Chart Title in Excel | Copy Chart Formatting to Another Excel Chart | Add Secondary Axis in Excel Charts
This post is a game-changer! The advanced charts and graphs are just what I needed to elevate my Excel presentations. I can’t wait to download the templates and start using them. Thank you for sharing such valuable resources!
This post is incredibly helpful! I’ve always struggled with creating advanced charts in Excel, and these templates are a game-changer. Can’t wait to try them out and elevate my data presentations! Thank you for sharing!
This is a great post! I’ve been looking for some good charts and graphs to use in my work and this is a great resource.
Top 10 clients position wants to display on chart for 3 months (Month wise position based on revenue). How to do
Need help please.
For my speedometer Series of 40, 40, 20 = a total of 100
Series labels starts at 50% (as 0% mark at half the circle) with an increment of 5% up to 100% with the scale amount of 9-10s, 1-9, 1-1.
The needle formula is
Needle Amounts
Pointer 50.0% – F3
Thickness 0.015 – F4
Total 152% =203%-SUM(F3:F4)
My need is pointing right in the middle between 70 & 75 but it should be at the 50% (0%) line. What am I doing wrong?
I tried this chart which look nice. I have a problem while labeling 2nd chart 10,20,30……
While i am selecting the category name of 2nd chart the labeling is showing 1st chart labels. Can you please advice
How to fix range in pir chart in excel???
Dear Puneet
Although I work as Manager in Telecom, your tips were really nice. It helped me to catch those jokers in MIS
hahahahah. Good Luck.
NICE WORK BROTHER
10 very interesting graphicis, thank you
Thanks for the info 🙂
Hi….
I love to learn spreadsheet, may i have E-Book for that or tutorial
Hi Puneet
Your Excel tips are excellent. Which type of Chart is suitable to show student’s progress termwise.
How can I use speedometer chart in my teaching profession. I mean what could be possible areas to use speedometer chart.
Thanks
Greetings, Puneet. I have a recommendation for the Gantt chart. Instead of the Conditional Formatting changing the entire cell fill color when it fires, do this:
In the upper left-most cell of the chart grid, enter five lower-cased g’s. ggggg
Set the cell font to Webdings
Set the font size to 8
Set the font color to something dark
Set the text alignment to center and middle
Do a Merge and Center to that single cell only to force the characters to each side of the cell
You should see what looks like a bar going horizontally across the middle of the cell
Now, give that cell a Custom Format of ;;; The bar will disappear from view
Use the fill handle to fill the rest of your grid with that cell’s formatting and contents
While it is all still highlighted, go to Conditional Formatting and simply set it up so that when it fires (the date in the top row is in the range of your start and end dates) all that will happen is the cell’s font formatting will be set to General. No color changes or other formatting is needed.
Whenever a cell that is in the same column as a date that is in the range of your start and end dates for that row will allow the bar to be seen.
As always, thank you for your excellent site and I look forward to many more tips and tutorials.
https://uploads.disquscdn.com/images/45cd15bf80d75de3f65670af0ee0e1295ad5c96ba25fab3386d0e16d6218102d.png
@plastic_cup:disqus Thanks for this great idea. Let me try this.
Gauge Chart
@disqus_9YY4l5lcEz:disqus Great.
Fantastic -Usefully
@disqus_9YY4l5lcEz:disqus I’m so glad you liked it. 🙂