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.
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:
- PowerPivot is an add-in that’s already available in Excel. If you have Excel, you have the add-in. Free.
- PowerPivot is virtually unlimited in the number of rows of data it can load and analyze. I mean millions and millions!
- 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.
*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).
- When you create Pivot Tables in PowerPivot, they are SO MUCH FASTER, even with all those rows of data!
- PowerPivot gives you MANY, MANY more options for Pivot Tables, as well as other analysis features that Excel doesn’t have by itself.
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).
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..
Two 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, I expect that some version of PowerPivot will become, eventually, the new Excel, and you won’t have 2 windows, just one. Since we’ll have one tool, and it will be powered by the PowerPivot engine (which I understand is similar to the SSIS engine used in SQL Server), you need to learn PivotTables and PowerPivot now, as they aren’t going away.
Which leads me to my final topic . . .
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.
https://msdn.microsoft.com/en-us/library/gg413497(v=sql.110).aspx (several Microsoft tutorials–scroll down to the What You Will Learn topic)
Has anyone played with or used PowerPivot yet?