Problems With Dynamic Charts in Excel

A couple of days ago, I wrote about creating dynamic charts in Excel, and allowing users to select a date range from drop down lists. At the end of the article I warned that the dynamic values would disappear if the entire range is selected, in Excel 2007.
Since then, I’ve done some testing, to see when the values stick, and when they disappear.

Excel 2007 Tests

First, I tested Excel 2007, with and without an Excel Table, and using different formulas for the named ChartDates range.

Table Dyn Lost ChartDates Range
Yes Yes =Table1[Date]
Yes Yes =OFFSET(Chart!$A$1,1,0,COUNT(Chart!$A:$A),1)
Yes Yes =Chart!$A$2:$A$23
No No =OFFSET(Chart!$A$1,1,0,COUNT(Chart!$A:$A),1)
No No =Chart!$A$2:$A$23

With the data in an Excel Table, the dynamic chart values were always lost, if the full date range was selected. So, the best option in Excel 2007 is to avoid an Excel Table, and use a dynamic ChartDates range.

Excel 2003 Tests

I hadn’t noticed the problem of disappearing dynamic values in earlier versions of Excel, but decided to test the Lists feature in Excel 2003.

List Dyn Lost ChartDates Range
Yes Sometimes* =OFFSET(Chart!$A$1,1,0,COUNT(Chart!$A:$A),1)
Yes Sometimes* =Chart!$A$2:$A$23
No No =OFFSET(Chart!$A$1,1,0,COUNT(Chart!$A:$A),1)
No No =Chart!$A$2:$A$23

*If the entire range is shown in the chart, and you add a new item at the end of the chart data, dynamic values in the chart are lost. However, you can select the entire range, without losing the dynamic values.
With the data in a List, the dynamic chart values were retained, except as noted above. So, the best option in Excel 2003 is to avoid a List, and use a dynamic ChartDates range.

Control the Start and End Date Selection

In the creating dynamic charts in Excel post’s comments, Jeff Reese asked how you could ensure that the user selects an End Date that’s greater than the Start Date. Michael Pierce suggested a dynamic range for each of the drop down lists.

My suggestion would be to create named ranges for the Start and End Date, using the same offset technique as is used for XValues and YValues. The StartValues range would start at the beginning of the list and continue through the EndDate – 1. The EndValues range would begin at Start Date + 1 and continue through the end of the list. That way, the list of valid end dates is updated any time a selection is made for either date.

Derek suggested using the earlier date as the Start Date and the later date as the End Date:

Alternatively, have the start date and end date be replaced in the formulae with MIN(start date, end date) and MAX(start date, end date), ensuring that whichever is the greater is used as the end date. I assume the graph would just look silly if the two dates were the same, not broken.

Calculate the Start and End Dates

Michael’s suggestion would work well, but users might have problems switching from an April date range to a May date range.  The May end date would have to be selected first, to make the May start dates available.
I decided to go with Derek’s suggestion, and use the earlier date as the start date, whether it’s in the Start Date cell, or the End Date cell.
The CalcStart formula:
The CalcEnd formula:
I named the date calculation cells as CalcStart and CalcEnd, and used those names in the XValues definition:

Download the Revised Sample File

You can download the revised Excel 2007 sample file for Dynamic Date Range Charts. In it, the Excel Table has been removed, and the start and end date calculations added.