locked
Create a PowerPivot table based on an existing pivot table RRS feed

  • Question

  • I inherited a huge Excel file that has multiple pivot tables. We're at the point now where Excel crashes when trying to update the pivot tables because we have too much data. How can I make it so I can import all the data into PowerPivot and keep/update all the existing tables?

    Also, the pivot tables have complex formulas in them that were made in the Calculated Field section under the PivotTable analyze tab. Not sure if that makes a difference or not.

    Thanks.

    Friday, June 3, 2016 12:57 PM

Answers

  • The only way to go is to import the data into Power Pivot in a new Excel file, and create new pivot tables. It's not possible to change the data source of a pivot table from a range in Excel to the data model. When working with Power Pivot, calculated fields are replaced by DAX measures (also called calculated fields in Excel 2013), which are much more powerful than the standard calculated fields. But you will have to rewrite the calculations, it's not an automatic replacement.
    • Proposed as answer by Charlie Liao Monday, June 6, 2016 6:56 AM
    • Marked as answer by jdmarks3 Monday, June 6, 2016 12:03 PM
    Friday, June 3, 2016 1:42 PM
    Answerer

All replies

  • The only way to go is to import the data into Power Pivot in a new Excel file, and create new pivot tables. It's not possible to change the data source of a pivot table from a range in Excel to the data model. When working with Power Pivot, calculated fields are replaced by DAX measures (also called calculated fields in Excel 2013), which are much more powerful than the standard calculated fields. But you will have to rewrite the calculations, it's not an automatic replacement.
    • Proposed as answer by Charlie Liao Monday, June 6, 2016 6:56 AM
    • Marked as answer by jdmarks3 Monday, June 6, 2016 12:03 PM
    Friday, June 3, 2016 1:42 PM
    Answerer
  • Thanks for the quick reply!
    Friday, June 3, 2016 1:45 PM