The doughnut chart seems like an elegant version of the pie chart.
But as elegant as it may be, it is still not as effective as something so simple as a sorted bar chart to convey the same information.
Even better, the bar chart is easy to make.
Taking the example below, this bar chart employs automatic sorting so if the numbers change, the sort order automatically changes too.
If this is something that you need once a year, you can manually sort this using the sort feature in Excel.
But if this is something you use often, you’d want Excel to do the sorting for you.
As a rule of thumb, in determining the type of chart to use, understand the message of the report.
In this case, we’re looking at key geographical areas as a percentage of sales volume, while other countries are grouped in the Other category.
A sorted bar chart is used when the particular sort order of the series is not fixed and we want to look at the data in ascending or descending order.
For cases where the sort order is fixed and your audience is used to looking at them in that particular order, such as model releases, it would be wise to keep the order to avoid confusion.
Featured Course
Fundamentals of Financial Analysis
Copying the data to Excel
For this example, I’m assuming that the data doesn’t originally come sorted.
The first column contains the country of origin, with European countries, America and so on, and the Other category, last.
Automatic sorting of charts using the RANK() function
To create a sorted bar chart of this data, one option would be to highlight cells J7:L13, manually sort it and then create a bar chart.
In cases where the data is changing very often, it would make sense to set it up and let Excel sort the data automatically.
In this case, create a data preparation table that gives a sorted version of the information in the original table.
Note that we will be retaining the Other category at the last spot.
The chart will then be used as the reference to the chart, so when the sorting in the table changes, so does the chart.
The RANK() function is great for getting the information in ascending or descending order.
In this case, we will be using the descending order because ultimately, we want to show rank 1 first, followed by rank 2 and so on.
The syntax of the RANK() function is RANK(number,ref,[order])
- number – the cell value
- ref – the number or range it should compare to. In this case we exclude Other.
- [order] – 0 for descending, 1 for ascending order. By default, this is set to descending so you can also just leave this parameter blank.
Cell M7 = RANK(K7,$K$7:$K$13)
Pull this formula down to row 13, which contains the category just above Other.
This will now show the rank of the categories in descending order, with China having the highest rank.
Add another column with numbers 1 down to 8.
This will be used as the reference to display the country corresponding to the rank number in column O and the corresponding value in column P.
For rank 8, do a cell reference to the Other category.
Cell P14 = J14Cell G14 = K14
For ranks 1 to 7, we will use the Index Match approach to display the country and value. (Here is the link to the Index Match walkthrough.)
The syntax of INDEX() is: INDEX(array,row_num,[column_num])
- array – The range that contains the value you want to display. In this case, it is J7:J13. The Other category is excluded since we already pinned it as the last row of the data preparation table.
- row_num – This tells how many rows the function needs to go down. Since our data is dynamic, we will use the MATCH() function.
- column_num – This defines how many columns it needs to move right. Since we only have one column, we can leave this blank.
The syntax of MATCH() is: MATCH(lookup_value,lookup_array,[match_type])
- lookup_value – The cell that contains the value you want to find. For the first row, it is O7.
- lookup_array – The range that contains the value you want to find. In this case, it is M7:M13. Make sure you have the exact same height as your INDEX() area.
- match_type – 1 for less than, 0 for exact match, and -1 for greater than.
Cell P7 = INDEX($J$7:$J$13,MATCH(O7,$M$7:$M$13,0))Drag this formula down to the last row above the Other.
Do the same for the Value column, but instead of using J7:J13 as the index array, use K7:K13.
Cell Q7 = INDEX($K$7:$K$13,MATCH(O7,$M$7:$M$13,0))Drag this formula down to the last row as well.
We will be using this data prep table to create the bar chart.
In case you want to exclude the Other category dynamically, use the formula below.
Further details will be discussed in a separate walkthrough.
Cell M7 = IF(J7=”other”,COUNTA($J$7:$J$14),SUMPRODUCT(($J$7:$J$14<>”other”)*(K7<$K$7:$K$14))+1)
Creating the bar chart
Highlight the country and value cells in the data prep table (cells P6:Q14).
Under the Insert tab, insert a bar chart.
By default, Excel sorts the categories from bottom to top, with Other showing at the top and rank 1 at the bottom.
To switch this order, go to the Format Axis panel and tick the box for Categories in reverse order.
Add the other necessary elements and remove those that are not necessary:
- Add the data labels by right-clicking on the data series and selecting Add Data Labels.
- Remove the horizontal axis by clicking on it and pressing DELETE.
- Remove the gridlines by clicking on it and pressing DELETE.
Format the chart according to your preference.
For this example, decrease the gap width to 70% at the Format Data Series panel.
In the doughnut chart, you will notice that the first category is the darkest and the last category is the lightest.
To change the colors of the bars, click on the series and go to the Series Fill options.
Tick the box Vary colors by point.
You will notice that when ticking this box, the colors of the series change according to the theme you’ve used for the spreadsheet:
There are different ways to change colors of each point:
- Clicking on each series and changing the color one by one.
- Change the theme of the spreadsheet under the Page Layout This will however change the formatting of all the contents of your spreadsheet.
- Select the chart and go to the Design, then click on Change Colors. You will notice that there are monochromatic options for each color in the current theme. Select the gray scheme.
Continue formatting as follows:
- Remove border. Go to Format tab > Shape Outline and select No Outline.
- Remove the embedded chart title by clicking on it and pressing DELETE. Replace it with a formatted text title.
This chart is a better presentation of the data since you can see all the information in one glance.
You can easily compare the size of each country and see its value right beside it, whereas for the doughnut chart you had to think how much bigger China is in comparison to the USA, and then look down the table to find the corresponding value.
Accounting for duplicate ranks
One thing to consider when using this approach is to account for duplicate ranks.
It is possible to have two of these countries with the exact same percentage for the same number.
If both France and Italy were to have 3.5, notice that under the Rank column it shows both of them as rank 5 and there is no rank 6.
This results in an error in the Index Match for Country and Value columns, which is reflected in the bar chart as well.
To be on the safe side and avoid an error showing in your charts, make sure you have formulas that account for these duplicates.
One way to do this is to use the COUNTIF() function, which counts how many times a number has occurred in a range.
It has this syntax:
COUNTIF(range,criteria)- range – We set this to be fixed from the top row to itself, so when we drag it down, the range expands as well.
- criteria – The value of the country in the current row.
Cell M7 = RANK(K7,$K$7:$K$13)+COUNTIF($K$7:K7,K7)To inspect this, highlight the COUNTIF() portion of the formula and press F9.
This gives you a 1, since the value 12.7 only occurs once until now.
This means that it adds 1 to the original rank.
Since we don’t want that to happen, reverse it out by typing -1 after the COUNTIF():
Cell M7 = RANK(K7,$K$7:$K$13)+COUNTIF($K$7:K7,K7)-1Drag this formula down and you will see that France is ranked 5 and Italy is ranked 6. This is because 3.5 has occurred twice until now, so it adds 2 to the original rank and then subtracts 1 so that we get a unique rank.
Manually sorting the data table
If your report is not done often enough to warrant going through all the above steps to enable automatic sorting, then manual sorting will suffice.
To do this, highlight the area you want to sort.
In this case it is J7:K13.
Make sure to exclude the Other category, since we want this to remain as the last data series in the chart.
Right-click and select Sort.
Select Sort Largest to Smallest.
Then you can use this table as a reference without the need of having the data prep table.
While this is straightforward and easy to do, the advantage of setting up the automatic sorting is that you don’t have to manually sort the data every time you prepare the report, which, in some cases, is easily forgotten.
Feel free to Download the Workbook HERE.
Further reading
Graph Versus Table
Better Tables in Reports
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
