Pivot to the Next Level

I’m assuming you love PivotTables. I mean, who doesn’t, right? Well, traditional PivotTables are amazing, but they’re not without limits. Instead of using a traditional table as the PivotTable source, you can take your PivotTables to the next level when you use Power Pivot instead. Check it out.

Before we get too far, let’s clear up some names and terminology. The tools presented in this article have various names depending on the version of Excel and depending on the Excel user—including Power Pivot, the data model and the DAX engine. In older versions of Excel, the Power Pivot add-in required a separate download from the Microsoft website. In newer versions of Excel, you can simply click the little green Manage Data Model command. Once enabled, the data model (aka Power Pivot) allows you to build reports that were not practical, or possible, with traditional PivotTables.

For starters, it allows you to create a report from multiple tables. Wait … what? Yes. In a traditional PivotTable, the data source needs to be a single table. Thus, Excel users got really good at using functions such as VLOOKUP and SUMIFS to manually combine data from multiple tables. This step is not needed when using the data model. The data model allows you to use multiple tables and define their relationships. Once defined, you can create reports using various fields from the various tables as desired.

So far, we see we can use multiple tables, but it’s time to take another little step. Let’s distinguish data tables from lookup tables. For our purposes, data tables contain transactions such as invoices, sales receipts, expenses, checks and so on. Lookup tables on the other hand, contain lists of related items—such as a chart of accounts, vendor list or department list. In a traditional Excel workbook, if a data table had an account number but not an account name, we could use VLOOKUP to retrieve the related account name from the lookup table. The data model supports using a data table with numerous lookup tables. Which is cool. But, it gets even better.

Beyond using a single data table with multiple lookup tables, we also can have multiple data tables—for example: a budget table and an actual table, or a check
register and the bank activity download, or an ecommerce extract and an accounting system export. You get the idea. Plus, we can throw in the related lookup tables as well. Now we’re beginning to get a glimpse of the advantages of using the data model. But the fact that we can use multiple tables is just the beginning.

You see, in a traditional PivotTable, the underlying assumption is that the source data exists in an Excel workbook. So, the typical workflow begins by getting the data into Excel. How do we do this? Typically with a copy and paste. Then, we use a variety of techniques within Excel to get the data table ready, such as retrieving related values with VLOOKUP. Then, we build the PivotTable. Whew. We save the workbook, close it, and move on with our life. And life is good until next period, when we get to repeat the entire process again.

But with the data model, that traditional workflow is replaced. You see, Power Pivot can create a live, direct connection to the external data source. We can connect to a variety of external sources within one data model—including databases, CSV files and many others. We build our PivotTable, save, close and move on with our life. And life is good, even next period. Because we just need to do a refresh, and Excel pulls in the updated data and it flows into the report.
Voila!

If you’d like to get started using Power Pivot, I have a free video tutorial and blog post that will help you create your first data model PivotTable, entitled How to Build a PivotTable with the Data Model.

If you use traditional PivotTables often, it’s probably worth the time exploring Power Pivot. Using the data model as your data source may just help you automate some manual steps and help you get your work done faster.

And remember, Excel rules!

Note: Power Pivot is only available in some Excel versions. For a complete list, visit https://support.office.com/en-us/article/where-is-powerpivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b