Work Smarter: Tips and Tricks: Next Level PivotTables.

AuthorLenning, Jeff
PositionTechTalk

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.

VLOOKUP No Longer Necessary

For starters, Power Pivot 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...

To continue reading

Request your trial

VLEX uses login cookies to provide you with a better browsing experience. If you click on 'Accept' or continue browsing this site we consider that you accept our cookie policy. ACCEPT