News & Updates: Data in Practice: Simple PivotTables to Understand Fees

PivotTables have applications beyond simple addition and averages. Let’s take a look at some further applications of PivotTables using the attorneys’ fees data we modified last week.

Build your PivotTable

Remember, as a first step, to select your data set, and insert your PivotTable.

Attorney Summary

We can start our analysis by looking at a simple summary of attorney time, by selecting “Timekeeper” for our rows, and “Hours Worked” for our values.

But now, we included our “Category” field – so we can add that to the column to quickly see the breakdown of who was spending most of their time on which category of work.

It’s a Date

If instead of the specific attorney, you are interested in the date that work happened in specific areas, you can simply swap the “Timekeeper” for “Date” in your rows field.

Don’t fret. By including a field that is formatted as a date, Excel will automatically group the values, displaying a higher level of aggregation (here, by month), which you can expand by clicking on the plus sign next to the month.

If you do not want your dates aggregated, you can uncheck the Days (Date) and Months (Date) that Excel automatically added to your PivotTable Fields.

Field Filter

One other feature to highlight today, is the “Filter” tool that you can use in PivotTables. Let’s say that you just want to review those items identified for “Follow-Up” in our “Notes” field. Drag “Notes” to the “Filter” area of the field chooser, and then navigate to the arrow in the upper left corner of the PivotTable area.

There, you can see the time attributed to areas that need “Follow-up,” on which dates and in which category.

Switch this around by selecting the Timekeeper, and removing the Date and Category, to quickly see which attorneys had time entries that need follow up.

Adding Value

One last thing to flag – if you are interested in seeing both the time and dollar total for the fees, you can add another field to your values. Simply drag “Fee” to your values area.

Now, if you want the number of entries, rather than the total hours, you can right click on a value in the “Sum of Hours” column, and select “Summarize Values By > Count” rather than “Sum”.

Mixing and matching fields and filters in this way let’s you get a quick handle on a range of issues in your attorneys’ fees data.

---

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!

Then, simply navigate to the folder where your files and subfolders are stored, and Excel will automatically import file names, types, file path, and other metadata.