Benefits of Format as Table

In this post, I’ll go over some of the benefits of formatting a block of data as as a table. In Excel, it’s as easy as clicking the [Format as Table] button.

Recently, I’ve re-discovered this very useful feature of Excel.

Here is our raw data:

Raw Data

First thing we will do is select the data, and click the [Format as Table] button.

Our data transformed…

The first thing we notice is the look. You can choose from many different styles that can make readability a lot easier. You can choose from several themed colors, and alternating row colors.

The next feature is filtering. If you’re needing that, it comes pre-loaded with tables.

Another cool feature, when you scroll down, it changes the columns from A,B, C, etc. to the name of the column. See example:

Check the column names…

Another valuable feature is adding formulas to the table. All you have to do is add the formula along side the data and it is automatically copied down the entire table. This can be a big help when deal with data that is 1,000’s of rows.

Yes, I know I need make the G column wider to show my end balances.

In my example, I’ve added a “Net” column and “End Balance” column. I added formulas to the first row of data and it copied down. It gives you the lightning bolt icon to let you know it did it’s thing. You can also us the drop down on the icon to undo this feature.

Finally, if you plan to use your data for formulas, or pivot tables, the new table comes with a name that can be edited/changed in Name Manager. Usually, Excel just assigns “Table1”, “Table2”, etc.

Name Manager