One of my favorite quotes comes from Yogi Berra who said “You can observe a lot by just watching”. In this case, I was watching someone work with Excel and doing various tips to make the spreadsheet more usable. In fact, I’ve written about many of those tips. I suggested to my co-worker that he could reduce steps by using Excel Tables. And no, I’m not talking about pivot tables but something simpler and equally powerful.
I think part of the confusion is the word “tables” as people figure that the native state of the spreadsheet is a table. However, Excel has a feature called “table” 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.
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 was done in 4 clicks.
If you compare the two screen snaps, there are 3 immediate differences.
- The data is formatted in colored bands for easier reading.
- The top row is locked so as you scroll down, the column heading stays in place.
- The columns are filterable.
How to Create Excel Tables
As I mentioned, it took only 4 quick clicks to produce that table.
- Click in any populated cell.
- Click the Insert tab.
- Click Table
- Click OK in the Create Table dialog. Notice how the range is automatically entered.
If you don’t like the color combination, you can check out the options in the Table Styles.
Easier Calculations and Formulas
Apart from the usability features, Excel Tables make math easier. To start, you can add something called a Total Row by checking the box in the Table Style Options panel.
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 additional math on the columns such as determining the average inauguration age. If you click the triangle, you’ll see a list of math options.
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.
If you want to try out this feature, I’ve attached the presidents spreadsheet.