News & Updates: Data in Practice: Quick Summaries with a Total Row

Data in Practice: Quick Summaries with a Total Row

Posted by Marie Jonas

Now that you have formatted your client’s data set, it’s time to run a few summaries. Taking advantage of table formatting unlocks several options for you.

(Remember, sample data can be accessed here.)

When a data set is formatted as a table, if you select a cell in that table, you will view a “Table Design” option on your top ribbon.

This lets you control several features, such as Table Style Options and Styles, including inserting a Total Row. Selecting that option inserts a row at the bottom of your table, where you can select a variety of functions.

Sum

In our sample data, the default when we select our Total Row is to “Sum” the hours to list their total.

This adds all of numbers in the select column of the rows shown.

Note, if you then filter your table, the total row will display the sum of only those rows shown.

Count

Let’s say you want to simply count how many cells are filled – to get a sense of the volume of entries shown in your table. Simply navigate to any column in your total row, and select the drop-down for “count.”

Here, you can see our data set starts with 50 entries (this excludes the header). Note, if a cell is empty for some reason, it won’t be counted. The count here (technically, “COUNTA” for Excel pros) looks for how many cells in the specific range have content.

Let’s say we want to know the number of entries that exceed 8 hours. We can then filter our “Total Hours” column to just those over 8.

The Count of our filtered total shows us that there are 27 entries with over 8 hours.

Max and Min

If you need to quickly know the smallest or largest number in a set, you can access that too.

Here, with time entry data, perhaps you need to know the latest an employee arrived at work, or the earliest they departed. With time formatting, Excel enables you to display the earliest and latest times using the “Max” and “Min” functions available in the Total Row.

The latest someone arrived during these two days was 8:59 am.

The earliest someone departed was 5:04 pm.

Basic functions using the Table format will provide quick answers to some basic questions whenever you dive into a new data set.

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!

Do you ever have a matter with 5 different critical worksheets, all of which live in separate files? When I work with data, I love to have all the material I need in one place: first, it serves as a backup (so I don’t risk messing up an original data source); second, I don’t need to continually reference multiple files; finally, I can set up functions to refer to data in on central place – and not risk breaking links. But cutting-and-pasting is time consuming and risks damaging your formatting. The solution? Right-click on the sheet tab you need to work with (where it says the name of your sheet, e.g., “Sheet1”); click on “Move or Copy…”, and check the box to “Create a copy”. Now, you can select a new or existing file (book) for your working set of data to live. Repeat for your other relevant sheets – moving all of them to the same book!

Note, this only works with “static” data (a fixed data set), not dynamic (“living” data). But as lawyers, static data is often what we are working with.

Data permeates every aspect of legal practice. Data in Practice is a bimonthly feature to provide practical tools for attorneys to better organize, manipulate, and understand data. Whether it’s working with basic case information, preparing document productions, or conducting exposure analyses, a more robust knowledge of Excel is guaranteed to streamline your work. A few simple tools can help attorneys more efficiently and effectively represent their clients, and better navigate a professional landscape inundated with big data.

Marie Jonas is a Partner in Folger Levin’s litigation practice group. Marie has over a decade of hands-on experience working with Excel in all aspects of her practice: ranging from investigations to trial. If you have an idea for a topic involving practical data tips for lawyers, she can be reached at mjonas@folgerlevin.com.