Select Excel Chart Dates From a Drop Down List

Instead of showing all the data in a chart, you can select a specific date range, and show only the data from that period. In this example, drop down lists of dates are created with data validation. Select Excel chart dates from a drop down list, and the chart changes to show the new range.

To create this dynamic chart in Excel 2007, you can create a table from the chart data, then create named ranges, and use those names in the chart.

Create an Excel Table for the Chart Data

[Note: Please see the post, Problems With Dynamic Charts in Excel and ignore this step — the Excel Table may cause problems.]

1. On the Chart sheet, select a cell in the chart data, e.g. cell A1
2. On the Ribbon, click the Insert tab, then click Table
3. In the Create Table dialog box, click OK, to create the table for the selected range.

Name the Start Date and End Date cells

1. On the Chart sheet, select the Start Date cell (F17)
2. In the Name box, type StartDate then press Enter, to name the cell
3. Select the End Date cell I17, and in the Name Box type EndDate, then press Enter

Name the Chart Date Range

[Note: Because you did not create an Excel Table, as advised above, use the following formula in step 3, to create a dynamic range for the ChartDates:
=OFFSET(Chart!$A$1,1,0,COUNT(Chart!$A:$A),1)

1. On the Ribbon, click the Formulas tab, then click Define Name
2. Type ChartDates as the name for the range
3. Click in the Refers To box, and select the Chart Dates (A2:A23) on the worksheet.
   Because this range is in an Excel Table, the reference will automatically change to show the
   table name and column name, Table1[Date]
4. Click OK

Create the Start Date and End Date dropdowns

1. On the Chart sheet, select the Start Date cell (F17)
2. On the Ribbon, click the Data tab, then click Data Validation
3. From the Allow dropdown, select List
4. In the Formula box type: =ChartDates
5. Click OK, then repeat these steps to create a dropdown for the End Date.

Define the named ranges for X and Y values:

1. On the Ribbon, click the Formulas tab, then click Define Name
2. Type a name for the series: XValues
3. From the Scope dropdown, select the data sheet name, Chart
4. Type a formula that finds the start and end dates in the data:
   =OFFSET(Chart!$A$2,MATCH(StartDate,ChartDates,0)-1,0,
   MATCH(EndDate,ChartDates,0)-
   MATCH(StartDate,ChartDates,0)+1,1)
5. Click OK
6. Follow the same steps to define the YValues named range, using the following Settings:
   Name: Yvalues
   Scope: Chart
   Refers To: =OFFSET(Chart!XValues,0,1)

Create the chart

1. Select a cell in the chart data
2. On the Ribbon, click Insert, then click Line in the Charts group
3. Click the first Line chart option
4. To hide the Legend, click the Ribbon’s Layout tab, click Legend, and click None

Make the chart dynamic

1. Select the chart, and click the line to select it
2. In the formula bar, change the series formula, replacing cell references with range names:
   =SERIES(Chart!$B$1,Chart!XValues,Chart!YValues,1)

Test the chart

1. Select a start and end date from the drop down lists, and the chart will display that range

Warning: If you select the entire range, the series formula will revert to
absolute cell references instead of the named ranges. (This doesn’t happen in earlier versions of Excel.)

Download the Sample File

You can download the completed Excel 2007 sample file for Dynamic Date Range Charts.

[Note: Please see the post, Problems With Dynamic Charts in Excel and download that sample file — the Excel Table may cause problems.]
_________________