You can, however, work around this issue pretty easily, depending on the problem you’re dealing with. Here, we’ve included the list of reasons that could cause this issue with viable solutions you could apply to fix this issue.
What Causes the “Data Source Reference Is Not Valid” Error?
Fix: Data Source Reference is Not Valid Error
There is a range of solutions you could try on your own to work around this issue in MS Excel. The solutions will vary depending on the nature of the problem you’ve encountered. Hence, we recommend you go through the list of possible issues behind the data source reference error in Excel.
Invalid characters in the file nameFile opened from a browserThe referred range does not existIncorrect named range
Rename File
Square brackets act as invalid characters in the Excel file names. If you have square brackets included in your Excel file name (e.g., SampleTitle[1]), you will come across this issue. In most cases, changing the Excel file name solves this issue. You will have to save your file with a different name, then store it anywhere on your device. If you suspect this is your issue, follow these steps to rename your Excel file:
Save Files From Browser
Did you open your file directly from a browser? When you do not save the file to your device, Excel cannot use the referred cells as a source to create a pivot table. Additionally, this temporary directory is named with the squared brackets. To work around this issue, you will have to save the file instead of simply opening it online. Here are the steps you could refer to save your Excel file to your device:
Check References
You may have entered an incorrect cell reference while filling in the details before creating a pivot table. The references you include in the message box must be the existing cell locations in the Excel grid. You can rectify this error by entering the correct cell references in the required field. Follow these steps to correctly enter cell references while entering the source into your pivot table:
Change Referred Named Range
You may have made a typing error while entering the named range as your reference to the pivot table. When you incorrectly enter your named range, Excel cannot locate the data to create the table and hence will alert you with the following “Data source reference is not valid” error. Similar to changing the cell references, you can check if you have correctly spelled the referred name range. To check the name of your named range, follow these steps: