Table of contents
Introduction
We live in a world of data. There is so much of it everywhere that if we want to efficiently communicate complex information, we need to summarize this data in clear and easy-to-understand charts.
But there are many types of charts actually. Many more than the dozen options that Microsoft Office let us build in Excel or PowerPoint.
Choosing the right chart for the right task is a task of often underestimated importance, that will make your data - and you - stand out from the crowd and communicate ideas better.
This post provides you with a comprehensive guide to using various chart types in PowerPoint and Excel.
Default charts Vs advanced chart types
Default chart types in PowerPoint or Excel
There are limited options in terms of chart types in Microsoft PowerPoint and Excel. The applications come with 17 types of charts: column charts, line charts, pie charts, bar charts, area charts, XY scatter plots, maps, stocks, surface charts, radars, treemaps, sunburst, histograms, box & whiskers, waterfall charts, funnels and combos.
But don't settle for these default choices, there is actually a much greater variety of possibilities of chart types you can use to visualize your data.
How to create advanced charts in PowerPoint or Excel?
There are actually dozens of additional chart types you can create in Microsoft Excel and PowerPoint. It may take a bit of time and some workarounds, but it's possible. You may find help with some tutorials online for a lot of these charts.
There are many tricks to make chart do what you want with the basic PowerPoint and Excel options. Some of these tricks include using invisible series, like for creating the below dumbell chart
Another powerful trick is to leverage XY scatter plots. Many charts actually consist of data points with X and Y coordinates that you could calculate with formulas and plot.
For instance, take the below column chart with a trend arrow:
Here the trend arrow is obtained by plotting a line. Each data point of this line is positioned with XY coordinates.
The 1st data point of this arrow is a the bottom left. This data point has X set as 1 (because it's the 1st column), and Y set as the value of the first column plus 5 pixels.
The 2nd data point is the top left corner of the line. X is still set as 1, and Y is set as the maximal value of all columns, plus a few pixels.
The 3rd data point is where we have the "58%" label. It has X set as 2 (because it's on the 2nd column), and Y is the same as for the previous data point. This data point has a label when the other don't.
The 4th data point is the top right corner of the arrow. It has X set as 3 (because it's on the 3rd column), and Y is the same as before.
The 5th and final data point is the tip of the arrow. It has X set as 3, and Y is set as the value of the 3rd column, plus a few extra pixels.
Now when you put this in a single line series, and add a bit of formatting for the label and showing an arrow edge for the line, you get the above chart.
Another sweet technique consists of using error bars. The size of error bars can be defined using data from your spreadsheet. In the below example, we use XY data points to position the black diamond shapes, then errors bars with custom size are used to go above the first data point.
Like this, with a bit of creativity and mathematical skills, you can plot pretty much any type of chart in PowerPoint or Excel.
Inserting advanced charts with the Power-user add-in
If you're not in for the long work of buidling these from scratch, you can nsert a template for any of these charts from the Power-user add-in for PowerPoint and Excel.
The tool's charts library can even integrate your corporate colors and fonts to make charts perfectly matching your brand in no time.
Advanced chart types you can create in PowerPoint and Excel
We have identified at least 40 different types of charts you can create in PowerPoint and Excel. We summarized all these chart options in the below infographic that can help you quickly find the right chart for the right use case.
All the above chart types can be created with just Microsoft Office and some workarounds, or inserted directly from the Power-user add-in.
Some of these powerful charts include Mekko charts for analyzing 2 dimensions of data, Sankey charts for visualizing flows, Slope charts or Dumbell charts to quickly see how values change between 2 dates, diverging bars stacked for CSAT analysis, range charts for pricing comparisons, bullet charts for tracking deviation to budget, panel charts to compare the performance of multiple products or countries, waffle charts and more.
This list is not exhaustive, as there are certainly many other possibilities in this world. But it gives you a lot of options already so that you can pick chart types that will wow your audience.
How to choose the right chart type to visualize your data
Understanding your data
Once we have a properly cleaned up set of data, we might feel this urge to jump to designing charts. That's a mistake.
Before anything, you need to invest a bit of time to really understand your data. What do you need to show? What are people expecting from your analysis? There may be some obvious answers, like you need to show how sales are going compared to last year. But that's barely scratching the surface. What's really interesting is not what we can all see, it's why this is happening.
So you need to first think about the data you have, and what you should be looking at exactly to provide actionable insights to your audience. Choose the right KPIs and metrics, and think about how you should segment your analysis to dig up answers (are sales going down in every country? for every product? for every player? what are the key drivers? etc.).
Once you have identified the key analysis that deserve your attention, you can start preparing the data and choosing the right chart to visualize it.
Choosing the right type of chart
To choose the most powerful way to visualize your data, you have to ask yourself:
Do I need to make a static comparison?
Do I want to see how data evolves over time?
Do I want to see the composition of my data in sub-categories?
Do I want to look at the distribution?
Each chart should fall into one of these 4 categories. Using the above infographic, you can look and see all the options you have for that specific need. Then depending on the number of series and categories you have, as well as other specificities, you can choose the one chart that will work best.
Hint: you may also be interested in this older post about how to choose the best chart type to visualize your data.