Create a Dynamic Excel Pie Chart

Recently, I viewed a forum post where a user wanted to create a dynamic pie chart that will change the series based on different data points.  So it is similar to a dynamic chart series but it is a little different then adding a new data point to a dynamic Excel chart series and instead changes the chart to a new series all together.  This is more like what a Pie Chart does than say a line chart.

Here is a visual display.  The user wanted to have an excel pie chart that started with month of March data series and moved to the month of April data series when he entered it into the Excel Spreadsheet.

From thisimage to thisimage

When he enters data:

From thisimageto thisimage

Well no problem, we can do this in Excel.  However, I really always stress that users should use Tables as much as possible when creating dynamic Excel Dashboards, but in this instance, I can’t think of a better way than using the Offset function and actually, I can’t think of another way (so if you know of one that doesn’t use VBA or the Offset formula, please let me know in the comments) – (well maybe some combination of the direct formula, but that is the topic for a future post Smile)

 

The Breakdown

1) Setup Your Data and Create a Pie Chart

2) Define a Name with the Offset Formula

3) Edit the Excel Pie Chart and Change the Data Series with Your New Current Data Named Formula

4) Create a Chart Title Cell

5) Link Your Chart Title to the Chart Title Cell

6) Watch in Glee as You Save Yourself Time Every Month

 

Step-by-Step

1) Setup Your Data and Create a Pie Chart

Setup your spreadsheet data in any fashion that you like as long as you can make a pie chart out of it.  Here is what mine looks like:image

Now I am not going to chart the totals in my pie chart.  Instead, I am going to chart cells A3:D7 so that I exclude the totals since they will not be used in the Pie Chart.  So highlight the range A3:D7 and insert a pie chart from the Excel Insert Ribbon. Your chart should look like this when you are done:image

2) Define a Name with the Offset Formula

Now as I showed you how to use the Offset Formula in Excel Charts Postings that you can find here:

How-to Make a Dynamic Chart Using Offset Formula

We need to Define a Named Formula in Excel to make the Excel Pie Chart.  This is slightly different than other Excel Tutorials that I have posted because it moves the entire range and not just adding an additional data point to the chart.

To create a named range using a formula, we must first click away from the chart to anywhere in the spreadsheet as this will not work when you have a graph selected like the pie chart.

Then click on the Formulas Ribbon and then choose the Define Name button go to the Formula Ribbon and you should see this New Name dialog box:SNAGHTML1be6e957

Then put any old name in there that you choose, but DON’T put any spaces in the name as this will cause you problems.  For my example, I will put the Name of “CurrentData”

Then tab down to the “Refers to:” box and put in the following formula:

=OFFSET(Sheet1!$A$4,,COUNT(Sheet1!$4:$4),4,1)

To learn more about the Offset Formula, please check out this URL:

This is the Bomb: or How I came to love the Offset function

It will look like this:SNAGHTML1be87bc6

You will know if the Named Range Formula is working right if you click into the “Refers To” box and it shows you this:image

3) Edit the Excel Pie Chart and Change the Data Series with Your New Current Data Named Formula

This is the critical step to making the Dynamic Pie Chart because without it, your chart will not update the data series.

First click on the chart and then choose the “Select Data” button from the Data group in the Design ribbon.

image

Now you should see the Select Data dialog box.  From here you need to click on the EDIT button in the Legend Entries (Series) area.SNAGHTML1c59fe8e

You should now see the Edit Series dialog box and we want to change the “Series Values” to

=Sheet1!CurrentDataSNAGHTML216660a5

It must be entered this way with the Sheet name ! Defined Name or it will not work.  Press OK on this dialog box and the Select Data Series dialog box and this step is complete.  You can read and see more about “Creating Dynamic Charts with the Offset Function” here:

 

Case Study – Creating a Dynamic Chart in Excel Using Offset Formula

4) Create a Chart Title Cell

Most people put Chart Titles in their Excel Pie Charts.  This might even be recommended to give the chart context.  But when you create a Dynamic Excel Pie Chart, you will also want to make the Chart Title Dynamic.  Here is how you do it.

First, pick a spreadsheet cell where you want the Dynamic Chart Title to reside.  Based on the data presented above, this finds the last month in row 3 on sheet 1.  Your data may be different, but this will work for this example.  I am putting this formula in cell C1.

=OFFSET(Sheet1!$B$3,,COUNTA(Sheet1!3:3)-1,1,1)

This formula will find the last category entry for the pie chart and capture it for the Chart Title.

Notice that in Cell C1 has a value of “Mar” in it because March is the last value that it can find in row 3.

image

Now I have added “Apr” to cell E3 and by doing so, cell C1 is changed to “Apr”.  This cell will be used in the next step to create our dynamic Excel chart title.

image

5) Link Your Chart Title to the Chart Title Cell

Okay, your chart is now updating as you add new series to the data.  Try it out and you will see that if you add Apr to the data set that you will see it update the chart, but it is not updating the Chart Title.  Well now that you have completed the previous step, this will be easy.

Click on the Excel chart.

Then Click on the Chart Title

Then press your “=” (equals) sign and use your mouse to select your Chart Title Cell (cell c1) and then press the Enter Key.  This will now link the Chart Title dynamically to the cell of C1 and C1 is dynamically finding the last value in row 3 to update its value constantly.

More examples of creating linked chart titles to an Excel spreadsheet cell can be found here:

How-to Make an Excel Chart Title Change Dynamically

You should be all set and as you add new data points to your chart data range, Excel will dynamically find the last entry and update your Pie Chart automatically!

image

6) Watch in Glee as You Save Yourself Time Every Month – This step involves a mirror and I think you can take your own video of this step Smile.  Nothing else needs to be done but enter new data and watch your chart change every data period of your choice.

 

Video Tutorial

Watch how to make a Dynamic Excel Pie Chart here:

Please let me know if you have any questions on this topic in the comments section of my blog.  Also, don’t forget to sign up for my email list by clicking on the RSS feed button.  That way you will be sure to get the next post in your email inbox as soon as it is released.  I will also send out special offers to any one on this list.

 

Steve=True

2 years ago by in Chart , Dynamic , Offset , Pie Chart | You can follow any responses to this entry through the RSS feed. You can leave a response, or trackback from your own site.
3 Comments to Create a Dynamic Excel Pie Chart
    • LC
    • Great help to get me started into things more complex:) !! I cannot solve how to do this ‘picklist – dynamic chart’ with data that has TWO columns per series … eg. for each product you have a ‘sales’ column and a ‘objective’ column … I’m trying this with two product (so 4 columns) and the offset formula is not working …. any thoughts?

      Thanks again!

    • Lisa LePome
    • I keep getting an invalid reference error when I try to use the named formula in the chart’s data series. I get this even when I use the autocomplete successfully, so I think I’m entering the name correctly

      • SteveEqualsTrue
      • Hi Lisa,

        Make sure you have the sheet name infront of your named formula. Excel needs the whole string. i.e. =Sheet1!formulaname

        Hope this helps. Steve=True

Leave A Response

* Required