Akib Bin Rashid
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.
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.
- 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.
- This time Excel will create a sparkline.
- Use the Fill Handle to AutoFill up to F9.
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.
- 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 will draw a sparkline.
- Use the Fill Handle to AutoFill up to F9.
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.
