How-to Make a Dynamic Excel Pie Chart with 4 steps in less than 4 minutes

This is an awesome guest post from our great friend Pete.  He came up with this awesome technique to make a dynamic pie chart without the use of offset.  THANKS PETE!

 

Recently I saw some comments come through for the post “Create a Dynamic Excel Pie Chart”, and it made me re-read the post. I noticed this part…

 

“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 clip_image002)”

 

And that got me thinking…there has to be a better, or at least, more simple way to do this. So I got to work. J

The solution turned out to be very simple to implement and it used tables, so your formulas will auto expand to fit your growing set of data!

First, I changed the layout of the data slightly from this…

clip_image004

To this…

clip_image006

Which I made into a table using CTRL+T. The Total column is a sum of the data in that row, and in table syntax the formula reads: =SUM(Table1[@[Org]:[Em]]).

Now I needed a way for the pie chart (which we haven’t created yet) to pull only the last entry in the table. I set up a “Data for Chart” area next to my table, which looks like this…

clip_image008

The current month cell is an INDEX and COUNTA combination: =INDEX(Table1[Month],COUNTA(Table1[Month]))

The INDEX(Table1[Month] portion indexes the ‘Month’ column of the data entry table, and the COUNTA(Table1[Month]) portion counts how many non-blank cells there are in the column. COUNTA returns 4 and that is in the row portion of the INDEX formula, so it returns the item in the 4th row of the indexed ‘Month’ column, which happens to be the last entry, or Apr.

This same technique is used for the data portion of the Data for Chart area….

So this…

clip_image010

Is really this…

clip_image012

The Table1[[Month]:[Month]] portion of the above formula is not necessary, but it makes it easier. It is a column lock relative reference in table syntax, so that when you drag the formula to the right it remains pointed at the ‘Month’ column. It is the same thing as using $A1 as a reference for cell A1 with a column lock. This just means that I only had to write the formula once and drag it over instead of creating it 5 times for the Org, Dir, Ref, Em, and Total columns.

Next, I inserted a Pie Chart from the new data set, and made the chart title dynamic by clicking on the title and then placing =Sheet1!$K$3 in the formula bar. This allows the title object in the chart to be referenced to a single cell, which in this case has a formula in it to state the last month entry in the data entry table.

The whole setup looks like this…

clip_image014

It is fully dynamic and automatically expanding when entries are made in the Data Entry Area. There are no named formulas or VBA, just an INDEX and COUNTA combination.

 

What an awesome solution Pete.  Thanks again!

 

Video Demonstration

Here is the video tutorial where you can see all the steps detailed in just 4 minutes 🙂

Free Sample File Download

You can download the free pie chart template file here:

How-to Make a Dynamic Excel Pie Chart with 4 steps in less than 4 minutes

Please send me a note on the contact form below if you have a guest post that you would like to contribute.  Thanks

 

Steve=True