We all know that bar charts can be used to display values spread across various categories or times. We also know that pie charts / donut charts can be used to display percentage breakup of various quantities in a sum total. How about mashing up both to create a Donut Bar chart?
“oh, donut what?!? It sounds like a brand new junk food from dunkin’ donuts”
Well, not really. It is a mash-up or combo chart using which you can display, for eg. sales over last few years along with percentage break-up of individual products. See below to understand.
In the above chart we have mixed a bar chart with a donut chart and sprinkled it generously with a scroll bar form control.
In this charting tutorial, we will learn how to cook a donut bar chart using Microsoft Excel. Scroll down the page to get the downloadable workbook to see this in action.
Recipe for making a donut bar chart
A good donut bar can be healthy while adding variety to your regular menu of charts. To make a neat looking donut bar chart, just follow the recipe.
Ingredients:
Sales data (finely chopped – year vs product level), one donut chart, one bar chart, one scroll bar form control and 5 minutes of spare time.
Step 1: Get your dough, err, data ready
As with any chart, we need the right data in right format to make a perfect donut bar chart.
I have arranged the data for our chart in the below format.
The last column shows the values as per scroll bar position. (more on this in the next steps)
Step 2: Insert a scroll bar control and link it to a cell
Go to developer ribbon tab and insert a scroll bar form control. (learn more about turning on developer toolbar in excel 2007)
Once you have the scroll bar, link it to a cell, say B18.
Also, set the scroll-bar minimum value as 1, maximum value as the number of years you have (in our case it is 14) and incremental change to 1.
Step 3: Determine product-wise breakups and totals based on scroll bar selection
We can use INDEX() excel formula to do this.
What is INDEX excel formula?
INDEX formula (examples) returns a specific value from a range of cells by taking the row and column of that range as input. For eg. =INDEX(A1:C10,2,1) will return the value in 2nd row, 1st column, ie, the value in cell A2.
So, how to write the INDEX formula in our case?
That is your home work. Just use the kitchen sink to experiment.
Once you are done, the product-wise breakups should be listed in a tabular format like this.
Step 4: Put everything together and boil for a minute
We have done all the ground work required to make the donut bar chart. Now, We just need to put everything together and make some charts. Here we have 4 small steps.
- Insert a donut chart using the product-wise breakup data
- Insert a bar chart with the yearly totals
- Insert another series in the bar chart to show the selected year total. Completely overlap this series with the totals series.
- Position everything together.
Finally adjust formatting and colors as per your taste.
That is all, the donut bar is ready for consumption. Serve hot or chilled. The donut bar tastes great with a cup of coffee.
Download the donut bar chart excel work book and play with it
Click here to download the donut bar chart template & workbook. Change the values and chart attributes to understand how this works.
Also try a Bar Pie chart, just follow the same recipe, but replace donut with pie.
Please tell me how your donut bar tasted using comments. 🙂
Recommended charting tutorials for you:
8 Responses to “Top 5 keyboard shortcuts for Excel Charts”
As far as I remember (checked, again, 2 minutes ago) in my "Excel 2013" in order to select various chart elements I need to use the Arrow keys and not the TAB key.
Practically, the TAB key does nothing (within a Chart).
----------------------------
Michael (Micky) Avidan
Thanks for pointing this out. This is how I remember it too, but when I was recording the video yesterday, only TAB key worked. MS must have changed the keys in Excel 2016. I have edited the post to include both keys.
The key navigation on charts is different in 2016.
TAB cycles through a layer of objects (SHIFT+TAB cycles backwards)
ENTER move down a layer
ESC moves up a layer
So on a column chart with title/legend/data labels if you select the plotarea the TAB will go through Title > Legend > Plotarea.
ENTER at plotarea will then select Vertical axis. Tab will take you through
Horizontal axis > gridlines > Series > Horizontal Axis.
ENTER with series selected will then allow you to TAB through individual data points and data labels.
If you ENTER on datalabels you can TAB through each data label.
ALT + F1 : to create default chart
ALT+E S T = CTRL + ALT + V, T : I find that easier to remember
I second what Michael already said about TAB and arrow keys. I can't help but think if this is related to the "," or ";" as separator. I prefer to use the chart tools - layout- drop down box, anyway.
Got to be F11 for instant charting. Highlight your data , hit F11 and voila! ?
Ctrl+1 is the most important chart shortcut. In fact, it works for any Excel object: whatever is selected, Ctrl+1 opens the task pane or dialog to format that object.
Somewhere along the line, maybe when Excel 2016 came out, the arrow keys stopped working to cycle through the elements of a chart. But what works is holding Ctrl while clicking the arrow keys. I haven't gotten used to the Tab and other keys, but as long as Ctrl+Arrow works, I'm good.
And F4 used to be so helpful when formatting a lot of charts. But since Excel 2007 came out, it has been mostly useless. It used to remember a whole set of changes at once, so I get that the newer modeless dialogs make that impractical. But now it only seems to work with formatting of lines and borders, and maybe fills. I find myself writing a lot of VBA one-liners in the Immediate Window to handle these tedious formatting tasks.
after clicking on a chart, is there a shortcut key to copy it?
Thank you for the Alt E S T - tip. This is more than a time saver. Because of dynamic charts or de-activated external references to data when you make the charts, you often have empty charts that are otherwise impossible to format. So this shortcut helps adressing that. I will work with it more and see if there remain some obstacles.