Dynamic Charting By Dates

This article describes Dynamic Charts, and shows how to create a dynamic chart that allows the user to easily change the dates included in a chart.

Dynamic Charts

Excel charts are dynamic by nature: when a chart's source data changes, the plotted points move accordingly. But Dynamic Charts refer to charts whose data ranges change automatically according to changing parameters. The data range may stretch or shrink, move within the worksheet, or even skip different numbers of cells between points. The mechanics behind dynamic charts are Excel's named ranges.

This article will examine three levels of dynamic charting, covering a range of difficulty and functionality:

• A simple dynamic chart based on starting and ending dates entered into the worksheet.

• A dynamic chart that uses sliders to allow selection of starting and ending dates.

• A chart that uses sliders to define endpoints and option buttons to set the spacing of data points.
   

A Simple Dynamic Chart

We'll begin our exploration of dynamic charts with a simple chart, which changes its data range based on dates entered by the user. Part of the data for this chart is shown below (the worksheet is named "Data"):

Define the Named Ranges
Cells A1 and A2 contain the starting and ending dates for the chart. Cell A1 is named "StartDate" and cell A2 is named "EndDate" (see sidebar Naming a Range, above). The range containing all of the dates in the data range, in this case A5:A63, is named "AllDates".

Now it's time to make use of the user-entered dates. Press Ctrl-F3 to open the Define Names dialog. Type the name "ChartDates" in the name box, and in the Refers To box, enter the following formula:

=OFFSET(AllDates,MATCH(StartDate,AllDates,1)-1,0,MATCH(EndDate,AllDates,1)-MATCH(StartDate,AllDates,1)+1,1)

The OFFSET function defines a new range using the following syntax:

=OFFSET([Starting Range],[Row Offset],[Column Offset],[Rows],[Columns])

MATCH(StartDate,AllDates,1) tells us the position of the starting date within the range of all the dates, and MATCH(EndDate,AllDates,1) tells us the position of the ending date in that range. Therefore, ChartDates is the subset of AllDates that reaches from the starting to the ending date. ChartDates will serve as the chart's X values. Define the following named ranges for the Y values of the chart:

Construct the Chart
Select a blank cell, and start the Chart Wizard. In step 1, choose a line chart. In step 2, select on the Series tab. Click the Add button, click in the Name box, and select cell B4, which contains the label "Firm A". Click in the Values box, and type "=Data!ChartFirmA" (the worksheet name is "Data"). Finally, click in the Category (X) Axis Labels box, and type "=Data!ChartDates".

Click the Add button again, click in the Name box, and select cell C4 ("Firm B"). Click in the Values box, and type "=Data!ChartFirmB". The Category (X) Axis Labels box retains the link to "ChartDates". Excel has changed the prefix from the worksheet name to the workbook name, but don't worry about that.

Repeat this sequence to add the third and fourth series to the chart. Click on Next and Finish until your chart is finished, adding any options along the way, and formatting as desired. The result is a time scale chart that changes its scale as the input dates are changed.

Add Sliders to Enhance Range Selection

Selecting the charting range can be much easier if you add a few worksheet controls to the example above. Start with the workbook you've made above, and add two slider controls (scrollbars) from the Forms menu. Click on Toolbars on the Tools menu, and select Forms, if the toolbar isn't visible. Click the Scrollbar button on the Forms menu, then trace a rectangle where you want the scrollbar to go. Make a second scrollbar by repeating this process, by holding Ctrl while you drag the first scroll bar, or by copying and pasting the first scrollbar.

Right click on a control to format it. I've set the Cell link of these two sliders to B1 and B2. I've set the other parameters the same for both: Minimum = 1, Maximum = 59 (for all the days in January and February 2003), Incremental Change = 1 (for clicking the arrow buttons at the end), and Page Change = 10 (for clicking the rectangle between the arrow and the current position of the slider itself).

I've put this formula into cell A1:

=INDEX(AllDates,MIN(B1:B2))

and this formula into cell A2:

=INDEX(AllDates,MAX(B1:B2))

Using MIN and MAX assure that A1 will always have the earlier date and A2 the later date. All the rest of the defined names, and the chart itself, are unchanged from the earlier example. The top part of the worksheet looks like this:

Add Option Boxes to Control the Spacing of Data

Use the previous workbook; the sliders still select the endpoints of the range to chart, but now we will use option buttons that let the user to select whether to chart data daily, weekly, monthly, etc. Add six option buttons from the Forms menu. Click on Toolbars on the Tools menu, and select Forms, if the toolbar isn't visible. Click the Option Button button on the Forms menu, then click the mouse, and Excel draws a button. Make additional option buttons by repeating this process, by holding Ctrl while you drag the first scroll bar, or by copying and pasting the first scrollbar.

Right click on a control to format it. Change the caption of the first option button to "Daily", the second one to "Weekly", the third to "Monthly", and the fourth through sixth to "Quarterly", "Semiannually", and "Annually". It is important to label the buttons in order, or the cell link will not refer to them in order. Set the Cell link of these buttons to cell G1. All option buttons on a worksheet use the same cell link, since only one can be selected at a time, and the link simply gives the index of the selected button. (You need only set the cell link of one button.)

Move the data table down a couple rows to make room for my option buttons. You can arrange the buttons nicely in the sheet, fill the cells behind them white to hide the gridlines, and format a border around these cells. The arrangement of controls and the top of the data range is shown below:

As before, cell A1 is named "StartDate" (see sidebar Naming a Range, above), and contains the formula:

=INDEX(AllDates,MIN(B1:B2))

Cell A1 is named "EndDate", and contains the formula:

=INDEX(AllDates,MAX(B1:B2))

Skip column F to avoid confusing the raw data with the computed charting data, then set up a parallel charting data range in columns G through K. Cell G1 has the cell link to the option buttons added above (in this case 2, since the second button, "Weekly", is highlighted). G2:L4 contains the counters needed to change the spacing of data:

• When button 1 ("Daily") is highlighted, G2:G4 is the relevant array, identified by the named ranges "DayIncr", "MonthIncr", and "YearIncr" defined below. The range contains {1,0,0}, which increments each date 1 day, 0 months, and 0 years from the prior date.

• When button 2 ("Weekly") is highlighted, H2:H4 is the relevant array; it contains {7,0,0}, which increments each date 7 days, 0 months, and 0 years from the prior date.

• When button 3 ("Monthly") is highlighted, I2:I4 is the relevant array; it contains {0,1,0}, which increments each date 0 days, 1 month, and 0 years from the prior date.

• et cetera

Name a few ranges in the worksheet before populating the charting range. The range containing all of the dates in the data range, in this case A7:A65, is named "AllDates". The range containing all the original data (A7:E65) is named "AllData". Define the following named ranges for the date incrementing algorithm:

With the headers from B6:E6 duplicated in H6:K6, enter this formula in cell G7 (the first date in the parallel range):

=StartDate

Select cell G8, then press Ctrl-F3 to open the Define Names dialog. Enter the name "NewDate" in the Name box, and enter this formula in the Refers To box:

=DATE(YEAR(Data!$G7)+YearIncr,MONTH(Data!$G7)+MonthIncr,DAY(Data!$G7)+DayIncr)

This is the "magic formula" that determines the new date from the previous date, based on which option button is pressed. Enter this formula into cell G8, and drag it down to fill G8:G65 (parallel to the original dates):

=IF(NewDate>EndDate,NA(),NewDate)

This fills in the next date, until the end date has been reached; from that point downward, the cells are filled with #N/A errors. Note that the dates increment by 7 days (one week) with the "Weekly" option button selected. Click on the other buttons to watch the dates change.

Now select cell H7, and enter this lookup formula:

=IF($G7>EndDate,NA(),VLOOKUP($G7,AllData,COLUMN()+1-COLUMN($G7)))

Drag this to the right to fill H7:K7, then drag it down to fill H7:H65. Where column G has a valid date, this formula returns the corresponding value; where column G contains #N/A, so do the cells with this formula.

Finally, define the following named ranges for the dates and the Y values of the chart (the Refers To formulas for "ChartFirmA" through "ChartFirmD" are unchanged from the prior workbook):

The top part of this parallel charting data range looks like this:

If you started with the previous workbook and made the changes as described, the original chart works fine, but now it shows data on a periodic basis (i.e., weekly). If the chart was broken, you can rebuild it as described in the first section above.

Click here to download a zip file that contains three workbooks, one for each of the examples in this article.

More Dynamic Charts

Here are a few of the many web links that deal with dynamic charting. This is not intended to be a complete list, nor are the entries in any particular order.

John Walkenbach - The Spreadsheet Page
http://j-walk.com/ss/excel/usertips/tip053.htm
Dynamic Chart - Demonstrates how to make a chart's data series display data based on the active cell position.

Stephen Bullen's Excel Page
http://www.oaltd.co.uk/DLCount/DLCount.asp?file=FunChrt1.zip
This file demonstrates how to set up a chart so that it automatically includes new data as you type it in.

Tushar Mehta
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
Dynamic Chart Tutorial

Bernard V. Liengme
http://www.stfx.ca/people/bliengme/exceltips.htm#Dynamic chart
Dynamic chart: add new data and the chart expands.

Jon Peltier
http://PeltierTech.com/Excel/Charts/Dynamics.html
A few examples of dynamic charts, charts that use worksheet controls, and a longer list of links, compiled by the author of this article.