Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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