Interactive Donut Chart

While surfing the net, I saw a nice interactive donut chart with an equally nice data table beside it. I wondered whether I can do it in Excel. Of course I did it. Here is how…

This is the picture I saw while surfing (link).

In order to build this interactive donut chart, you need to be familiar with some of Excel’s tools and concepts. Here are what I used while building this chart. I am not going to explain how to use these tools in detail in this post. But you can get that information from the links below:

• Conditional Formatting
• Linked Picture
• Form Controls (Option Button)
• Named Ranges
• Choose Function

First thing to do is to arrange our frame for the chart and data table. Arrange size and color of your cells as shown below. Also you can input your data inside the table at this point. I used 8 pixel for inside border and 12 pixel for outside border but you can use whatever size suits you best.

Now we need a reference cell for selections and conditional formatting. I will use cell B3 on Sheet 2. Activate Sheet 2 and input 1 into this cell. Return back to Sheet 1 and select cells I4 to K4. Open conditional formatting and set format for only background. Set it with the same gray color. Use the formula below as formatting condition:

Range I4:K4        =Sheet2!$B$3=1

You will notice that range I4:K4 will now get a gray color.

Repeat the same process (with same color) for other ranges with following conditions:

Range I5:K5        =Sheet2!$B$3=2

Range I6:K6        =Sheet2!$B$3=3

Range I7:K7        =Sheet2!$B$3=4

Range I8:K8        =Sheet2!$B$3=5

Now we are going to add option buttons to enable selection. Insert an option button and set its cell link as Cell B3 of Sheet 2. Now make 4 more copies of it by using copy-paste. They will automatically have the same cell link (but checking never hurts).

Place your option buttons on the ranges which you applied conditional formatting as shown below:

Option buttons will change the value on cell B3 of Sheet2 from 1 to 5 as you click on them. As that cell’s value changes, conditional formatting rules we set before are going to activate or deactivate. This will create the selection effect.

This part requires a good bit of labor. We are going to build moving circle parts. In Sheet2;

1. Select an area of 14×6 cells and set cell color as gray.
2. Create a summary table from your data
3. Select that table and insert a donut chart next to it. Place it on the gray area.
4. Set border color, background color as invisible for the chart.
5. Also set border and fill color for data series as invisible except “Leisure” series.
6. Set fill color for leisure series as blue and border color as no color.

Here is how it looks:

Labels on a gray area to the right of the chart are formulated to get values from cells J5 and I5.

Repeat this process for other data series (Health, Transport, Foods and Drinks, Other). When you finish, select your gray areas and create a named range for each of them. Here is how it should look like in name manager. You can also open name manager from FORMULAS ribbon and create this ranges in this interface.

When this is finished, we need to create 2 more names as follows. These two names will enable us to make selections based on the value of Sheet2!$B$3.

Name: Chart      Formula: =CHOOSE(Sheet2!$B$3,chart1,chart2,chart3,chart4,chart5)

Name: Label       Formula: =CHOOSE(Sheet2!$B$3,label1,label2,label3,label4,label5)

Now select and copy the first gray area in Sheet 2 that includes blue donut part and paste it as a linked picture to cell B2 of Sheet 1. Click on this picture and type =Chart inside the formula bar.

Do the same with the label but this time place it in the middle of the gray area in Sheet 1.

While on Sheet 1, insert a donut chart as shown below. Format it as follows;

• Set chart background color to No Fill
• Set chart border color to No Outline
• Set series fill color with corresponding color of data series on the table.
• Set border color of data series as gray and border width as 3pt.

Now carefully place this chart on top of other pictures we just placed on the gray area to the left of data table. If you are having problems with pictures and chart are obstructing each other, you can arrange their displaying order by sending them back and forward from FORMAT ribbon of each object.

Congratulations, your chart is finished. You can click on the data table and observe it responding.

 

You can download this interactive donut chart from this link:

Download Interactive Donut Chart

Download FAQ

For any comments, questions or suggestions please don’t hesitate to leave a comment bellow.