How to Create a Multi-Layer Doughnut Chart in Excel

[Music] hey there and welcome to the office lab in this video I want to show you how to create this beautiful multi-layered donut chart in Excel this chart is not available in Excel by default but you can easily build it based on the standard Excel donut chart with only a few tricks you have to know I'm gonna show you step-by-step how to create this chart how to format and design it in the correct way and also how to make sure that the well use are always sorted from the biggest to smallest value in the chart and if you want you can download the worksheet from Excel fine come with that being said let's get straight into it here we have a small data set for that chart with four regions and some revenue numbers now with this data we could simply insert a simple regular donut chart by clicking on insert and right here we select the donut chart there we have the simple donut chart nothing special now how do we add multiple layers to this donut chart for that our basic data set is not enough we have to create some additional artificial data now that sounds more complicated than it is for each row we simply duplicate the revenue well you three times to make it four well use per row why do we need exactly four values we have four rows in our data so we want to also have four layers in the donut chart and for that we need four separate data serious so let's click here to autocomplete these values and now we click on the chart and expand the range of the source data to include these additional three columns and that gives us these four layers stacked on top of each other that's the first step the next step is to format the chart area we start by making the background color really dark and also change the general color theme to one of Excel standard color themes called yellow then we call it multi-layer donut chart and change the legend font to white beautiful now that the background is dark we can see that by default these donut layers all have white border lines around them that's something we absolutely don't want to have for this chart so we remove them for each of the layers in order to get this layout with a decreasing number of layers from the biggest to the smallest data point what we do is we start with the second data point which is Canada here we want to remove the outer layer so we select this specific single data point in the outer layer with the first click the whole house a data Sirius is selected with the second click the single data point is selected and then we go to fill and set it to no fill after that we apply the same technique for the third data point but this time we want to remove the two layers so we set the outer layer to no fill and then the second outer layer as well and that for the smallest at a point we obviously remove the three outer layers which leaves us with only one layer here that's already beautiful now for the coloring it is really important to make it one smooth visual flow from the biggest to the smallest data point that means for this color theme that we have selected it's a good choice to start with red for the biggest data point we leave the second data point orange as it is then for the third data point we choose the slide yellow and the last one will be simply white that looks already way better and smoother than before another good advice is to put the latch in to the right so you instantly see the order of the data points and then what is always really helpful if we work with donut charts is adding sub labels because you don't have an axis that tells you what absolute values are actually represented here and you have to be careful when you add labels to this custom donut chart because if you think this can be done with one click by right-clicking on the outer layer and add data labels for the data serious the problem you will have is let's make that white to show you as soon as you put this a little bit to the outside this connector line is correctly created for this first data point but if you do the same for the smallest data point for example now it is only referring to thin air because that's where the outer layer has been for this data point so what we have to do is we first get rid of all these except the first one for this one we add a border line around it to make it visually more appealing and then we add the labels separately for each data point by right-clicking on the new outer layer so let's see how that looks for the orange one now it is referring to the correct layer and we can apply the same approach for the remaining data points I know it takes some manual effort in the beginning but in my opinion if you want to have a great data visualization you have to put in the effort you now since we used theme colors and no custom colors for this chart design we can now easily change the coloring of this chart with one click by going to page layout colors and there we can select from a variety of theme colors the standard office color themes are down there but I also have a bunch of custom color themes from a PowerPoint template called massive X and just by hovering over these you can see the design of the chart is changing according to these themes ain't dead beautiful let's choose this one for now to improve this design even more you could make use of creating and coloring using different shades of the same color but for now let's keep it basic from a design perspective and focus on one last important question the data we currently have is already sorted from the biggest to the smallest value and that's why the chart looks so smooth but what if the data changes so for example if the first value is suddenly 10 and the second is 50 now this chart doesn't look as smooth as before because the Whistle order is somehow broken that means you should always make sure that your data is sorted in the descending order before you build that chart on top of it you have multiple options how to do that you could either sort your data manually but I recommend to automate it sorting with a simple formula so we leave our original data as it is up here and then we copy this data area rename it and remove all the data in the top left cell of this range we now insert the sort formula which takes care of the sorting for us I pass our data array reference as the first argument then we define the column index of the column and we want to sort by let's take the second column for this and for the sort order we pass a minus 1 to make a descending that's already yet our original data is now spilled into this range but sorted by the revenue column so if we change the world you for Canada to 100 now Canada is the top value down here beautiful so all we need to do now is to connect our chart to this new data range and at this step many of you will face a big issue that often happens if you want to change the range of your data completely because by default if you change the source data to a completely new range of data the formatting of these charts is reset that's something that has been bugging me in so many situations but now I got the solution to resolve that issue for you if you are facing that problem all you have to do is go to the Excel options and under the Advanced tab scroll down to the chart section there you have to uncheck properties followed shot data point for current workbook the option above should also be unchecked and if you now return to the chart change the data range formatting stays the same and with this little extra step we assured that no matter how the well use in our original data change the chart will always have the correct order from the biggest to the smallest well you and that's already it for this quick insight on how to create a multi-layer donut chart with automatically sorted data I hope you liked it let me know in the comments and if you want to learn more advanced data visualization techniques in Excel make sure to like this video and subscribe to my channel see you next time Cheers [Music]

Channel: The Office Lab

Views: 27,671

Rating: 4.9709792 out of 5

Keywords:

Id: qQJSuNh2FRc

Length: 10min 13sec (613 seconds)

Published: Sat Jun 13 2020