Real Auditors Use Excel PowerPivot

powerpivot iconIf 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…
    PowerPivot Join Files, no vLookup

    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.

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

    *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).

  4. When you create Pivot Tables in PowerPivot, they are SO MUCH FASTER, even with all those rows of data!
  5. PowerPivot gives you MANY, MANY more options for Pivot Tables, as well as other analysis features that Excel doesn’t have by itself.
  6. 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).
  7. PowerPivot is also the power behind Power Query, which is a powerful way to load, clean, and transform data THAT IS EASY.
  8. Like most software, you can learn the basics for free (see way below).
  9. 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. I’ll try to tackle this in a future post.

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?

Advertisements

13 Comments

Filed under Audit, Data Analytics, Excel, Free, Technology

13 responses to “Real Auditors Use Excel PowerPivot

  1. Pingback: Real Auditors Use Excel PowerPivot – sec.uno

  2. Pingback: SutoCom Solutions

  3. blair151

    Sounds awesome, but unfortunately I can’t play with PowerPivot using my Office 365 Home subscription — it’s not there. I’ll have to make time during work hours using our E3 subscription, which hopefully has this feature. :-(

    Like

  4. Audit Monkey

    Real Auditors get someone to do it for them.

    Like

    • My old friend,
      I know you are just poking fun, but I’m going to take the bait anyway. My point is that unless auditors are keeping up with the latest tools (Microsoft’s or other) and techniques to analyze huge amounts of data, they will be left behind (and I am not referring to the rapture).

      At least I didn’t say in my post: “at least use Microsoft Access”!

      However, I did use Access in an audit this year, but only because the client gave me the data in an Access database. When I asked the client about it, I learned that she actually used 4 Access queries to filter 4 datasets and then other Access queries to join them together. So of course I requested those queries and vetted them to ensure the data was appropriate and complete.

      Personally, I don’t have a problem with auditors using Access if it does the job, especially if they already are familiar with Access AND because it can handle a lot more data than 32-bit Excel without PowerPivot. I know you hate Access, but we have debated that elsewhere on the ‘net.

      Take care, Monk, and thanks for stopping by. I can always use a good poke.

      Like

  5. Pingback: Auditors, Do Data Analytics or Die | ITauditSecurity

  6. Since it’s been 4 months and still no auditors have commented anything about using PowerPivot (other than Blair, and I don’t think he’s an auditor), it appears to confirm how far internal auditors are falling behind in their use of technology, especially analytic technology.

    PowerPivot has been around since 2009!!!!!, and I just heard about it for the first time last year. Most auditors have not even heard about it. Part of that is Microsoft’s fault for not marketing it properly, but mostly it’s audit’s SALY attitude in not wanting to learn new things.

    Also, since PowerPivot is free if you already have Excel, auditors are still ignoring it.

    I suppose that’s because most auditors are scared of pivot tables, so they are powerfully scared of Power Pivot. I recently read that only 5-10% of ALL Excel users can create pivot tables (that’s all Excel users, not all auditors). I guess that explains a lot.

    Good grief auditors, step up!

    If you want to stand out and keep your budget expenses low, learn PowerPivot. It’s the next analytic revolution.

    Like

  7. Pingback: Use LinkedIn to get an IT Audit job | ITauditSecurity

  8. Pingback: Excel: Basic Data Analytics | ITauditSecurity

  9. Luciano

    Hi, I’m an auditor and came across this article via a google search looking for “power BI instead of ACL”. I’ve been using ACL for many years and recently started to explore the Power BI world. I’m quite impressed and started to wonder whether I need ACL at all. At this stage I’m using ACL to do the bulk of the analysis, and Power BI for visualisation, but it seems like I could do it all in Power BI.
    Now for Powerpivot, I’ve not yet seen the tool, so can’t comment on it. What would it give me that Power BI can’t?

    Like

    • Luciano,
      PowerPivot is a stand-alone Excel add-in, but the same engine that powers PowerPivot is built into PowerBI. So the only thing PP gives you that PowerBI can’t is really huge PivotTables. So not a whole lot. Both PP and PBI have the DAX expressions. If you haven’t looked into DAX yet in PowerBI, put that on your list.

      PowerBI could probably replace ACL in many cases, but PowerBI doesn’t have an EXECUTE command that I use a lot in ACL to call other programs. The other thing I haven’t figured out how to do in PowerBI is ACL’s GROUP and LOOP commands that are pretty helpful.

      Thanks for stopping by. Glad you’re looking past ACL!

      My original intent in writing this post was to give auditors who DON’T use ACL or Power BI, but depend on Excel additional horsepower to handle bigger data files.

      Like

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s