I am trying to create a pivot from a range but keep getting the error Data source reference is not valid

I have Googled this and tried all of the following.

  • Select from Table instead from range.
  • Open the Excel spreadsheet click on the view tab and select new window. Save the spreadsheet with a different name for the new window.
  • Click on HOME> PREPARE> INSPECT DOCUMENT> Removed all unneccesary references and xml forms.

I have labels on all the columns and all the cells are populated.

I am using Office 2010 on Win7 and the file is in .xlsx format


I found the reason the pivot tables didn't work was because of the file name. The reports are generated by a report automation system and the name contains square brackets [] that Excel doesn't like.

I removed the square brackets totally by chance and found the pivots are working.

I also tried to save the file in the Save As option with square brackets but like I said, Excel moans about it and doesn't allow you.


I had this same error but didn't have brackets [] in my file name. I found that the Excel file from my web-based reporting tool was not actually in XLSX format. When I did a "Save As" in Excel the file type was "Web page". I changed that to "Excel workbook (*.xlsx)" and saved with the same name and the Pivot Table worked.


I had a similar issue. No brackets in the file name though. It did however contain a comma "," in the file name and since it was sync'd with OneDrive the ccomma was converted to an "^J" so that could have been the issue too.
I coppied the data to a blank spreadsheet and the pivot table worked fine. So it told me that there must be an issue with the file name too. I changed the file name to remove the comma and boom, it worked fine.


Issue was the file name with a comma that was converted to ^J when I synced with OneDrive.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.