PowerPivot vs Pivot Table
PowerPivot vs Pivot Table
PowerPivot is a new tool available in Excel 2010 and Excel 2013. International experts believe this is a quantum leap in Excel and people like Mr Excel’s Bill Jelen has stated that it is the best thing to happen to Excel in years.
On the surface it looks like a traditional Pivot Table with some new screens. However it offers several advantages which include:
- Easier linking of source data
- Creating relationships between these tables
- Improved formula to provide meaningful reports
- Can handle more data (100 million rows is possible!)
As per Microsoft the goal of PowerPivot and the DAX calculations in particular is:
“The ultimate goal of PowerPivot for Excel is to make data analysis really easy. Unlike products that are designed for IT professionals, (for example: Microsoft SQL Server Analysis Services,) PowerPivot for Excel is intended to be used by the same folks who build Excel PivotTables today. The idea is that Excel users should be able to leverage the training and experience they already have and should not be required to learn complex multi-dimensional concepts nor specialized multi-dimensional languages.
With this in mind, DAX was implemented within PowerPivot to achieve the following:
- Ease-Of-Use- DAX uses standard Excel formula syntax (and some of Excel’s functions)
- Relational Data- Simple relational data model based on tables, columns, and relationships
- PivotTables- Analysis performed via PivotTables based on the database (multiple tables)
If you are a regular user of Pivot Tables then the comparison below between the two will help you understand the differences.
Traditional Pivot Tables
- Single data source typically in an Excel worksheet
- Work within the data source to join tables i.e. VLOOKUPS, SUMIFS etc.
- More forgiving to errors
- Dates are handled within the pivot table
- Can use the functions available within Excel only
- Multiple sources of data (text, excel, database, DataStream) which can be refreshed directly
- Relationships can be formed without VLOOKUP or making changes to the underlying data sources
- Less forgiving. The process stops if it is not happy
- Special treatment of dates
- Similar functions to Excel and the Excel syntax but lots of new functions
The key for people who are familiar with using Excel and Pivot Tables is knowing how it differs and how you use the powerful new features and compensate for the differences.
The majority of the constraints that existed in Excel (and you probably have given up trying to find a clever solution and do it manually) have been sorted.
You need to reassess what you have always wanted to achieve with your Excel analysis work. You can now do it with ease.