Business
istock_kukai

Use range names to make sparklines interactive

by
21st Oct 2016
Partner The Knowledge Base
Share this content

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
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
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:

  1. 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.
  2. 0. Don't move any rows from row 7
  3. 0. Don't move any columns from column B, so our top-left corner is cell $B7
  4. 1. Make our range 1 row deep
  5. $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
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
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
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
Source: Simon Hurst

Follow Hurst's step-by-step range name content series here

 

Tags:

Replies (3)

Please login or register to join the discussion.

avatar
By NealeB
29th Oct 2016 09:05

Hi Simon nice trick - the Edit Name image for SparkLineRange has $B1 rather than $B7 in the Refers To box.
Regards
Neale

Thanks (0)
Replying to NealeB:
Simon Hurst
By Simon Hurst
29th Oct 2016 16:18

Thanks Neale . Well spotted. I'll correct the screenshot.

Thanks (0)
avatar
By neilfleming
09th Jan 2018 20:58

Having trouble with this one. I have a perfectly functioning formula that returns a cell reference range like $T$5:$Z$5. I put it into a named range and it still works.

Put it as the data range for a SparkLine chart and I get "The Reference for the location or data range is not valid".

I have tried both an OFFSET approach and a CELL("Address"... approach, but get the same result.

Ideas?

...Neil

Thanks (0)