|| OFFSET() function | Graph tips | Grouping data for charts | Index() ||
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.
<< [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. >>
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
file. It has been rewritten for Excel 2010.
|file:xlgraphoffset||© meadinkent.co.uk 2015||Page last updated Feb14||CMIDX S7 P6 Y|