Dynamic Charting
You can make your charts dynamically- grow or shrink with the number of data items, and also
- sort themselves in different ways, based on user inputs.
Making your chart grow or shrink with the data
Charts can be hard to maintain if the number of data items changes and you have to change all the series formulae each time this happens, so your chart doesn't have big white spaces, or leave out data. How can you get a chart to include more data automatically, or to shrink when you delete data?
The trick (attributable, I believe, to Stephen Bullen) is to use range names for the label and data ranges for each series, so instead of the series formula looking (say) like this
=SERIES(C101,B102:B106,C:102:C106,1)
it will look like this...
=SERIES(C101,Series1_Labels,Series1_Values,1)
Why? Because we can't make a fixed range like B102:B106 change, if we add more data. However, you can put formulae in range names which can change the cells they point to.
For example, you can define a range called (say) CountData and give it the formula
= COUNTA(B102:B115)
This counts the number of non-blank items in the range B102:B115 and returns that number. So if I have a cell with the formula =CountData, it will always show the number of non-blank items in B102:B115, and as I add more items in this range, the number will increase.
To define the range, you will have to use Insert, Name, Define, from the menu to bring up the Names dialog. Then you type in the name at the top, and the formula at the bottom, as shown here.
So now that we can make range names that are "dynamic", how can we use them to make charts grow and shrink with the data? It's just a case of using the right formula for the range name. Take my example above, where
- the labels start at B102 and may go as far as B115, and
- the data series similarly starts at C102 and may go as far as C115, then
these are the formulae we need to make the chart use exactly the number of data rows provided.
Labels: =OFFSET(B102,0,0,COUNTA(B102:B115),1)
Values: =OFFSET(C102,0,0,COUNTA(B102:B115),1)
This version of the OFFSET command returns a range of cells. It needs
- the cell to start from (B102 or C102),
- the number of rows and columns to move (zero for both), and then
- the number of rows to include, which is where we use COUNTA to give us the number of actual data rows
- the number of columns to include, which will be 1
So what the Labels formula gives us is a range consisting of all the cells between B102 and B115 which have something in them. Similarly, the Values formula gives all the data cells between C102 and C115.
If we use these range names in the chart series, as I showed higher up, the chart will now respond dynamically as you add or remove data.
Dynamic sorting
Suppose you'd like your users to be able to show a chart sorted alphabetically, or by size, or by something else.
Perhaps you'd also like to let your users choose the number of items to show (eg the "top 5" or "top 10"), or else allow them to pick which items they want to show on the chart, from a list.
This is hard to explain outside of a spreadsheet, so I suggest you look here. It doesn't use any magic tricks, just some hard working Excel functions. But it looks like magic, and that's what counts.
(By the way, if you page up in that example spreadsheet, you'll see it also has an example of using pictures to show charts, which I explain here on this site. The reason is that it comes from a set of notes I created for a university course on modelling).
Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.