Simon Hurst concludes his three-part content series looking at Range Names by delving deeper into the advanced features.
The recent ExcelZone guide to using the advanced features of Excel Range Names mentioned the use of a Range Name that doesn't refer to a cell or group of cells, but instead refers directly to a formula.
One example of this technique is to allow functions to be used to control the source of Excel objects such as charts, linked pictures and Sparklines. Here, we will look at how you could use this in practice, in this case to allow you to interactively control the number of cells in the data source of a Sparkline.
Sparklines were added to Excel in the 2010 version. They allow a simple trend line chart to be quickly added to one or more individual cells:
Source: Simon Hurst
In this example, we have used the Insert ribbon tab, Sparklines group, Line command to add Sparklines showing the trend of sales over the previous 12 months. We can select any one of our Sparkline cells and use Sparkline Tools, Design Ribbon tab, Sparkline group, Edit Data dropdown, Edit Single Sparkline's Data to see the current range that that Sparkline is based on:
Source: Simon Hurst
We are going to enter a value between one and 12 in a cell and use that to control the number of cells our data range includes. We can do this using the OFFSET() function which allows us to specify a starting cell and then define the top-left corner of a range and the number of rows and columns in the range. (Note that OFFSET() does have some drawbacks: it is a volatile function which means Excel always has to recalculate it when anything in the workbook is changed and it also 'breaks' the dependency chain, making it difficult to trace a calculation back to its source cell or cells.)
We could recreate the range B7:M7 using OFFSET() as follows:
=OFFSET(Sheet2!$B7,0,0,1,Sheet2!$O$1)
We have entered five arguments:
- Sheet2!$B7. This sets our starting point to column B (fixed) and this row which is row 7. We have removed the dollar sign from in front of the row reference so we can copy the formula down to the other rows in our table.
- 0. Don't move any rows from row 7
- 0. Don't move any columns from column B, so our top-left corner is cell $B7
- 1. Make our range 1 row deep
- $0$1. Make our range so many columns wide. This is a fixed reference to a value held in cell O1. If the value 12 is entered into cell O1 then our range will start at $B7 and the bottom right corner will be M7 (12 columns to the right of column B)
In fact, we are not going to enter the OFFSET() formula into any cell. Instead, we are going to use Formulas Ribbon tab, Range Name group, Defined Names group, Define Names to allocate our formula directly to a Range Name. It is important that we have cell N7 selected when we set up the Range Name, otherwise our relative reference to row 7 will change when we apply the name to our Sparkline:
Source: Simon Hurst
We can now return to the Edit Sparkline dialog for cell N7 and set the data source to our Range Name. We have used a Range Name because the Edit Sparkline Data dialog will not accept a range that includes a function directly:
Source: Simon Hurst
We can then copy the Sparkline in N7 down to our other rows. If we enter a different value in cell O1, our Sparklines should just show the trend for that number of months, from the first month:
Source: Simon Hurst
If we wanted to show the most recent 4 months, we could change the Range Name formula from N7 as follows:
=OFFSET(Sheet2!$M7,0,0,1,-Sheet2!$O$1)
Here, we have set the starting cell to the right-hand end: M7 and then used minus to move to the left, rather than the right, of our starting cell. Here is our revised range combined with the use of a Spin Control to make the process even more interactive:
Source: Simon Hurst
Follow Hurst's step-by-step range name content series here