How to Create PivotTables That Work (Really!)

Staring down mountains of data, is your heart rate ticking upward and upward? Are your hands trembling as you reach for your mouse to make sense of the hundreds (maybe thousands?) of rows of cells full of data?

While an analyst may recognize the need for a PivotTable, this misunderstood tool often strikes fear in the hearts of even the noblest of data analysts. Once you learn how to create PivotTables that work, you’ll be able to display data to get answers to your questions at a glance.

Why Bother with PivotTables?

PivotTables are a powerful data summarization tool in Excel and are especially useful for working with large amounts of data. A good PivotTable enables you to view key information from what might otherwise be insurmountable amounts of data.

This blog will take you through some of the basics to create PivotTables that work for you. For this example, let’s say you want to find out which of your branches originated the most loans in the last month as part of a friendly competition for a $100 gift card. Taking it one step further, let’s also say you also want to award the loan officer who opened the most loans.

Gathering Great Data for a Great PivotTable

Before you can create a great PivotTable, you’ll need great data to work with. Using GOLDPoint Systems’ data-extraction tool GOLDMiner, you can download the data you need in an Excel spreadsheet for further manipulation. For this example, we grabbed all loans opened between the dates 6/30/2019 to 7/31/2019. To help sort this data in our PivotTable, we also requested the Branch/Office Number, Loan Officer Number, Loan Dates, and Loan Amounts. Learn how to get exactly the data you need by checking out this blog post on Data Mining.

A good PivotTable starts with good data. There should be no blank rows or columns and all columns should have clearly labeled headings. (Column headings will be your field names in your PivotTable, so this is critical!)

Getting Started: Creating Your PivotTable

1. Select the set of data or table you would like to create a PivotTable from and click the Insert tab. Here you’ll click the PivotTable icon.

2. This will open a window where you can verify your table selection is correct and decide if you would like your PivotTable in a new worksheet.

3. Now you’re ready to start setting up your PivotTable. Check the relevant fields (see example below). It can be helpful to drag and drop and shuffle fields around to give you an idea of what each zone (filters, columns, rows, and values) does for your data. Microsoft also has this great guide for designing your PivotTable if you’re ready to get creative.

But to get to the nuts and bolts of it, a numeric value should go in the Values section. Then you use the Rows (and/or Columns) section to label (or assign meaning to) those values. Here, “Original Face Amount” (total for loan amount) is in the Values section. In the Rows section, that data is broken down by both Branch Number and Officer Number. Below are the results of this particular configuration.

This PivotTable shows the dollar amount of the sum of loans originated in July. Because we have both Branch ID and Loan Officer ID in the Row section, Excel has listed the Branch and the associated total loan amount. Then, you can expand a Branch and view the sum of loans by Teller ID.

But you wanted to find out the number of loans originated. Easy enough. In the Values section, click the down arrow to open a menu and select “Value Field Settings.”

The menu below will open, and you’ll select "Count."

Now you’ll see the number of loans per branch and loan officer. Looks like Loan Officer #1003 is killing it at Branch 9995!

Don’t be afraid to experiment a little to find what configurations work best for your particular set of data. You may even want to create a couple different PivotTables from the same data to highlight specific information.