Why Excel Tables are a Must-Have Tool for Data Management

Ever wondered how to make data management easier? Excel tables are a perfect start. They simplify formatting and calculations. Imagine transforming your data into a more functional spreadsheet. Learn how to make an Excel table in 4 clicks. (Includes practice file.)

What’s an Excel Table?

I think part of the confusion is the word “table,” as people figure that the native state of the spreadsheet is a table. However, Excel has a “table” feature that expedites formatting, filtering, and calculations. Moreover, it’s probably one of the product’s easier features to master. Let’s start by showing a Before and After picture.

Normal Excel spreadsheet before converting to table.
Before – Normal Excel spreadsheet

As you can see, it’s a collection of stats about US presidents. Not terribly exciting, but it works well for illustration. Now, here’s the same info as an Excel table. This table conversion was done in 4 clicks.

Excel table with highlighted features.
After – Excel Table example

Excel Table Differences

If you compare the two screen snaps, there are 3 immediate differences.

  • The Excel table data is formatted in alternating colored banded rows for easier reading.
  • The Excel table top row is locked, so the column heading stays in place as you scroll down.
  • The Excel table columns are filterable.

How to Create Excel Tables

As I mentioned, producing that table took only 4 quick clicks. If you’re into Excel macros, you can do it with Ctrl + T.

  1. Click in any populated cell.
click Excel cell
  1. Click the Insert tab.
click Excel insert tab
  1. Click Table.
Insert Excel Table
  1. Click OK in the Create Table dialog box. Notice how the range is automatically entered.
Create Table dialog

If you don’t like the color combination, you can check out the options in Table Styles.

Easier Calculations and Formulas

Apart from the usability features, an Excel Table makes math easier. To start, you can add something called a Total Row by checking the box in the Table Style Options panel.

Add Total Row
Table Total Row option

The nice part about this row is it isn’t limited to Totals. There are a number of built-in math functions. For example, after adding the row, I can do the additional math on the columns, such as determining the average inauguration age. You’ll see a list of available math options if you click the triangle.

table total row options
Performing Average on a column

Meaningful Column References

Excel Tables also make formulas easier by using your column names instead of cell references. For example, I can insert a new column called “% Electoral Vote.” Instead of referencing Electoral Votes as column F, Excel shows the column name.

All I had to do was type =[ to produce the list. From there, I can click to select the other column I want and then add the closing ]. Essentially, you add opening and closing brackets to identify the columns. You can then add more variables and operators.

table column names dropdown
Column Name List

Excel Tables & Slicers

Another advantage to tables is you can create Excel slicers. Slicers only work with Excel Tables and Excel Pivot Tables. A slicer gives you another way to drill down into your data. You can create one from any column within your table. The user clicks on one or more slicers to get their data. As you click one button, other slicer buttons may enable. For example, if I clicked California, the Actor button under Occupation would enable, and I’d see Ronald Reagan added.

Excel table with slicers off to the side.
Slicers added to an Excel table

I’ve attached the presidents’ spreadsheet if you want to try out the Excel Table feature.

Additional Resources