Analyze Matter Data with Pivot Tables

In this post I’ll explain how to use one of the most powerful data analysis tools in Excel – the Pivot Table.  My example involves a list of new pro bono matters, but pivot tables are also an effective way to analyze email metadata or any other large data set.

Suppose your firm signed up 140 new pro bono clients across nine different programs in the last quarter of 2014. Each matter is represented in a row of the spreadsheet shown below. For each new matter, there are fields for the program, responsible attorney, date opened, and several other data points.

PT1

Now suppose you want to know how many new matters were generated in each program, or which program had the highest average cost in this quarter, or see a breakdown of new matters by borough? By month opened? By advocate? You can answer all these questions with a Pivot Table.  Keep reading to learn how.

You can download the Excel file by clicking here.

First, for Pivot Tables to work properly, you should make sure your data is the form of a table. That means no blank rows or blank columns in the data you want to sort.

To insert a pivot table, start by clicking anywhere in your table. Then navigate to the Insert ribbon and select PivotTable on the far left:

PT2

By default, Excel selects all the contiguous data for inclusion in the Pivot Table, and places the table on a new worksheet. You will usually want to accept these default settings:

PT3

Excel takes you to the new worksheet where the Pivot Table has been created. All you see at first is this intimidating diagram:

PT4

The first thing we’ll do is tally the number of clients in each program. Drag the “Program” field from the list into the Row Labels field and into the Values field.

PT5

Like magic, Excel tallies the number of clients in each program, as well as the grand total:

PT6

Now you can get a breakdown of the programs by any given category. Let’s see how many of the clients from each program are from any given borough. The trick is to visualize how you want the data to look and anticipate what the Row and Column headers will be.

Since we want a separate column for each Borough, we drag the Borough field into the Column Labels area.

PT7

Voilà:

PT8

NB: You can change the appearance of the headers by choosing Design –> Report Layout –> Show in Tabular Form

You can even drill down on a certain data point to see which records it includes.

At the intersection of Queens and Bankruptcy Clinic there is a 5, indicating that there are 5 clients in the Bankruptcy Clinic from Queens. Double-click on the number 5 and something neat happens:

PT9

Excel copies those 5 records and pastes them into a new worksheet! It’s a good idea to rename the worksheet to remind yourself what this data is. Double-click on the tab name, type the new name, and hit Enter.

Group by Date

Now let’s generate a breakdown of all the new matters by month.

The first step is to pull the dates into the Column area and see a breakdown by date.

PT10

This is not very useful yet, because it creates a breakdown for every single day. To collapse the breakdown to months, click on one of the dates and then select Group Field from the Options ribbon:

PT11

Choose Months and Years and hit OK

PT12

Now the Pivot Table will break down the count by Month:

PT13

Tally the expense subtotals by program

Remove the Date field from the Column area and drag the Expense field to the Values area:

After formatting these values as USD, you will see the subtotal of expenses for each program, along with the number of clients.

PT14

NB: You can format all the sum and average cells as US Currency by selecting them and then hitting Ctrl+Shift+$

Calculate the Average Cost per Client in Each Program

Now let’s look at the average cost per client for each program. The first, counterintuitive, step is to drag the Expenses field into the Values field again.

PT15

Now you’ll have two columns in the pivot table representing the sum of the expenses for each program:

PT16

Double-click on the Header, the cell that reads Sum of Expenses2, and you will see the option to change the value to an average:

PT17

Now you’ve got the Total in one column and the Average in the other:

PT18

You can also sort these Pivot Table results. If you wanted to view the above table by most expensive to least expensive program, you would click in the Sum of Expenses column and click the Z–>A button on the Options ribbon (you should be there by default):

PT19

With this result:

PT20

To make the Pivot Table more presentable, you can get rid of the Row Labels title by hitting the Field Headers button, and edit the other column headers by adding your own titles:

PT21

One last thing to remember about Pivot Tables: they do not update automatically if you add, delete, or edit the underlying data. If you want the Pivot Table to update, Go to the Options tab and hit the Refresh button.

PT22

These techniques are covered in-depth in my new CLE course† Excel Essentials for Busy Lawyers. To learn more about hosting a session of Excel Essentials at your law firm, company, or public interest organization, please email Ben Kusmin at Excel Esquire.

†The required fine print: Approved in New York for 2.0 CLE credits; approved for both new and experienced attorneys; financial aid available.
Advertisements

About excelesquire

NYC attorney and Excel enthusiast.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Analyze Matter Data with Pivot Tables

  1. Pingback: Discovering pivot tables | Sandline Blog

  2. BelajarExcel says:

    Pivottable is one of my favorite tool in excel.
    Many thanks for this great and helpfull tutorial. It add my knowledge about pivottable

    Like

Leave a Reply

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