Dynamic Chart Info

Latest&last update - just format tweak + 2nd chart for period selection.
Screenshot: http://bit.ly/4s3rjp
Excel file: http://bit.ly/8HwVSm

More choice = more fun!
I've just installed xl2010(beta) on my desktop and in the first place corrected one bug which showed up only in new excel. I added some transparency (sorry xl2003 users but probably your visual experience with my file will be slightly lower now - but it's only a question of tweaking 3 chart series color - average lines and grey area). I am not a fun of transparency but in this case it enabled to see gridlines under area chart and thick average lines.

Summering up the functionality implemented so far:
  • User can pick one category (out of 4) and main performer within selected category to visualize it's sales progress in selected period of tim
    • performers selection updates depending on main category picked
  • Selected performer will be always visible on the chart - as a thick green line
  • Time frame can be adjusted on 2 scroll bars:
    • horizontal scroll selects fist month to plot,
    • vertical bar narrows number of months presented (from 24 to 2)
    • in effect user can very specifically narrow charting scope (eg. 4/2008 to 9/2009),
  • User can filter it's selection by choosing members of all 3 non-selected categories
    • User can also can unfilter by selecting 'All'
    • Filter selection updates depending on main category picked
  • User can plot area of all performers activity within selected period
  • User can analyze sales of all performers at ones (as colored thin lines)
  • User can plot an average sales in selected period for main performer
  • User can plot an average sales in selected period for all performers
  • User can easily find best and worst month for selected main performer - visible on horizontal axis as green and red triangles
  • As each option is selected independently user can adjust amount of information presented
Excel fie; http://bit.ly/6v0UyS
Screenshot: http://bit.ly/6JEVm1
New excel format can be picked in download section (version 6 xlsm) - but I am not sure about compatibility with xl2007 (in xl2010 it works).

28-11-2009 Update:

I came to the limit of sumproduct formula usability (speed of my chart dropped with the previous version and  I didn’t feel well with those lags). Therefore I decided to omit my first assumption and I added pivot table so that I can use much more efficient GETPIVOTDATA formula.

Macro wouldn't be needed if I didn't put 3 filter combo with "All" option available with previous version. As GETPIVOTDATA can't (as far as I tested it) deal with them properly I put some code which hides a pivot field which has 'All' attribute selected by user.

Excel file: http://bit.ly/6d0gQu

One pivot in background + 35 lines code (very basic one) and we have speeeeed.....

No new features but no more lags (not tested in xl2007).

27-11-2009 Update:

I added a new version:

  • I added 3 more combo boxes - for filtering (dynamic content & number of records in each),
  • I added 3 Checkboxes (just user preference),
  • Cleanead layout of Filter Sheet - now it's easier to look around filter sheet.
Those 3 more combo are big step forward in functionality - but step back in performance. Sheet should be still OK to work with - but it is probably max usage of sumproduct formula
I created this site to present my version of dynamic chart for 'Visualization Challenge #2' contest launched on Chandoo blog.


As I didn't have enough fresh ideas to make whole dashboard I focused on one chart only. My basic inspiration was a chart with one series highlighted I recently saw on one of the popular Blog (probably PTS Blog but I am still looking for it). Dynamic features applied:

  • 2 dynamic combo boxes (2nd combo updates based on 1st combo selection),
  • 2 scroll bars to select time frame (1 scroll for select start date, 2nd for select number of months displayed),
  • max/min indicators on X-axis - for period selected.

Screenshot: http://bit.ly/6vLJLF
Excel file:    http://bit.ly/6wIEnA


The chart doesn't use any runtime VBA code. I used only one code in design time - to unpivot presented data (to put them into vertical layout which is more flexible and natural to use for me). I attached the code in separate file in case you would find it useful http://bit.ly/7SSiXK. Just remember to have all text fields on the left side of first value field. I re-randomized data - to have some categories crossed.


As I didn’t use VBA I found putting pivot table to be to frustrating (formatting!!!). Therefore I exclusively used excel built-in formulas.


I haven’t tested my chart performance on xl2007 yet. On xl2003 chart refresh speed for filtering in ComboBoxes should be OK, for scrolling: moving scroll point or clicking scrolls is acceptable but clicking arrows sometimes jams for several seconds.


Thanks for your comments.