PowerPivot is a new free add-in for Excel 2010. It’s probably the most expected feature in the new version of Excel because it turns it into a powerful Business Intelligence tool. These are some of the major benefits of PowerPivot:
- While Excel grid goes until row 1,048,576, has we’ve seen on the Excel 2010 Grid post, with PowerPivot you can handle millions of rows of data. You can sort, filter, scroll and pivot that amount of data using PowerPivot.
- You can create pivot tables with data from multiple tables.
- Import data from different sources like Access, RSS, SQL Server and show the data into a single pivot table.
- DAX — Data Analysis Expressions is the new formula language in PowerPivot. DAX has 117 functions for two types of calculations: 81 normal Excel functions and 54 new functions mainly for data analysis.
- Excel workbooks with PowerPivot are smaller than the ones that use traditional pivot tables.
There are also some negative aspects on using PowerPivot on your workbook:
- You can’t Group on pivot tables.
- You can’t use VBA on PowerPivot as you could with pivot tables.
- PowerPivot can’t be used with other versions of Excel, only the 2010 version.
