Real Auditors Use Excel PowerPivot

If you’re an auditor and you are not yet using Excel PowerPivot, you are missing the next greatest thing since spreadsheets arrived.

If you are NOT an auditor, and you don’t use PowerPivot, you’re in the same boat with the auditors mentioned above, and it is sinking.

In other words, if you use Excel, you should be learning Excel PowerPivot. It’s that big.

Let me explain why.

NOTE: I updated this post quite a bit with new info…

First, PowerPivot has been described as Excel Pivot Tables on steroids. If Pivot Tables scare you, you can use PowerPivot without making any Pivot Tables. More on that later…

Back to why you should use PowerPivot:

1. PowerPivot is an add-in that’s already available in Excel. If you have Excel, you have the add-in. Free.
2. PowerPivot is virtually unlimited in the number of rows of data it can load and analyze. I mean millions and millions
3. Regular Excel can only pivot on a single table. PowerPivot can pivot from multiple tables simultaneously! So when you need to join files, you can kiss vLookup goodbye. To join tabs or tables, you just click and connect. No more formulas! In the example below, I right-clicked on the ID field, held the mouse button down, and dragged to the ID field on the right. That’s it for the join. Pivot the table* and you have a joined file.
   Click the image below to enlarge it…
   And you can join multiple files together, as long as each file has a matching key field (in this case, ID). The field names do NOT have to match, but the data does. In other words, you can join table1 with a field called ‘ID’ to table2 to a field called ‘UserID’ to table3 with a field called “User”. The field names don’t have to match, but at least some of the IDs in each field must be identical.

4. One other thing: at least one of the tables must have unique values in the key field.

5. *The only catch is that you need to learn PivotTables, which are not that hard. Here’s some resources: Excel 2013: PivotTables for Absolute Beginners (youtube) and Excel Easy: Pivot Tables (step-by-step).

6. When you create Pivot Tables in PowerPivot, they are SO MUCH FASTER, even with all those rows of data!
7. PowerPivot gives you MANY, MANY more options for Pivot Tables, as well as other analysis features that Excel doesn’t have by itself.
8. PowerPivot is the foundation that the rest of the Microsoft Business Intelligence software (Power BI) is built upon. So what you learn in PowerPivot you can also use in Power BI, which is the cheapest (free) and might be the most powerful BI tool on the market today (and yes, I’m including ACL and Tableau in that assessment). See this article for the Difference between PowerPivot, Power Query and Power BI (it also mentions Power View, but I don’t think that tool is worth your time).
9. PowerPivot is also the power behind Power Query, which is a powerful way to load, clean, and transform data THAT IS EASY.
10. Like most software, you can learn the basics for free (see way below).
11. Excel and PowerPivot are the two pillars that support Power BI. You already know Excel, and Microsoft built PowerPivot AND Power Bi in a way that uses your Excel knowledge and extends it. One person describes it like this: Microsoft took a bi-plane (Excel) and gave it jet engines (PowerPivot and Power BI) without changing the cockpit.

The only limitation is the RAM in your computer. Even if you don’t have much RAM, can’t get any more, you can still analyze more data with PowerPivot.  Why? Because PowerPivot compresses data (which regular Excel does not), which allows you to do more with the RAM you have.

If you can get more RAM, 16 GB RAM is recommended. To take advantage of all that RAM in Excel, you’ll need a 64-bit OS and 64-bit Office. That configuration will allow you to work with at least 25 million rows and more! If you think that configuration is beyond your reach, consider the info in the box below.

Ask your IT department to create one or more virtual machines (VMs) with this configuration. It’s a lot cheaper, and you’ll get the machine faster. Skyyler explains additional reasons to use VMs in this post: Why Use Virtual Machines (while the focus of that article is about running ACL in a VM, all the reasons noted there apply to Excel and all other programs as well).

The only limitation I’ve found is when you have 7 or more million records and 60+ columns. If you limit the number of columns, that really helps.

Using PowerPivot without Pivot Tables

So how can PowerPivot help you if you don’t know how to create pivot tables?

• When you load data into PowerPivot, you can filter the data before you load it. In other words, if only need 2Q data and the file contains an entire year, you can just load 2Q data using filters that function exactly like Excel filters (because that’s what they are). Or you can filter out all records that contain a blank in certain fields, or drop entire columns all together. Before you load the file.
• As noted above, you can easily join files together.
• Once you have a single file loaded or multiple files joined together, you can then filter the data just like Excel.

So the bottom line: If you have a file that’s too large to use regular Excel, you can load it into PowerPivot and doing basic filtering, total columns, and other basic stuff.

Of course, I’d still recommend you learn to do Pivot Tables, as that unleashes the real advantages of the tool.

Three last comments…

First, PowerPivot is a little challenging to learn, but worth it. The most confusing part of learning it is understanding that PowerPivot has 2 separate windows you work in, the “regular Excel” window and the “PowerPivot” window.

Both of them belong to the same Excel file. Keep that one thing in mind, and you will avoid much of the confusion.

Second, PowerPivot was the next evolution of Excel, and Power BI is taking it even further (although it is a separate application, it’s actually a combination of Excel, Power Query, Power Pivot, and DAX (Data Analysis Expressions, the new Excel function language with many additional capabilities). So the bottom line is that you need to learn PivotTables and PowerPivot now, as they aren’t going away.

Third, PowerPivot, Power Query, and Power BI can be used separately or together. I know that’s confusing, but that’s Microsoft.

Which leads me to my final topic . . .

Learning PowerPivot

Here’s some resources to get your started. Neither skyyler, me, or this blog is affiliated with these websites or the training that is offered.

I have listed them in the order I suggest you review them. If you read through one or more of these, you’ll gain a good overview of PowerPivot.

The first link below offers a PowerPivot course, but you can preview several helpful lessons without signing up (for a limited time). Just click one of the green FREE PREVIEW buttons. If you don’t do anything else, look at the lesson titled Differences between Excel and PowerPivot use.

http://online-excel-training.auditexcel.co.za/course/powerpivot-training-online/ 

https://www.codeproject.com/tips/621908/beginners-guide-to-powerpivot-for-excel

https://www.excelcampus.com/pivot-tables/powerpivot-instead-vlookup/

http://blog.aicpa.org/2014/03/powerpivot-a-game-changer-for-excel-pivot-table-users.html#sthash.KhA4uUrR.dpbs

https://msdn.microsoft.com/en-us/library/gg413497(v=sql.110).aspx (several Microsoft tutorials–scroll down to the What You Will Learn topic)

I highly recommend powerpivotpro.com and all the articles they have regarding Excel, Power Query, Power Pivot, and DAX. I’ve met Rob Collie (formerly on the Excel team at Microsoft) and some of his staff, and the resources on his site are amazing, and many of them are free.

Your Turn

Has anyone played with or used PowerPivot yet?