everydayExcel Newsletter Header

Achieving the Impossible with Excel Power Pivot

No newsletters were sent out for the last few months as we have been extremely busy updating our existing courses with new contents and developing our new Excel course on PowerPivot, a relatively new function in Excel 2010. With added capabilities that Excel users have long wished for in Pivot Table, PowerPivot comes with new features and functions that allow Excel to better compete with other proprietary analytical reporting tools. Power Pivot Table in Excel 2010 will revolutionize the way Excel is used. Its new uses that were impossible to achieve with the current Pivot Table have not ceased to amaze us.

Note: Power Pivot and Power Pivot Table are 2 Different Related Functions

When we first learnt about Power Pivot Table, it looked like an enhanced Pivot Table function that allowed users to analyse more than one million rows of data. The enhancement did not appeal to us as the chance of analyzing more than one million rows of data is pretty slim. When we need to extract more than 1 million rows of data for one of our clients, we used Macros instead. Books on PowerPivot published by well-known Excel experts did not offer any more revelation other than the DAX formulas that are readily available in Microsoft website.

The second review of Power Pivot Table in the second half of last year generated new discoveries and got all of us excited again. We found that most of the books published undermined the capability of Power Pivot Table and did an injustice to the function. Here is what we have since discovered about PowerPivot Table.

Superior Processing Speed

Unlike the normal Pivot Table, Power Pivot Table is capable of processing data at lightning speed. To process about 40,000 rows of records took only a few seconds, unlike MSQuery, which can take up to 10 mins to process the same number of rows.

New Sets of Formulas

Power Pivot comes with a new set of formulas (known as DAX) that can process complicated calculations such as YTD numbers based on Financial Year instantly without having to make changes to the raw data. Normal Pivot Table function may take hours to process the same calculation. Another example of calculation that can be processed easily by Power Pivot Table is the variance between 2 periods (Last Month vs This Month).

Multiple Sources of Data

Normal Pivot Table can receive data from one single source whether it is from an Excel file, text file or databases. Power Pivot Table can combine and link all the sources together and present the consolidated data in the same Power Pivot Table. With Power Pivot Table, there is no longer any need to know Excel's VLOOKUP formula.

Synchronised Filtering with Slicer

Power Pivot allows users to create multiple Power Pivot Tables from the same source. With the addition of the slicer, all the Power Pivot Tables can be linked together so that a single click on the slicer will update all the Power Pivot Tables at one go. There is no need to repeatedly select the same criteria for each and every Pivot Table. This is especially useful when Power Pivot Tables are deployed within a Dashboard or Management Report, which is what our course "Gaining Business Insights with Excel Power Piovt 2010" demonstrates. To lean how you could enhance your reports and at the same time learn about Power Pivot, sign up for the course using the link given below now!

Gaining Stunning Business Insights with Excel Power Pivot 2010

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 - 14 & 15 Aug 2014

Interactive Dashboard Reporting with Excel 2007/2010 - 14 & 15 Jul 2014

Gaining Stunning Business Insights with Excel Power Pivot - 31 Jul & 1 Aug 2013

Learning the Magic of Macros 2007 (Excel VBA) - 18 & 19 Aug 2014

Breakthrough Performance for HR in Excel 2007/2010 - 28 & 29 Aug 2014

Dynamic Real-Time Forecasting with Excel 2007/2010 - 10 & 11 Jul 2014

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.