Formatting as a table

By Neil Shorney, Director, Naturally Sales Ltd.

In Excel level 1, we learn about using the Format as table option to improve the look of data. But did you know that this can also be a time-saver?

Tables provide a quick-and-easy way to total lists, add rows, filter and affect the look-and-feel of your data. To create a table from existing data, highlight your data and choose "Format as table" from the Home tab, or "Insert table" from the Insert tab. As you create the table, tick the "My table has headers" box to use the top line as column headings. You'll see that the look of your data will change and a contextual menu Design tab will appear on the ribbon. In here you have a number of options, for example, adding a quick "Total" row at the bottom, changing the format of the first or last column to bold, removing duplicates, and other useful tools. In the "Table styles" box, you can quickly change the colours of the table. You'll also notice that Autofilter arrows will appear at the top of each column.

One of the most useful aspects is that if you have a column of formulae in your table, then adding a row in the middle will automatically copy those formulae to the new row, saving time copying and pasting.

Should you at some point decide that a table is not the best format for your data, the "Convert to range" button in the "Tools" box will turn it back to a normal data range.