Radial bar chart in Excel? Yes, it’s possible. In this tutorial we’ll show you how to create this stunning visualization to compare sales performance. Good to know that radial bar chart is one of the best solutions to create impressive infographic-style excel dashboard. Data visualization is king!
First let’s see a short introduction! After this we’ll take you through the process of creating the chart step by step. Finally we’ll show you the VBA based solution.
What is a radial bar chart?
At some places they call it multilayered doughnut chart because of its layout, but it is better to call it appropriately by its usual name, because its origin is from the bar charts.
It can help you get better perspective on the numbers in your data, whether it relates to sales comparison, production, demographics, and many more.
Differences between Cartesian and Polar coordinate systems
Radial bar chart evolved from the classical bar chart. All the difference between them is only that one of them uses polar, and the other Cartesian coordinate systems.
Let’s see the comparison with a help of a simple figure. On the left hand side we can see the info-graphics-style graph and on the other hand the classical bar chart.
Even if you not familiar with data-visualization you can easily decide that the bar chart is very easily understandable at first glance. But the radial bar chart represents a much higher level in visualization (it is a lot more spectacular).
What is the reason behind all this and when is more useful to apply one or the other solution?
As we have said before radial bar chart is a tool capable of higher value data-visualization. The problem with it that our eyes used to boring, ordinary charts and may misinterpret its meaning. Every single column (bar) closes a different angle so the differences look relatively larger / smaller. This is most true about the outer rings.
Finally we can come to the following conclusion:
Use bar chart if the goal is to create an old-school report. But if our object is to build an impressive presentation we can bravely choose the new generation of data visualization.
If you follow this subject closely surely have seen that for example the Tableau is able to create the chart in question. The time has come finally to enable Excel for this task.
How to create radial bar chart in Excel?
Steps to create the base chart
In this detailed tutorial we’ll show you how to create a radial bar chart to measure sales performance. This special Excel graph is useful for sales presentations and reports. Let us see the initial data set! We’ll compare 5 products.
Step 1: Check this range below! You can include the Product Name and Sales fields. The first field is placed in the column ‘B’, the second field in the column ‘C’. We’ll build a colorful radial bar chart in Excel using this data set.
Step 2: We’ll insert a helper column using column D. Enter the formula “=MAX(C12:C16)*1.30” on cell D11.
Step 3: Add the formula “=$D$11-C12” to cell D12 and fill down the formula using the range “D12:D16”
Step 4: Select the range “B11:D16”. Go to ribbon and Insert tab, Chart and Insert a Doughnut Chart.
Step 5: Click on the inserted chart. Select the chart area. Right click on the chart, finally click on “select data”. A new popup window will be opened.
Step 6: Click on Switch Row/Column button.
Step 7: Now right click on the doughnut and click on Format Data Series. Choose the donut hole size slider and set it to 25%.
Step 8: The most important step! Select the outer ring. Use the right-side pane and fill the orange area as No fill. Repeat these steps for the inner slices too.
Step 9: Select the first blue slice and fill it as your favorite color. We strongly recommend using divergent color schemes. You can create stunning schemes using ColorBrewer.
Prepare the labels for radial bar chart
First create a helper column for the data labels on column E. Then enter the formula =B12&” (“&C12&”)” on cell E12. You can use the CONCATENATE function also. Finally fill down the formula for “E12:E16”.
Go to the Ribbon, click on Insert tab. Insert a Text box.
Now we’ll create a linked cell to Text box. Select the inserted text box. Jump to formula bar and type: “=$E$12”.
Insert the 4 more text boxes and link with cells D13, D14, D15 and D16.
Format the labels together to create Groups
Hold Ctrl and select the five text boxes. Right click then choose Group from the list. You can apply various formatting methods. Use No fill to create transparent background for text boxes. Remove the border and choose your favorite font.
Our radial bar chart is ready to use!
Radial bar chart add-in for Excel
As you could see it is not rocket science to create such visualization in Excel. For sure it needs manual work but it’s manageable with some time spent. But what if we need to make ten, twenty or even more charts in a short time?
Excel doesn’t contain this type of chart as a default. Fortunately there’s an automated solution for this problem (also).
UDT is a professional data-visualization and chart add-in for Excel contains many special chart functions.
All together in two steps (in a mare of seconds) you can create spectacular presentations. After giving the initial data sets with only one click the dynamical radial bar chart is done. I think we don’t need to explain how much easier this makes the life of those who involved infographics on a daily bases.
The add-in is very easy to use and best of all, you don’t have to go outside Excel to get these done. The workflow is easy and allowing you more productivity. You can focus on your primary business goals!
The “relationship” between Excel and radial bar chart didn’t start easy because we had to fill a missing function. For example if we meet sales comparison type of tasks we strongly recommend its use.
If you are developers we recommend you to use VBA to create special charts. Virtually you can automate EVERYTHING with its help that has anything to do with Microsoft Office. Let it be Excel, Word or even PowerPoint.
Soon we’ll continue our series where we’ll create never before published tutorials for your benefit.