Creating a chart which grows with your data

- Dynamic ranges in Excel 2010

Line charts are a useful format for displaying values over a period of time. Unfortunately, if there is one data series which has fewer categories than another and contains values of zero, a line plotted by Excel will correspondingly fall to zero on the Y axis. This gives a misleading impression.

The example (right) shows a comparison of expenditure last year and this year and the current year data series has fewer values (i.e. April to July only). If the future monthly values (i.e. F4:G4) in the table are left blank your graph will be unaffected. However if all of the table cells use calculations to retrieve values from a list of data, the table will contain zero values for the last two months.

It is possible to instruct an Excel chart to automatically ignore the unwanted latter part of the series (i.e. August and September). The OFFSET function can be applied to resize the range of the graph source data to include an appropriate series of values.

• Create your data table (worksheet name 'Main') and graph and save the spreadsheet. If you edit the chart and select 'This Years' chart line you should see the following formula in the formula bar. =SERIES(Main!$A$4,Main!$B$3:$G$3,Main!$B$4:$G$4,1). This is not a regular function but it contains the properties of a chart data set and can be directly edited: =SERIES(data_name, legend_titles, values, line_sequence). The final line_sequence argument refers to the order in which the data sets are shown in the legend - e.g. This Year = 1, Last Year = 2.
• Select any worksheet cell, then insert a named range based upon a formula ([Formulas] Defined names | Name Manager | New). Type a name (e.g. 'ExpThis') for your range in the Define Name dialog box, then add the following formula into the 'Refers to:' field. =OFFSET(Main!$A$4, 0, 1, 1, Main!$E$1)

• The OFFSET function in this example defines a range anchored on cell A4, starting 0 rows down and 1 column to the right, with a height of 1 row and a width of E1 columns (i.e. B:E = 4 columns). Cell E1 contains the current year month number and is input manually. If the actual expenditure values will never be zero, the month number could be produced by the formula =COUNTIF(B4:G4,">0")

<< [v1] The 'This Yr' line falls to zero for the months of Aug and Sep. [v2] An improved chart where the line ends in Jul and the zero values are avoided. >>

• Now select the chart and the appropriate data series. The SERIES() definition will appear in the Formula bar at the top of the screen. The penultimate argument represents the worksheet range of the current cells (e.g. Main!B4:G4). Edit this argument and replace it with the defined Name. =SERIES(Main!$A$4, GraphMain.xlsm!MonthNames, GraphMain.xlsm!ExpThis, 1) You must prefix the Name with the spreadsheet filename. Note that filenames containing spaces must be enclosed in apostrophes (i.e. 'Monthly Graph.xls'!ExpThis)
• Alternatively, instead of editing the SERIES() formulas, the defined names can be typed into the Source Data dialog for the chart ([Chart Tools : Design] Data | Select Data ...)
• The current year line on the chart will now grow automatically each month and avoid any of the zero values.

This methodology can also be applied so that the whole graph (including X axis categories) grows automatically to include a dynamically sized table. As more columns of data are added to the table, the graph will extend to include them. This can be done without the need for a cell containing a month value. In the OFFSET function which is used to define the Name, instead of using 'Main!$E$1', the function 'COUNTA($B$3:$M$3)' would count and capture all of the X axis labels (see 'MonthNames' in the picture above). A separately defined Name would be required for each of the components on the Chart - i.e. a name for the X data labels, another name for the current year row values and another name for the old year row values.

The same outcome can also be acheived using Index() instead of Offset().

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.