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
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
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
Step 3 – From the left panel, click on save as option
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.
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
Step 3 – Click on name manager that is in the defined names section
Step 4 – In the opened window click on new button and in the appeared window mention the range that you want to create
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
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
Step 3 – Click on name manager that is in the defined names section
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
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!!