Making the most of the Power of Tables in Excel

By Danielle DavisRoe

One of the ways that lawyers use to organize and make sense of financial data is through Excel.

Filled with columns and rows, an Excel worksheet looks like a giant table. However, an actual Excel table comes with many benefits that make working with your data a lot easier. Here are a few quick table tips, adapted from Affinity Consulting Group’s “Microsoft Excel for Legal Professionals.”

Advantages of Excel Tables

Tables make it easier to work with large data sets arranged in rows and columns. The advantages include:

• Automatic header and total rows. You can automatically include header and total rows with the check of a box.
• Automatic expansion. Tables automatically expand as you add new rows and columns. Formatting and formulas automatically carry over the new rows and columns.
• Formula replication. Formulas automatically replicate to new rows and blank cells in the same column, eliminating the need to copy and paste formulas or use autofill.

• Table styles. Table styles allow you to quickly format the entire table, including banded rows that automatically update as rows are added or deleted.
• Sorting and filtering. The header row automatically includes controls to sort and filter the data.
• Removal of duplicate data. Tables include tools to remove duplicate data.
• Structured referencing. Tables and columns in tables are automatically given names, allowing you to create formulas that are easier to read.

Creating Excel Tables

Tables work best when each column of data has a header above it. Most often, this means that row 1 serves as a column header. To convert your existing data to a table, start by selecting the data. On the Insert ribbon, in the Table group, click on the table.

If the first row of the data you selected included column headers, check the box for “My table has headers.” Click OK.

Your data will be converted into a table and will likely be formatted with blue, banded rows.

Total Rows

Total rows allow you to quickly write formulas based on numerical data in a given column. You can total the data, average the data, find the maximum number, and find the minimum number or more.

To enable the total row, click inside the table. On the Table Design ribbon, in the Table Style Options group, check the box for Total Row. In the new row that appears at the bottom of your table, click on the column in which you want to do a calculation and select the desired formula, most often a sum (total).

Table Styles

Table styles determine how your table looks and make banded rows incredibly simple. If your table was created with blue, banded rows and you’d prefer a different look, click inside the table. On the Table Design ribbon, click on the drop-down arrow to expand the Table Styles gallery. Excel comes with a wide variety of styles to select from.

From the Table Styles gallery drop-down, select New Table Style to create your own style. You can then edit each table element, selecting different colors and other formatting attributes.

Click inside the table to turn banded rows or columns on or off. On the Table Design ribbon, in the Table Style Options group, check or uncheck the Banded Rows and Banded Columns boxes.

Sorting and Filtering

Tables come with automatic sorting and filtering. To sort or filter the content of your table, click on the drop-down arrow in the column header.

To filter, uncheck the values you want to hide.

If you want to sort, select one of the two sort options at the top of the list.

Removing Duplicate Data

To remove duplicate rows from your table, click on the table. On the Table Design ribbon, in the Tools group, click on Remove Duplicates. Excel will examine one or more columns for duplicative values. If you have a name column and want to remove all duplicate names, check the box next to the name column.

If, however, you only want to remove duplicates that have the same name and email address, check the boxes for both name and email address. Any rows with a duplicate name but a different email address will remain. Likewise, any rows with duplicate email addresses but different names will remain.

Credit: Attorneyatwork.com