Error Message "Data source reference is not valid"

 2011-09-21    PivotTables    0    186

You might have encountered this error message while trying to create a PivotTable, and scratched your head trying to figure out what is wrong with the source data you have selected.
Especially if you have had no trouble creating a PivotTable before using the same or a similar data source range.

Well, despite what the error message suggests, it is probably not your data source that is invalid, it is most likely the filename of your workbook that causes this problem.

Usually this problem occurs when you have opened a workbook from e.g. a location on your company Intranet. You will notice that the open workbook name is something like MyWorkbookName[1].xlsx, and it is the square bracket characters in the file name that causes the error.

Why? Probably because Excel uses square bracket characters to separate the workbook name from a sheet name when storing the reference to a data source, e.g. like this:
When a workbook already contains square bracket characters in the filename this probably messes things up for Excel and triggers the error message.

The solution to the problem is to save your workbook (or rename the workbook file) using another filename that does not contain the square bracket characters.
Open the renamed file and you should now be able to create your PivotTable without any problems.

If you were to try to save a workbook from within Excel using a filename with square brackets in the filename, you will be notified that you are trying to use an invalid filename.