Excel – How to rectify Excel error “We couldn’t get the data from table in the workbook…”

microsoft excelmicrosoft-excel-2016office365pivot table

I am dealing with the following problem using MS Excel 2016:

  • The Excel file contains a huge data table and several pivot tables in separate sheets.
  • The current Excel file was saved under a new file name from an earlier file version.
  • Now, when trying to 'refresh all' pivot table data in Pivot Table tools Analyze ribbon, the following error message appears, referring to the earlier file's filename:

We couldn't get the data from 'Table1' in the workbook 'Oldfile.xlsx' Open this workbook in Excel and try again.

The strange thing is:

  1. The data table in my case is in the same file as the pivot table, there is no external connection.
  2. When I look for any connection to the earlier (old) file in my new Excel file, no connections are found.

Best Answer

Additional detail to @meggie's response above. I suspect this has to do with corrupt Data Model connections.

  1. Pay attention to the error message – it will list the name of the broken connection in quotes
  2. Data -> Queries and Connections to open the sidebar
  3. Click on Connections tab
  4. Hover over each item and look for one that matches the name in the error message. Delete.

Connections Tab full of connections, one of which was broken

Related Question