Power Pivot Data Model adds database like features to Excel. Power Pivot allows you to handle millions of rows of data, from multiple data sources and lightening fast analysis. It also eliminates the need for using VLOOKUP function for decoding codes into descriptions.
Power Pivot does NOT provide Grouping, Calculated Columns and Calculated Fields features. These need to be managed using related tables and DAX formulas. This can be a limitation in some cases – especially when you are new to Power Pivot.
In some cases, you want best of both worlds. The database consolidation, relationships and performance of data model. But at the same time, you also want the features of Pivot Tables which you are used to for so many years.
The good news is that you can do it quite easily. Here is how (requires Excel 2010 & higher).
Create Pivot Table from Power Pivot Data Model
- Create the data model as required.
- Now go to the Excel workbook and choose Insert – Pivot Table.
- Please note that there is no data in the Excel sheet.
- Now, from the dialog, choose the checkbox Choose this workbook’s data model
- A regular pivot table is now created.
- All the expected features are now available.
- That’s It. Enjoy.
This is only for Excel 2016?
Even in 2010, if you add power pivot addin, this can happen. You must use Connections in this Workbook option.