New Excel Trick (for me): Dynamic Charting

While I wait to get the word "go" from the government (hopefully not much longer...I'm crossing my fingers although it's taking a while to get the approval on my software), I've been working on another project that recently popped up a week or so ago. I'm trying to develop a quasi-visual reporting system that's easily updated each month, once accounting has done their piece. The goal is to project various aspects of the company in a digestible fashion. At the end, I hope to build a "snapshot of the hotspots" - a summary report showing the critical things that need watching.

But first, I need to build the details that will roll up to the various summaries. And then those summaries up to a higher level of summary. Right now, I'm thinking of overall corporate level, then team level and then individual level.

One of the things I've recently been playing with is the concept of dynamic charts. For those of you who don't know what dynamic charts are, they are charts that expand and shrink, depending on the amount of data being charted. So, in my instance, I may have three divisions, each having a different number of managers (one division would have 30 managers, another 3, and the third 10 managers - as an example). So your chart needs to expand or shrink the amount of data it will capture. Or say you want to offer different timescales as options for charting: monthly, quarterly, years. Again, your range of data needs to flexibly change to fit the appropriate number of data needed to chart.

There is a function for that: OFFSET. The basic structure being used for dynamic charting is

OFFSET(cell in upper left hand corner or starting position, 0, 0, COUNT(range of data), COUNT(range of data)

The cell in the upper left hand corner is typically the first cell in the range of data you want to chart. This is the cell you want to offset from. The two zeroes after the upper left hand corner cell represent the row and column of the cell you are offsetting from the upper left hand corner. In dynamic charting, zeroes are typically used and they mean you don't budge from the upper left hand corner. If the numbers have been 1, 1, you would start charting from 1 row down and 1 column to the right of the upper left hand corner. The next 2 COUNTS represent how many rows down and columns over to the right you want the data range to be. It is commonly described as the height and width of the range. So a count of 1 for each counts mean you would not move from your 0,0 position. A count of 2 for the row and column would mean that you would move from 0,0 down 1 row and 1 column to the right.

Some examples might help clarify.

• Offset(A1, 0, 0, COUNT(a1:a10)=1,COUNT(a1:j1)=1) where COUNT(a1:a10) counts the number of rows that contain a number, and COUNT(a1:j1) counts the number of columns that contain a date. The resulting range is A1.
• Offset(A1, 1, 1, COUNT(a1:a10)=1,COUNT(a1:j1)=1). Now your range is just B1.
• Offset(A1,1,1,COUNT(a1:a10)=2,COUNT(a1:j1)=3). The range depicted is now B1 to D2.

Now, you don't have to have the two counts in your OFFSET formula for dynamic charting. It may be more common to have only one count. For instance, in my dynamic charting, I had OFFSET(cell, 0, 0, COUNT(...),1).

A final element you need to do the dynamic charting is that you need to choose a range where your charting data will reside and range name it. The range name will include the OFFSET formula.

I won't go into all of the details, but if you google "dynamic charting excel", you will bring up a lot of sites that will explain in more detail how to set up the dynamic charting using OFFSET and range name. For my purpose, I want to talk about a particular problem I ran into that I didn't see addressed in the excel blog sites. I was trying to use dynamic charting with histogram charts. I think histogram charts come in with Excel 2016 only - yes, I don't see it in my 2010 version. You can retrieve the histogram from the analysis tool pack in 2010 Excel version but I don't think it's amenable to dynamic charting. You have to refresh the histogram each time the data changes. I want it to be automatic.

When I tried to use the dynamic charting technique with the histogram chart type in Excel 2016, the range would not dynamically change. So lesson number 1 is you cannot use dynamic charting with a histogram chart type. You need to use the FREQUENCY function to build your bins and counts for a histogram chart.

The second thing I learned is that the cells in the OFFSET need to be absolute or the cells will change each time you finish doing the range name. For example, say I type OFFSET($A$1,0,0, COUNT($A1:$A$10),1) instead of OFFSET($A$1,0,0, COUNT($A$1:$A$10),1). The range will change in ways you would not expect. You must have the dollar signs to lock in the ranges.

Dynamic charting is really useful, but you have to watch out for these little quirks.

Lastly, some sites you might be interested in checking out: Chandoo.org and Peltier.