The Excel Sparklines Location Reference Is Not Valid – 2 Solutions

This is the sample dataset.

Solution 1 – Set the Data Range to One Dimensional

Sparklines do not function when the data range is two-dimensional.

Steps:

• Go to the Insert tab >> Sparklines >> Select Line.

• In Create Sparklines ,select a two-dimensional Data Range, for example, C5:E6.
• Enter the Location Range.
• Click OK.

• The following message box will be displayed:

• Select a one-dimensional Data Range, for example, C5:E5.

Excel will create a sparkline.

• Drag down Fill Handle.

Read More: How to Use Sparklines in Excel

Solution 2 – Use a Single Cell Location Range

Sparklines do not function when the location range includes multiple cells.

Steps:

• Go to the Insert tab >> Sparklines >> Select Line.

• In Create Sparklines, select the Data Range.
• Select multiple cells in Location Range (F5:F6).
• Click OK.

The following message box will be displayed:

• Select the Location Range as a single cell. Here, F5.
• Click OK.

• Excel will display a sparkline.

• Drag down the Fill Handle to see the result in the rest of the cells.

Download Practice Workbook

Download the workbook and practice.

Related Articles

• How to Change Sparkline Color in Excel 
• Sparklines Are Not Showing in Excel
• How to Ungroup Sparklines in Excel 

<< Go Back to Excel Sparklines | Learn Excel