Dynamic Ranges
While there are different ways of doing this, the method demonstrated here is the most flexible and involves your assigning the chart data range to a named range and then having that name update with a formula based on Excel's OFFSET function.
Changing the time period of the chart
Here's our worksheet, we want our chart to plot the three data ranges Tom, Dick and Harry over the time period defined by the Start Date and the End Date, namely May 2015 to Sep 2015. |
| Worksheet showing the data ranges |
And here's the corresponding chart, a simple line chart showing the three data ranges and the time period on the x axis. We want to be able to change the starting and ending dates by changing the dates in cells C2 and C3 and then have the chart update automatically.
 |
| Data period plotted from May to Sep 2015 |
 |
| Validation Lists |
We change the dates by choosing a new value from the drop-down validation list which is linked to row 5 on the worksheet, the range where the dates are entered. To produce a valid range the start date must be before the end date. Just in case there's any issues with making sure that the dates entered are appropriate we can use formulas or Conditional Formatting to warn against making an incorrect entry.
As the dates change the ranges for the chart are recalculated and the chart updates. The start date has been changed from May 2015 to Jun 2015 and the new plot for the period Jun to Sep 2015 is illustrated below. A new end date for the chart can be specified in exactly the same way.
 |
| Data period plotted from Jun to Sep 2015 |
Creating the worksheet and chart
You might want to download the Excel file and work through the process. There's two worksheets in the workbook, Sheet1, which is the completed exercise and unfinished, which is the basic worksheet and chart where you can try out your names and formulas.The first job is to create some names that we can use in our formulas as follows:
| Name | Reference | Purpose |
| Date1 | A5 | Starting point for the dates |
| Data1 | A6 | Starting point for the Tom range |
| Data2 | A7 | Starting point for the Dick range |
| Data3 | A8 | Starting point for the Harry range |
| StartDate | C2 | The starting date |
| EndDate | C3 | The ending date |
| StartMatch | A2 | The position of the Starting date in row 5 |
| EndMatch | A3 | The position of the Ending date in row 5 |
 |
| Names to be set up |
You don't have to use Names for all the references but it's much easier to put your formulas together if you do. To create the names, click the relevant cell and then either type the name into Excel's Name Box (extreme left hand side of the formula bar) and press Enter or click the cell and click the Define Name control in the Defined Names group on the Formulas tab. Type the Name in and click OK.
 |
| Data Validation to link a cell to a range of values |
If you wanted to create some input validation for your worksheet and make sure that the end date selected from your list was actually after the start date then you could enter the following formula in a convenient cell:
=IF(C2>=C3,"Start is after End","")
This would not stop the entry being made but you would at least be aware that it was not correct.
Entering the worksheet formulas
There are two sets of formulas to be entered, the first ones are entered into cells and are used to spot the specified starting and ending dates in row 5 so that the chart data ranges can be calculated:
| Name | Reference | Formula |
| StartMatch | A2 | =MATCH(StartDate,5:5,0) |
| EndMatch | A3 | =MATCH(EndDate,5:5,0) |
Excel's MATCH function with Match Type 0 (Exact Match) is used to return a number indicating the position along row 5 where the dates corresponding to the specified start and end dates are located.
Entering the named formulas
The second set of formulas will calculate the ranges on the worksheet which are to be plotted on the chart based on the starting and ending dates. These formulas are not entered into worksheet cells, instead they are entered into Names. An unusual place to enter formulas. Click any cell on the worksheet and then click Define Name on the Formulas tab. Type the name into the Name box and then copy and paste the formula into the Refers to box. Click OK. |
| Defining a Name as a formula |
| Name | Formula |
| rngDate | =OFFSET(Date1,,StartMatch-1,,(EndMatch-StartMatch)+1) |
| rngTom | =OFFSET(Data1,,StartMatch-1,,(EndMatch-StartMatch)+1) |
| rngDick | =OFFSET(Data2,,StartMatch-1,,(EndMatch-StartMatch)+1) |
| rngHarry | =OFFSET(Data3,,StartMatch-1,,(EndMatch-StartMatch)+1) |
OFFSET is Excel's general pointer function and is used to return a variable range of cells. The narrative of the first formula is as follows: "Starting from Date1, stay on the same row but go over to the first date match cell and point to a range of cells whose width is the product of the EndMatch and StartMatch values". The positive and negative adjustments are necessary to precisely pick out the ranges.
 |
| Click the Names Manager control to review the Names set up in the worksheet |
Plotting the chart
The final job is to get the chart to use the calculated ranges rngDate, rngTom, rngDick and rngHarry, which correspond to the cells in rows 5 to 8 for the period bounded by the start and end dates. You used to be able to go straight into Excel's chart SERIES formula and edit the ranges directly. This never seems to work for me nowadays.
 |
| Editing the Chart data ranges |
You have to get the the Name into the SERIES formula for the chart.
You must always use the sheet name to qualify the range name and use the Select Data control on Chart Tools, Design tab to edit the formula rather than try to edit the SERIES formula in the formula bar. Click the first range and then click the Edit button.
You must always use the sheet name to qualify the range name and use the Select Data control on Chart Tools, Design tab to edit the formula rather than try to edit the SERIES formula in the formula bar. Click the first range and then click the Edit button.
When you enter the reference to the name, use the sheet name, like "Sheet1" and Excel takes care of the rest. The original SERIES formula looked like this:
=SERIES(Sheet1!$A$6,Sheet1!$F$5:$J$5,Sheet1!$F$6:$J$6,1)
And when you have finished editing it will be transformed to this:
=SERIES(Sheet1!$A$6,Sheet1!$F$5:$J$5,Sheet1!$F$6:$J$6,1)
And when you have finished editing it will be transformed to this:
=SERIES(Sheet1!$A$6,'date-period-chart.xlsx'!rngDate,'date-period-chart.xlsx'!rngTom,1)
That's got the names into the formula for the chart and now they will change dynamically as the worksheet dates change. Job done.
No comments:
Post a Comment