excel tables
share TWEET PIN IT share share 0
Tech Tips

Harness the Power of Tables in Excel

By Danielle DavisRoe

Book cover Microsoft Excel for Legal Professionals

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, 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 in 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 in 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.

About Affinity Consulting Group

Affinity Consulting Group inspires, enables, and empowers legal teams of all sizes to work smarter, from anywhere. The company’s holistic approach incorporates people, process, and technology. Affinity’s passionate, well-connected industry experts work hand in hand with you to help you better understand and optimize your business — from software to growth strategy, and everything in between.

More Tips From Affinity Consulting Group:

Illustration ©iStockPhoto.com

Subscribe to Attorney at Work

Get really good ideas every day for your law practice: Subscribe to the Daily Dispatch (it’s free). Follow us on Twitter @attnyatwork.

share TWEET PIN IT share share
Danielle Danielle DavisRoe

Danielle DavisRoe is a senior consultant with Affinity Consulting Group (@affinitylegal). Whether it’s teaching clients a new skill through training, speaking at CLE events, or management consulting, Danielle is 100% focused on making the lives of her clients better. She has a Bachelor of Science in Business Administration from The Ohio State University Fisher College of Business and a Juris Doctorate from The Ohio State University Moritz College of Law.

More Posts By This Author
MUST READ Articles for Law Firms Click to expand

Welcome to Attorney at Work!

Sign up for our free newsletter.


All fields are required. By signing up, you are opting in to Attorney at Work's free practice tips newsletter and occasional emails with news and offers. By using this service, you indicate that you agree to our Terms and Conditions and have read and understand our Privacy Policy.