01 – Powerpivot | Introduction to Powerpivot – what is it and why would you use it?

Powerpivot is a new free add-in that first appeared in Excel 2010. As a trainer I’m incredibly excited about the possibilities this offers Excel users to streamline their work and to do a lot more with their data. Imagine just updating your data connection and your pivot tables automatically updating to show current data. Imagine showing your boss or clients how they can quickly link budget, accounts and transaction data and whip up a powerpivot – complete with charts in 10% of the time it used to take…

Here are your hardware requirements

If you use pivot tables, you are probably pretty familiar with its strengths:

Quickly summarize large amounts of data by regions, months, years etc
Pivot Tables
Do percentages in one fell swoop
Quickly add snazzy charts.

However you have probably also come across its limitations..

Only work on one data source (although that has changed a bit in Excel 2013)
Have to create cobwebs of vlookups to link multiple data sources if you do want to work on multiple data sets and the attendant problems of N/As etc
Sometimes if you have large data sets, it just takes a long time…
Unless you have your data source set up as a table, when you add new records you have to re-select the data source and refresh.

So why is Powerpivot better?

First of all, because of the technology behind it, you can basically have unlimited amounts of data
You can store your data in separate tables and as long as you have some sort of matching field you can create a relationship ONCE and that stays no matter how much data you add to your source. So no more vlookups with their dreaded N/As
You can easily pull data from multiple sources – including text files, Excel sheets, Oracle databases, Access databases etc into your Powerpivot and then construct your mother of all pivot tables from it. This makes it much easier just to maintain your data.
If you are familiar with pivot tables, your learning curve is already made easier because of the overlap between Powerpivot and Excel formulas (although they are called DAX in Powerpivot)

What’s the catch?

Installing Powerpivot can be a bit like working with a nervous racehorse – it can bolt for no reason.
Powerpivot 2010 and Powerpivot 2013 are sort of like the characters in Step Brothers – they don’t get on. I’ve had to uninstall Excel 2013 and Powerpivot 2013 to get my Powerpivot 2010 to work.
When Powerpivot disappears, you may have to do a quick search…(This is for Excel 2010)
- File – Options
- Add-ins
- Look under Inactive (if it’s gone missing on you) – look for Powerpivot for Excel
- In the dropdown box below, (Manage – Excel Add-ins – Go)
- Choose COM add-ins – click on Go
- Tick the box Powerpivot for Excel
- Click OK.
- This usually brings it back.
- If it doesn’t you may need to re-install Powerpivot again, and/or Excel (yes, I know that is very annoying but I’m just letting you know up front…..)
Your data does need to be clean and organised in pivot friendly way. I’ve written about this before but you may well find that a good chunk of work is required to get the data organised in a pivot-friendly way. Many Excel users set up their data in a way that looks like a pivot table but which is not pivot friendly.

Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!) that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

MyExcelOnline.com

Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper.

Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.

PivotPowerPremiumRibbon

The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function

Chandoo.org

Cookie	Duration	Description
cookielawinfo-checbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.