Home	Excel	Access
| OFFSET() function | Graph tips | Grouping data for charts |

Creating a chart which grows with your data

OR Avoiding zero values on line charts

Line graphs or charts are an appropriate format for showing 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 looks misleading on a chart.

In the example (right) which shows a comparison of expenditure last year and this year, the current year data series has fewer values (i.e. April to July only). If the remaining month values in the table are left blank your graph will be unaffected. However if all of the cells use lookup formulae to read 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. The OFFSET function can be applied to resize the range of the graph for a particular series of values.

• Create your data table and graph and save the spreadsheet. If you edit the chart and select 'This Years' graph 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)
• Select any worksheet cell, then insert a named range based upon a formula (Insert | Name | Define). Type a name (e.g. 'ExpendThis') 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)

Would you like to learn more? Excel Charts by John Walkenbach

• The OFFSET function in this example defines a range anchored on cell A4, starting 0 rows and 1 column to the right, with a height of 1 row and a width of E1 columns (i.e.B4:E4 = 4 columns). Cell E1 contains the current year month number and is manually edited.

• 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, Main!$B$3:$G$3, MonthlyGraph.xls!ExpendThis, 1) You must prefix the Name with the spreadsheet filename *.
• Alternatively, instead of editing the SERIES() formulae, the defined names can be typed into the Source Data dialog for the chart (Chart | Source 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 separate 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. Using COUNT() or COUNTA() functions will not avoid the zero values which was the purpose of the previous example.

* Note that filenames containing spaces must be enclosed in apostrophes (i.e. 'Monthly Graph.xls'!ExpendThis)

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file