Issue - February 2015
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.
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.
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.
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.
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]))
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.
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.
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.
To find out whether you have the second bad habit, try this:
Click on this link to post your question.
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.
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.
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.
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.