Excel data source reference is not valid

microsoft-excel-2010pivot table

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

Best Answer

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.

Related Question