everydayExcel Newsletter Header

PowerPivot is not Pivot Table

Almost all the people whom I spoke to about PowerPivot had the impression that PowerPivot was PivotTable and believed that they knew how to use it. So, I wouldn't be surprised if you think the same too. PowerPivot has a similar name and a similar appearance as PivotTable. It would not be easy to see the difference unless you have actually worked on a PowerPivot before. In this write-up, I would like to share with you 5 key differences which I hope will help you differentiate the two and convince you that it is worth your time exploring this brand new function in Excel 2010.

Difference #1 - PowerPivot Has Its Own Tab

PowerPivot has its own tab on the ribbon with its own functions while PivotTable shares a tab with many other functions in the Insert Tab.

PivotTable Tab

Powerpivot Tab

If you click on PivotTable, you will see 4 boxes displayed at the bottom of its field list. PowerPivot has 6 boxes, 2 more than the Pivot Table. The two extra boxes are for the vertical and horizontal slicers.

field list

Difference #2 - Superb Processsing Speed

PowerPivot processes records at one tenth of the speed of normal PivotTable. One of my clients is using PivotTable (with macros) to process 1 million rows of data. Refreshing the PivotTable took about 10 minutes. The moment I convert the PivotTable Report to PowerPivot, the refreshing time dropped to less than 1 minute, registering a processing speed that is 10 times faster than PivotTable.

Difference #3 - Complex Calculations

In our PivotTable report, there are times we have to present the Top 5 results. This always leads to the need to calculate the results as a percentage of the company total. Using PivotTable, this percentage calculation has to be done outside the Pivot Table. But in PowerPivot, the calculation can easily be done as a Measure (new function in PowerPivot) using DAX formulas and presented together in the PowerPivot. Other calculations that are now possible with PowerPivot include YTD, LYTD and Variance. There are more than 100 DAX formulas you can use with PowerPivot.

Here is the Year-To-Date formula to get you started.

=CALCULATE(Actual[TM Actual Sales],DATESYTD(Calendar[DateKey]))

Difference #4 - Getting Data from Multiple Sources

PivotTable can only work with a single source of data stored in one worksheet. If you need to use data from another source, you have to append it into the same worksheet, probably using the VLOOKUP formula. One example is the combination of Actual numbers with the Budgeted Numbers for comparison purposes. PowerPivot Table, on the other hand, can combine data from multiple sources together with just a few clicks. You can even combine data from different data source types (such as CSV, Excel, SQL, Web-bsaed data, etc) and present them in a single PowerPivot Table. The data from multiple sources are permanently linked and updated with a single click on the Refresh ALL icon so there is no need to rework on the data every time.

PowerPivot multiple sources

Difference #5 - Trend Analysis with PowerPivot Chart

In PivotTable, you select the year and month and Pivot Chart will automatically show you an updated chart with values that are directly related to the selected year and month., whereas, in PowerPivot, clicking on the same year and month will provide you with a Pivot Chart that displays the trend for the past 2 years.

trend analysis

If you are currently facing constraints in your current PivotTable reports, sign up for our PowerPivot course and learn how to create an impressive PowerPivot Report. I believe you will not be disappointed with what you learn.

The Second Bad Habit (Con't from last issue)

To find out whether you have the second bad habit, try this:

  1. Input the word "Excel" into cell b3.
  2. Let go of your mouse and keep your hands away from the keyboard.
  3. Locate where the cell is current selected.
  4. If your cell is not in B4 or C3, you probably have not used the ENTER or TAB key. Instead, you have used your mouse to confirm the entry you have made on Cell B3.

Ask us an Excel question and get your question answered by our team of Excel experts

Click on this link to post your question.

Upcoming Courses

With the newly introduced PIC bonus, you are now paid (not paying) 60% of the course fees for sending your staff to our courses. Check out now in this IRAS PIC Link.

Hidden Secrets in Data Analysis with Excel 2007/2010 - 30 & 31 Mar 2015 (confirmed)

The Hidden Key to Excel Reporting and Charting - 19 & 20 Mar 2015 (confirmed)

Unleash the Power of Excel Power Pivot for Data Insights - 6 & 7 Apr 2015 (confirmed)

Learning the Magic of Macros 2007 (Excel VBA) - 5 & 6 Mar 2015 (confirmed)

Breakthrough Performance for HR in Excel 2007/2010 - 12 & 13 Mar 2015 (confirmed)

Dynamic Real-Time Forecasting with Excel 2007/2010 - 23 & 24 Mar 2015 (confirmed)

For the details of the courses, please refer to our Excel courses page.

Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.

Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.

Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"


Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.