PowerPivot - an application for expanding the capabilities of Pivot Tables

Starting with version Excel 2013, this add-on is built-in and does not require installation!


Those who often use Pivot Tables at work have already been able to appreciate how powerful a tool they are. However, pivot tables unfortunately cannot report from different data sources, be it databases or files. Also, when we operate with large volumes of data, our ordinary computers begin not to cope with the processing of this data, and begin to "slow down" or freeze at all. This applies to tables that have more than 300,000 rows. So, to solve all these problems, Microsoft Corporation has developed a free add-on to Excel that extends the capabilities of regular PivotTables.

So, we present to you an add-on for MS Excel that will allow you to create supertables - PowerPivot.

What is PowerPivot?

PowerPivot is an additional add-on for Excel that extends the functionality of PivotTables (PivotTables) . Used only for Office 2010. In the new version of Office 2013 PowerPivot is already an integral element of Excel.

How to set PowerPivot?

Before installing PowerPivot, you need (for Windows XP):

After installing PowerPivot in Excel an additional tab appears:

Clicking PowerPivot Window will open a separate window where you can upload data from various sources.

Using PowerPivot

After loading data from various Excel files into the PowerPivot Window tab, you will be able to work with them as you would on a regular Excel sheet, that is, add columns, make calculations using formulas. Also, there is an opportunity, unique for Excel, to make connections between loaded tables as in Access. Using relational tables, we can do Summary table , which will take data from different tables, without their previous manual combination, without repeated use VLOOKUP (VLOOKUP) , simply and quickly.

By clicking on the image of the Pivot Table , we get the opportunity to start shaping it according to our needs. Thus, we will have a table layout like this with all the data available that we have previously prepared and loaded:

Also, as we can see, new additional fields called Visual Filters appear in the Pivot Table (Slicers) .

Another important feature of extended PivotTables is accelerated processing of large tables (filtering, sorting, etc.) that occupy more than 1 million rows, which is almost impossible in standard Excel.

Related Articles: