An often underutilised option in Microsoft Excel is format as table. However, it can have a significant positive impact on your productivity when working with data stored in a table layout.
It can be accessed from the Format as Table option on the Home tab or from the table icon on the Insert tab. If the table has no blank rows or columns in it then you only need to click in one cell in the range of data before choosing one of these options. Otherwise the whole set of data must be selected.
Top 3 reasons for using format as table
1. Apply preset styles
Formatting can be applied using preset styles really quickly. Particularly useful is the opportunity to shade the rows in alternating colours. If you have ever done this manually it can be a tedious and time-consuming task.
2. Automatically expanding range
The range of data automatically expands when you add extra rows or columns of data. This is especially helpful in calculations where you don’t want to have to make manually adjustments to the formulas if the length or width of data changes.
When calculations are created the ranges of cells used are not recorded using cell references but instead using column, row or table names. For example, below, [heading 2] refers to all the cells in the heading 2 column apart from the heading itself. Because this is not fixed to specific cells it expands or contracts in line with the data. Different conventions apply to data including the headings or the whole table.
e.g. =SUBTOTAL(109,[heading 2])
Autofilters are automatically added to the headings providing a speedy way to sort or filter the required data.
This is now my go to default for any tables I create before doing anything else due to the advantages it provides going forward.