[Solved]: Excel Sparklines Location Reference Is Not Valid

Get FREE Advanced Excel Exercises with Solutions!

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. To represent data, sparklines come in handy in Excel. However, sometimes, we see that Excel sparklines location reference is not valid. This problem may arise due to many reasons. In this article, I will discuss some of the reasons and the solutions when Excel sparklines location reference is not valid.


Download Practice Workbook

Download this workbook and practice while going through the article.


2 Solutions for Sparklines Location Reference Is Not Valid in Excel

This is the dataset for today’s article. I have the list of some salespeople and their sales amounts in the months of January, February, and March. I will show these data using sparklines.

excel sparklines location reference is not valid


1. Set Data Range to One Dimensional

Sparklines do not function when the data range is two-dimensional. You have to set the data range along one dimension. That means the data range must be along one column or row. Let’s dig a bit deep for better clarification.

Steps:

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

  • Create Sparklines box will appear. Select a two-dimensional Data Range, for example, C5:E6. Select the Location Range. Now click OK.

excel sparklines location reference is not valid

  • You will see that a message box will appear to declare that the reference is not valid.

  • To get rid of this problem, select a one-dimensional Data Range, for example, C5:E5.

excel sparklines location reference is not valid

  • Use the Fill Handle to AutoFill up to F9.

excel sparklines location reference is not valid

Read More: How to Use Sparklines in Excel (Create and Customize)


2. Use Single Cell Location Range

Sparklines do not function when the location range consists of multiple cells. That means the location range must be a single cell. Let’s dig a bit deep for better clarification.

Steps:

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

  • Create Sparklines box will appear. Select the Data Range. Then select multiple cells as Location Range (F5:F6). Now click OK.

excel sparklines location reference is not valid

  • You will see that a message box will appear to declare that the reference is not valid.

  • To get rid of this problem, select the Location Range as a single cell. Here, I have selected F5. Click OK.

excel sparklines location reference is not valid

  • Excel will draw a sparkline.

  • Use the Fill Handle to AutoFill up to F9.

excel sparklines location reference is not valid

Read More: How to Change Sparkline Style in Excel (4 Suitable Examples)


Things to Remember

  • You can create column sparklines as well.

Conclusion

In this article, I have demonstrated 2 solutions when Excel sparklines location reference is not valid. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.


Related Articles

Akib Bin Rashid
Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo