Power Pivot is an excel addin which is used to perform data analysis & create data models. It was introduced by Microsoft in Excel 2010 as a downloadable addin, however in the latest version of excel it comes with the application itself.
Power Pivot was created to transform excel into a stand alone Business Intelligence platform. It utilizes a local instance of Microsoft’s powerful Analysis Service engine, enabling users to build advanced calculated measures, data models & work with Relational databases. The same engine is used by other popular products from Microsoft like SSAS & Power BI. But enough with these technical jargons, let's dive in and see what it can accomplish for a data analyst.
How to add Power Pivot Addin into Excel
For the users of Excel 2010 -
Follow the instructions given in the below link to download and then add Power Pivot to excel
For users of the later versions of Excel -
Go to File menu >> Options
Go to Addins and then select COM Add-ins from the MANAGE dropdown. Then press Go.
In the next prompt, select the check box for Power Pivot & press Ok.
This will automatically add a new tab named Power Pivot in your workbook like below
Now that you know how to add Power pivot to your excel, lets understand its benefits!
No limit on data import - While using normal excel, you can only import up to 10 million records, in Power Pivot there is no such limit. Technically you can import as many rows as you want, provided your system memory can handle that load. This comes in as a major advantage for any data analyst because, now you don't have to rely on SQL & big data platforms to analyze huge datasets.
Relationships & Model Building - While in basic Excel you could only work with 1 dataset at a time & had to rely on functions like Vloopup to get additional information from different tables. Power Pivots lets you create relationships among multiple tables and build complex data models which will enable you to perform advanced data analysis steps which were previously unknown to excel.
Data Availability - Since the data is stored in the analytical database inside the excel workbook, it is available to be used in Pivots & Charts which gives you amazing data visualization capabilities.
Eliminates Lag - If you have worked with huge datasets in excel, you would know that applying formulas & working with it in general is a pain. It will crash, processing time varies a lot and can lead to delays in your workflow. But with Power Pivot, importing huge data, analyzing it & then applying complex calculations on it using DAX (Data Analysis Expressions) is a breeze.
Comments