News & Updates: Data in Practice: PivotTable Basics

PivotTables are a surprisingly straightforward and highly underutilized Excel tool, that can serve to quickly organize and present critical information in a case. Ask my colleagues: I find uses for PivotTables in nearly every data set I work with.

Let’s start with the data set we worked with for table formatting, some employee time entries, available here. 

Inserting a Pivot Table

Click on any cell in the table, press CTRL+A to select just the table (you can manually do the same, but make sure to select only your data set). Then navigate to the “Insert” tab on your top ribbon, and click on the button at the far left, “PivotTable.”

You will get a pop-up like this. Select “New Worksheet” and click OK.

Building Your Table

To start, take a look at the PivotTable fields. They will be visible when you click on a cell in the blank PivotTable box shown above, generally at the right side of your window. Each field listed corresponds to a column of data in the source table. PivotTables provide a way to aggregate and compare fields in your tables with one another.

Let’s start with a basic example. Say you want to see the number of hours that each employee worked. The two fields (columns from your data set) that you are working with are Name and Total Hours (note, you must have the hours formatted as numbers, not text, for this summary to work). In this example, I want each employee’s name to be listed on a row, with the total hours they worked as my output.

I simply need to drag the respective field to the “Rows” and “Values” menu in the PivotTable fields menu, as shown below. The output in my PivotTable includes the Sum of Total Hours, as shown in the column header.

You instantly can see the total hours worked for all employees on the list.

Understanding Source Data

To clarify what data is being displayed, let’s take a specific example – Alaina Whitaker. From our PivotTable summary data, we see she worked a total of 15.167 hours, or an average of 7.583 hours per day.

If you navigate back to your underlying data sheet, you can see where exactly those numbers are coming from. Filtering by Alaina’s name gives us the following data.

The values we are looking at are drawn from the “Total Hours” column. We can see that the totals are 7.6333 and 7.5333 respectively. The sum of those two numbers is 15.167. Divided by the two days of data that we have, it generates an average of 7.583.

The practical applications of this tool in legal contexts are incredibly broad, a subject I will dive into more deeply in later posts.

---

Sheet Cheat

The Sheet Cheat will feature a short excel function or data tool for readers to try out. Please share your favorite cheat via email, and I may include it in a future post!

You may have been in this position – you want to reference a specific cell in a spreadsheet, but when you cut and paste the reference into another cell, suddenly the reference moves. Often that’s helpful! Sometimes, it’s not.

Let’s work through this with an example. I have a task list that might look something like this.

I built a quick function to calculate the difference between the task’s due date and today’s date – but it doesn’t work how I intended. The top row calculates the difference correctly, referencing the cell with Today’s date (G2). But when it filled in the rest, the references changed – to G3, G4, and G5 as the rows ascended. That’s not what I wanted.

When the cell references change like this, as the location of the destination cell for a function changes, it is called a “relative” cell reference.

What we need here is an “absolute” reference – one that doesn’t move when you cut and paste. There are two easy ways to do this:

1. Denote absolute cell references with a dollar sign.
The most common way to mark an absolute cell reference is with a dollar sign. To do this, simply type a dollar sign in front of the column and row, like this:

2. Denote an absolute cell reference with a cell name.
To do this, click on the cell you want to reference, navigate to the box to the left of the function bar where the cell number is displayed, and 👏Name 👏That 👏Cell👏! Type a name (no spaces) and click Enter.

I named the cell “TodaysDate.” Now I can simply write the cell name in a function when I want to reference it. Populated into my table, it now automatically – and absolutely – references the named cell.