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 )”
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…
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…
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….
Is really this…
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…
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!
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:
Please send me a note on the contact form below if you have a guest post that you would like to contribute. Thanks