If you use Pivot Table, then you need PowerPivot! Read on to find out why.
If you have never used Pivot Table, you should not read this article. You must learn Pivot Table first.
PowerPivot is an add-in which works on Excel 2010 / 2013 (Professional Editions only). For 2010 you have to download and install it from PowerPivot.com
For 2013, it is a part of the base installation. However you still have to activate it. File – Options – Add-Ins – Open Dropdown – COM Add-ins – GO. Enable the checkboxes next to PowerPivot, PowerView and Inquire. We will discuss the other two items later. This time we focus on why PowerPivot.
Finally you should see a PowerPivot tab in Excel.
Contents
What is PowerPivot
It is a new way of creating Pivot Tables for analyzing data. It eliminates all the shortcomings or limitations of traditional Pivot Tables… and adds more features.
Limitations of the good old Pivot Table
Large data = slow performance
Pivot Tables cannot handle very large amount of data. Even with few hundred thousand rows it starts to become slower and slower. Often you have to wait for a long time for a drag and drop of a column to finish all the calculations. Opening such large files is also extremely slow.
Row limit of Excel sheet
Maximum of 1,048,576 rows… just over 1 million. That is the limit. If your data is bigger than this, you must use some external database and then connect Excel to it. Of course, even if it works the usage will be extremely slow.
In addition, we have dependence on the external database – which makes sharing the files more difficult.
Pivot Table works on one tabular block of data
If your data is originating in multiple blocks of information, Pivot Table cannot handle it.
For example, you have transactional data of 100 rows containing the columns
Product ID, Qty, Price, Total Cost
You also have a master table containing Product Id and Product Name.
Pivot Table cannot be told to use data from the Transaction table along with the Product Master.
The workaround is to use VLOOKUP…
The VLOOKUP problem
That brings us to the next limitation of Pivot Table. Continuing with the example above, we will have to add a new column in the transaction table which uses VLOOKUP to lookup the product ID from the Product Master table and get the value of Product Name.
VLOOKUP function is now 10,000 times making the workbook even more slow to respond. Furthermore, there are only a few products. However the name of each product repeats thousands of times in the transaction table – leading to increased file size reducing the performance further.
It is not uncommon to have multiple VLOOKUP based columns in a single Pivot Table.
Yes, there is a workaround – Copy paste the VLOOKUP column as values immediately after the formula is pasted. This way we retain the desired output but eliminate the overhead of VLOOKUP. Although this works, when data changes (more data is added) you have to add more VLOOKUPs manually and then convert them again to values.
This is repetitive and cumbersome as well as error prone.
Either flat data or cube data
Many people have not explored Pivot Table as a front end to an SQL SERVER cube. Pivot Table integrates beautifully with SQL server cubes and exposes all features of the data warehouse elegantly. However, there is a limitation.
You cannot combine some data from the cube along with some data which is outside the cube. In practice, this type of requirement is common.
The solution: PowerPivot
PowerPivot is designed to solve all these problems and more – much more.
Here is the concept.
Stores data inside PowerPivot. Not Excel worksheets
Whether data originates in Excel or other places, PowerPivot stores it. Data is NOT stored in regular Excel sheets at all. This way, the storage mechanism can be made more elegant, fast and scalable.
Diverse data sources
It can create a Pivot Table using data originating from different sources. For example, transactions data can originate in a database whereas the Product Master could be a simple Excel table.
Handles millions of rows of data without inflating the file size
PowerPivot can store millions of rows of data. Of course we are worried about file size bloating up. But don’t worry. It stores it in a highly compressed manner. You can actually try this out. Take a large Excel file with data contained in a worksheet. Note the file size. Now import the same data into PowerPivot, save the file and check out the difference. You will be very surprised. The file size can reduce anywhere between 4 to 100 times depending upon the pattern of the data (compressibility).
Eliminates VLOOKUPs by creating relationships
Excel is not a database. We were using VLOOKUP as a workaround to create relationships. PowerPivot – like a database – allows you to specify which field in the transaction file relates to (is same as) the field in the master file. This type of MANY-to-ONE relationship eliminates the need for VLOOKUP and associated performance penalty.
More functions
As though this was not enough, PowerPivot adds more functionality which is completely missing in Excel.
- Ability to create hierarchies from flat data (Category, Group, Product, SKU)
- Ability to use Picture URLS (for use in PowerView)
- Creating Flat Pivot Tables
- 70+ new functions which extend the capability of Excel to something similar to sophisticated cube based formulas (without the need to learn complex MDX syntax)
- Time intelligence functions are very powerful and extremely useful
- Ability to merge cube and flat data
- Ability to refresh all data sources
- Calculated columns for additional customization
- Default field sets, and many more…
In the next article we will see how VLOOKUP can be eliminated using PowerPivot.
