Excel Table Features

Have you ever created a Dynamic Named Range in Excel? It was dynamic because the size of the range would increase if you added more data rows or columns. It was based on a clever use of the OFFSET function. That was a trick for advanced users but it is now a routine feature of Tables in Excel. 

A table is based on a set of data in columns, where columns have labels but rows do not. Rows contain the data that is identified in the column headers. 

To create a Table, select a cell in your data set and use the Table button on the Insert menu to turn it into a table. The appearance of your data set will change. The header cells will be shaded and they will now have dropdown filter lists. The data rows will be alternately shaded light and dark. Whenever a cell in the table is selected the Design menu will be available to allow you to change the format and other features.

Among the features is the ability to add a Total row at the bottom of the table. Various functions such as Average, Count, Max and Min are available.  

Now that you have a table, when you need to add data, just key in a new row at the bottom. The new data will automatically be incorporated into the table. Adding a column is just as easy. Just go to the first blank column to the right of your table in put in a new header name and data. It will immediately pick up the formatting of the existing table. You can also insert a new column somewhere in the middle of the existing table.

You may want to do calculations on data in the table. If you add a column and enter a calculation in the first data row, it will be copied to the entire column for you automatically. 

Now that you’ve built a table and added calculations, how can you use it? You’ll probably want to pull selected data from the table for reference elsewhere. That’s where structured references come in. This allows you to pull data from the table with a formula that uses the table name and column name, rather than cell positions. The use of structured references makes your formulas very readable. 

You may find the features of Excel Tables to be very helpful in your analysis work. Take some time to get familiar with them.