Doughnut Chart / Spiral Chart

A couple of posts back I demonstrated how to create a box chart. Here is thepost.

Today I wanted to demonstrate how to display the same data as a spiral graph. What’s a spiral graph you ask? Basically it’s a doughnut chart. Let me show you… In the above example I compare 3 leaf blowers based on Satisfaction, Price and Durability. Each category and item is separate. Well for the spiral graph I want to compare each category separately. I want to compare all three blower types for Satisfaction in one graph, Price in a second and Durability in a third.

To start my spiral graph I go to my data tab. For the green leaf blower I have the following data. The first column of numbers is the results of the survey. The second column of numbers is 100 – the first column (100 – 53 = 47). I need both of these numbers (53 and 47) to create the first spiral of my graph.

Satisfaction	53	47
Price	41	59
Durability	62	38

So I start by highlighting Satisfaction, 53 and 47. Then I choose Insert > Other Charts > Doughnut Chart

This creates my first spiral in the doughnut chart.

Now I want to change the blue of the doughnut to match the green color of the Satisfaction bar chart. I select the blue by mouse clicking on it. Now you may find that when you click on the blue Excel selects both the Blue and Red colors. No big deal, just click on the blue a second time and try again.

From the format tab, change the shape fill to be green.

Repeat the process for the red portion of the doughnut but select no fill instead of a color.

When Done I have my first section of my chart.

Now I can clean up the chart by removing the legend to the right by clicking on it and hitting the delete key.

Next I want to add the Yellow Satisfaction to my chart. I right mouse click on my green spiral and choose Select Data.

This opens the Select Data Source dialog. Click the add button. For Series name select cell A20 and for series value enter =Data!$B$20:$C$20

This adds the second spiral to the chart. I repeat the process above to change the color blue to yellow and no fill on the red portion.

Finally again I repeat the Select Data and add the red Satisfaction data.

I modify the blue color to be red and no fill the other section.

I am now left with my spiral graph.

I add the chart title “Satisfaction” by selecting Chart Title centered Above Chart from the Layout tab.

Now I can add a black edge to each color in my chart if I wish by using the Shape outline option just below the shape fill shown above.

Next I cut my chart moving it from the data tab to my Dashboard tab and resize it.

I repeat the above process for Price and Durability. When done I have displayed my results two different ways, by bar chart and by doughnut chart (spiral graph).

To add some additional detail I add text boxes for numbers and a line and circle from the Insert tab > Shapes.

How can you use the above techniques to add some wow to your next dashboard?

If you want to download the example spreadsheet, click onthe following link.

Camera Tool

Last week I demonstrated how to create a box chart. Didn't see the post? click here

Someone wrote to me asking why they would ever want to use a box chart when they could use a simple column chart to represent the same data. Well it’s a good question so let’s look a bit closer at the two techniques.

Here’s a simple dashboard comparing three different types of leaf blowers. It also shows the box charts compared to column charts.

Notice how I matched the leaf blower color in my box charts as well as my column charts. This helps provide continuity to the dashboard. Both the box chart and column charts show the same information, but the way it the data is represented is different.

And that’s the point. Excel allows you to represent data in different ways. Most people will settle for the built in charts Excel provides and that’s fine. However there are alternatives and the box chart is a good example of this. It’s a creative use of conditional formatting that provides a user the flexibility to display their data differently.

The person who emailed me also raised a valid point regarding the columns and how I needed to size them to make the box chart. He pointed out that it would be difficult to properly display data under these boxes due to the column width. I agree. It would not be ideal to place data under the boxes. However there is an easy solution.

Notice the columns D – M, O – X and Z – AK in the below example. These are the columns that the reader was referencing. They are very small in width.

To get around this problem, you can use the camera tool to place the image of the chart onto your dashboard. The actual chart itself can be on a different tab. And of course since you are using the camera tool, the image is not static (it will change as your data changes).

In the attached example I have my box charts on the data tab and the Dashboard “links to the box charts” by the use of the camera tool.

Now if you are not familiar with the camera tool, you should become acquainted.
The camera tool is a hidden feature in Excel that allows you to copy a rectangular area in your workbook and then creates a mirror image of the area as a drawing object. You can then move and resize the object. If the contents of original rectangular area changes then the mirror image also changes.

To use the camera tool, you must add the tool to a tool bar in excel menu area.

From the menu Click the drop down arrow on the tool bar and select More Commands

1. From the menu Click the drop down arrow on the tool bar and select More Commands
2. In the Customize tab and select All Commands.
3. Scroll down in the commands area until you see a little camera icon.
4. Click the Add Button then the OK button to add this to your tool bar.

To use the camera tool on our box chart first highlight the chart. When I select my cells they are outlined with a black boarder.  Next click on the camera icon that we added to our tool bar.

Now click anywhere in your worksheet and Excel will places a snapshot of the range you selected.

 Now you can move and resize this as needed.

Notice how my column widths are normal size on the dashboard tab.

So using the camera tool allows you to get around the problem of column widths.

The camera tool is a great feature in Excel but there are some issues that you need to be aware of. As shown here if you resize an image too small you will loose resolution. Also if you change the aspect ration of the image, you will get distortion. 

Need to see an example? Download the worksheet by clicking on this link.