MeadInKent
| OFFSET() function | Graph tips | Grouping data for charts | Index() |
excel 2010

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.

chart data

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.

Define dynamic names in Excel 2010
zero values on line charts
<< [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. >>

Offset used to limit values in current year

Edit the source data of a chart in Excel

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().

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file:xlgraphoffset © meadinkent.co.uk 2015 Page last updated Feb14CMIDX S7 P6 Y