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
3 Answers
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.
