How To Fix Data Source References Is Not Valid In Microsoft Excel

A few users when trying to create a pivot table from the range of cells in excel, get the error saying data source references is not valid. This issue might be because the excel file might contain characters that are not acceptable by the pivot table such as brackets. Or there might be a scenario like the range itself is not present and defined or it is pointing to the incorrect range value else the file you are trying to access is not saved in the local drive. In this article, we will see the solution for the ‘data source references is not valid’ error and possible scenarios causing this issue. Let us start!

Method 1 – Remove The Brackets From The File Name

 

Step 1 – Check if there are any brackets in the filename. If so, then remove them. To remove the brackets follow the below steps

Step 2 – Close the excel and go to the location where it is been saved

Step 3 – Click on the file and then right-click on it. From the appeared list click on rename

 

Rename



 

Step 4 – Then remove the brackets from the name because the pivot table will not support the brackets. Now give a proper name to the file

 

Remove Brace

 

Step 5 – Once done, close the file, refresh it, and check if the error is solved.

 

Method 2 – Save The File On The Local Drive

If you are opening the file directly from the website or any email attachments then that might be the reason for data source references not valid issue. Instead of doing so, download it and save the file on your computer location then try to use it

Step 1 – Open the required excel file

Step 2 – At the top left corner, click on file tab

 



File

 

Step 3 – From the left panel, click on save as option

 

Save As

 

Step 4 – Now choose the location to save the excel in the local drive of your computer and click on save button

Step 5 – Then open the saved excel and check if the data source reference is not valid issue is solved.

 

Method 3 – Make Sure The Range Exists And It Is Defined

If there is no range defined and then you are trying to create a pivot table then there are chances of getting the error data source reference not valid issue as shown in the below image.

 

Not Valid

 

Follow the below steps to check and define the range

Step 1 – Open the necessary Microsoft Excel

Step 2 – In the top excel ribbon, click on formulas tab

 

Formulas

 

Step 3 – Click on name manager that is in the defined names section

 

Name Manager

 

Step 4 – In the opened window click on new button and in the appeared window mention the range that you want to create

 

New

 

Step 5 – Use the Refers to box to set the range of cells that should be used. You can set the range by typing the cell numbers or by selecting the cells

 

Range

 

Step 6 – Once done click on ok and close the window

Step 7 – Now create the pivot table and check if the issue is solved.

 

Method 4 – Make Sure The Reference For The Named Range Is Valid

 

Step 1 – Open the necessary Microsoft Excel

Step 2 – In the top excel ribbon, click on formulas tab

 

Formulas



 

Step 3 – Click on name manager that is in the defined names section

 

Name Manager

 

Step 4 – Check if the range is referring to the cells that is actually needed and being analyzed by the pivot table

Step 5 – If there are any inconsistencies in the range of cells then double click on the table name and change the cell/ range values in the refers to option and give the correct values

 

Double Click

 

Range

 

Step 6 –  Now check if the data source reference error is solved.

That’s it! I hope the above provided information is helpful and easy to follow. Let us know which method you applied to get the issue resolved. Thank you and happy reading!!