Bottom Line: Learn step-by-step how to create a multi-layer doughnut chart in Excel, how to design it beautifully, and how to ensure your data is always sorted correctly in the chart.
Skill Level: Intermediate
Video Tutorial
A multi-layer doughnut chart is an advanced data visualisation technique in Excel that you will not find in many standard Excel reports and dashboards. It is exceptional and will pimp up your Excel data visualisation repertoire tremendously.
The core feature of this chart is the gradual multi-layer design, which means a data point has more layers the bigger its value is. At the same time the values are ordered from the biggest to the smallest value which leads to visually appealing flow for the audience’s eyes.
So let’s see how you can build it from scratch.
Table of Contents
Start with the standard Doughnut Chart
The doughnut chart is a better version of the pie chart. While most people still use pie charts when they build reports and dashboards, the doughnut chart is the only reasonable choice for circular charts in a dashboard in my opinion.
IMPORTANT: Do not use doughnut charts if you have a big amount of data points to display. A recommended amount of data points in a doughnut chart is between 2 and 6. Everything above will lead to more confusion for your audience and does rarely provide any value.
Let’s have a look at how to insert a regular doughnut chart: Simply select your data range, then go to the Insert Tab and insert the doughnut chart from the chart selection area (you find it under the same button as the pie chart).
The inserted chart is a simple doughnut as you might already know.
This chart visualises the ratio of our four data points easily, but it doesn’t have an exceptional look and only one regular doughnut layer. So how can we add more chart layers?
Add multiple chart layers with artificial data
Unfortunately, our simple data set is not enough for adding multiple layers to this chart. We need to create some additional data series that will then be included into the current doughnut chart. This sounds more complicated than it is. We simply have to replicate the original data series three times in order to get as many separate (but equal) data series as we have data points.
Once the three additional data series are created, we include them into the chart data range and our chart suddenly has four full layers. Beautiful.
Create gradual multi-layer design
In order to get the gradual multi-layer design (which means the data points display less layers the smaller they are) we need to do some formatting for this chart.
To get started with formatting, I recommend to choose a dark background (that makes the single elements become better visible). Additionally, I recommend to select a colorfol color theme like the ‘Yellow’ theme for a better visual experience.
By default, all doughnut chart layers have a borderline. As this border line is only disrupting the look, you should remove it for all borders first. After that, select the outer layer of the second (also second biggest) data point and set the fill to No fill.
For the third data point we apply the same technique to the two outer layers, and so on. This leads us to the following result.
That’s the foundation for the multi-layer doughnut chart, but it still requires some effort to get the most out of it and make sure it always looks smooth.
Finalise the chart design
For the final chart design, I recommend to follow these three steps:
- Move the legend to the right. This makes sense, because just like in the chart, the data points represent an order from the biggest to the smallest value. If the legend is on the right site, all data point descriptions are stacked in the correct order.
- Select a suitable color theme that fits your overall report style. If you use theme colors during the design process, you can easily switch the whole color theme by going to Page Layout > Colors.
- Add data labels to each data point. Be careful here: You have to seperately select each individual outer data point if you want to move the labels away from the chart with a connector line. Otherwise, if you e.g. add data labels for all data points to the outer layer, the connector lines will not be connected to the respective chart layer but only to thin air.
But this isn’t the end of the process. This current chart design only looks so smooth, because the data points have already been sorted from the biggest to the smallest value in our data. But what if our data changes and suddenly the second data point is the biggest? This would destroy the whole visual order and flow in the chart and look like this…
Can you see how the chart doesn’t look as good anymore now that the second data point has the biggest share with only three visual layers?! To avoid this behaviour of the chart, we can apply a simple trick to make it fully robust to changes in the order of the data points without touching the original data.
Automate the sorting of your data
Note: This final step might not be working in older Excel versions. To make sure you can use dynamic array functions, you should consider an upgrade to Excel 365. Read our full review of Excel 365 (click here!)
The trick for this is the SORT function in Excel. This function’s syntax looks as follows:
=SORT(array; [sort_index]; [sort_order]; [by_col])
To get a automatically sorted version of our original data, we simply have to pass the array reference for our original data, a column index by which we want our data to be sorted by (here we take the second column), and the desired sort order (which is descending in our case – from biggest to smallest value) .
After hitting enter, this formula spills the sorted data in the given range and whenever we now make a change to the original data, it will automatically sort it as desired.
So all we have to do now is to change the source data range of this chart to this new sorted range.
PRO TIP: If you want to make sure the chart format isn’t completely reset when you change the complete source data range, go to Excel Options > Advanced > Chart and uncheck the Properties follow chart data point for current workbook option.
Once the chart is referencing the new sorted data range, we can change our original as we want and the chart will automatically be sorted without a problem..
Conclusion
The multi-layer doughnut chart is certainly a technique that will impress everyone around you. It takes some effort, indeed, but it is well worth the work.
For an instant insight into all techniques covered in this tutorial, download the worksheet from our Download Area.
