Dealing with Excel can sometimes leave us with a lot of different errors that we need to resolve. One of them is an invalid data source error, that can occur often.
In the example below, we will explain why this error can appear and how to resolve it.
Invalid Data Source Error
There are several instances where this error can appear. We will present them and ways to deal with them:
- Source file moved or deleted. This is the case that happens most often. For the example, let us presume that we have a table with NBA players and their statistics from several categories: points, rebounds, assists, and turnovers:
This file will be saved on the Desktop. We will create a Pivot table from this data but in a totally different Workbook, located elsewhere. When we go to Insert >> Tables >> Pivot table, we will choose this data as a table range and the whole location will be shown to us:
Now, we will choose Conference in Rows, and Points in Values, and get the following Pivot table:
The risk that we have now is that our original data, located on the Desktop, can be erased, or the file can be moved or deleted. If we do just that (delete the file) and try to refresh our Pivot table, we will get the error message:
Which means that we need to check our data source.
- Connection settings. In today’s day and age, there are often situations in which files are being shared online, or we are using an external data source. With this in mind, it can often happen that file connection can give us a hard time. We can inspect the connection by going to the Data tab >> Queries and Connections >> Connections:
- Refreshing the Data. Sometimes, an error can occur if we are using an external source in terms of data refreshment. If this happens, we can go to the Data tab, and choose Refresh All, to try to resolve the issue:
- Drivers issue. If we are using any database, and we need to connect to it, we need to have all the drivers installed and up to date. If the drivers are outdated or incompatible this can cause connectivity problems. We can often resolve this issue by going to the database provider’s site and downloading the latest drivers.
- File permissions. As said, in modern times, we can often have our data source located externally, on a network, or on a shared drive. We always need to have permission to access the file in question. If we do not, we can get an invalid data source error. We need to contact a system administrator or the owner of the file to get permission.