Power Pivot is a new tool of Excel 2013 that allows you to perform calculations on an unlimited number of rows. The calculation can be done with calculated columns, like in Excel, or with measures.
It all starts with SQL Server
Before to explain how to use Power Pivot, it is important to know that this tool has been developed by the engineers of Sql Server.
SQL Server is a relational database management system developed by Microsoft. It’s interface is SQL Server Management Studio (SSMS). When you use SSMS for the first time and when you are familiar with worksheet, you will be surprised to not visualize your data like you can do with Excel.
In fact, your data are stored, somewhere, but to visualize your data, you have to create a sql query.
So, to visualize the data like in Excel, the Microsoft’s engineers have developed a tool call SQL Server Analysis Services (SSAS)
When you link SSAS to a database, you can see easily the contain of each table like in a spreadsheet. But the data are in View only mode.
SSAS = Power Pivot
At this step, Microsoft has created a tool to connect to database without limitation of rows. That’s the perfect tool to avoid the limitation of Excel.
So, in 2011, Microsoft has integrated SSAS to Office. And because the only way to return the results is in an agregator, and the only agregator of Excel is Pivot Table, SSAS becomes Power Pivot in Office (logic).
Installation of Power Pivot
In the version of Excel 2010, Power Pivot was an add-in but with Excel 2013, Power Pivot was integrated in the native Pro Plus version.
Update: Since May the 22nd 2018, Power Pivot is available for all the versions of Excel (Family, Student, Pro, …) and for Excel 2013 or Excel 2016.
But by default, Power Pivot is not activated. To activate Power Pivot, you must go to
- Files > Options > Add-ins
- Select COM Add-ins
- Press GO
- Finally, select the option Power Pivot
Now, you can see the tab Power Pivot in your ribbon