Location Reference Is Not Valid – Excel Sparklines

Facing the error message that the location reference is not valid in Microsoft Excel when you want to add sparklines? The cause of this problem is, as indicated by the error message, one of the cell references used in the data ranges. In this article, I’ll show the error and the solutions.

What are sparklines?

Sparklines are a great way to graphically illustrate trends in data ranges. You can use them for different types of data. As long as the data is numeric, a sparkline can be used. The source can be values, currency, or even the results of a formula. Sparklines can be used both for vertical and horizontal data ranges.

Inserting a sparkline is easy. Simply select the Insert tab (1) in the ribbon menu, and then click the Line icon in the Sparklines section (2).

This will bring up the Create Sparklines dialog box.

Next, select the data range for the sparkline to be based on, and the destination cell where the sparkline should be shown. If you selected the data range before clicking the Insert sparkline icon, that cell range will already be populated in the Data Range field.

Solving the location reference is not valid error

There are two possible causes for the location reference error. The first is when the Data Range is not valid. This happens when a multidimensional cell selection is specified. Or simply said, when you have more than one row or more than one column selected as the source, and the destination range is a single cell.

As can be seen in the screenshot, the Data Range has cells B2 to M3 selected. This includes two rows of data, rather than one. When you click OK in this situation, Excel will show the location reference error.

Similarly, the Location Range in the Create Sparklines dialog must point to a cell range that matches the Data Range. If multiple cells are used when the Data Range is a single row or column, the location reference error will also show.

The solution is obvious, make sure the ranges match. In the simplest scenario, this means a single row or column is used as the Data Range, and the Location Range is a single cell.

That does not mean you cannot insert multiple sparklines at once. But if you select multiple rows in the Data Range, the cells in the Location Range need to have the same count. To illustrate:

In this example, three rows are selected as the Data Range, and the destination Location Range also has three cells specified. Clicking OK will insert the three sparklines without any error.

An even faster way is to drag the bottom-right corner (1) of the first sparkline to the cells below it (2).

After releasing the mouse button on the destination, Excel will automatically replicate the cell content to the cells below it. Effectively this will insert the sparklines in those cells for the cell ranges in that row.

All this also applies to columns of course.

I’d also like to point out that cell ranges can be easily overlooked when you are using merged cells. Merged cells, or cell ranges defined by a name can contain invalid cell ranges for use in sparklines. Again, a few examples.

First, an invalid data range resulting from merged cells.

So, please pay close attention to the actual cell reference in the Data Range field, even though your cell selection in the spreadsheet may appear valid.

For the location range, the same is true. You cannot use a combined cell as the destination for the sparklines. The example shows two horizontally merged cells that are used as the Location Range in the Create Sparklines dialog box. Clicking OK will again show the invalid reference error.

I hope this article gave you a better understanding of the limitations of the sparklines in Excel. Using the instructions above, you should be able to solve and prevent location reference errors in Excel.

More tips on Excel.