The Power of Powerpivot | Excel’s new free plug-in to help you work with large amounts of data

Well, this week I’m going to write specifically about Powerpivot. Powerpivot is the new free add-in you can download free for Excel 2010 and later. However Powerpivot has a diva side – it’s been known to flounce off in a huff and only return when you coax it with a Repair via the Control Panel and via your Add-ins. So you need to be aware of that.

For people who work with large amounts of data, there are a few staples they always have to use:

But they can often present their own problems. Not least the challenge of when data is changed, added to etc. The idea behind Powerpivot is to eliminate all that. No more vlookups, now you have relationships. You still have sorting and filtering but then to quote from The Matrix “Some things never change”.

I’ve become really interested in learning more about PowerPivot (got three books already – see my comments below) as I see it as being a way to offer as good a service as possible to my classes and one of the authors mentioned (Rob Collie) is going to be offering an Advanced Powerpivot class in August (along with Chandoo). (FYI, this is an affiliate link but like everything I have an affiliate link on, it’s ONLY something I would use myself – eating my own dogfood as it were..)

I plan to enroll for this with a view to really upping my game and then to offer some more classes in it in the coming months.

If you are curious and would like to learn more…

Here are some websites

http://www.powerpivot-info.com/ (you can download some great data files here to work on)

www.powerpivotpro.com (This is the website hosted by Rob Collie – a true Powerpivot Pro and author of a really accessible book on this)

Here are the books I have found really useful as I start on this fascinating journey

DAX Formulas for PowerPivot: A Simple Guide to the Excel Revolution by Rob Collie

This comes with sample files and is written in an incredibly accessible style. It takes you from installing Powerpivot right up to using DAX formulas (Like Excel formulas only with some fancy additions like TotalYTD and Time Intelligence). And Rob loves his movies….

Microsoft® PowerPivot for Excel® 2010

I don’t think this one is as accessible as Rob’s books but it’s a really great solid textbook on Powerpivot

PowerPivot for the Data Analyst: Microsoft Excel 2010 (MrExcel Library)

Of course idle curiosity has made me ask if there is a Mrs Excel (and apparently there is – and has been for 22 years – but she’s not as much of an Excel head as her hubby). I liked this one a lot too.

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.