The Table feature in Excel (not to be confused with PivotTables or Data Tables!) is a great tool for organising and analysing large amounts of data. For this reason, like PivotTables, they are sometimes not used in pure financial modelling, but as a very useful tool in reporting and analysis, they are also worth a brief mention. See this article for more information about the use of Tables in Financial Modelling.
The two useful features of a Table are:
- A Table will automatically resize depending on the number of rows. If you have a formula or a PivotTable referencing the Table, additional data will automatically be included in the range.
- Formulas created within a Table are automatically populated, hence ensuring consistency in formulas for blocks of data – a fundamental point of financial modelling best practice.
TO CREATE A TABLE
- Organise your data so that it is arranged in a block, with headings on each column as shown in Figure 1. You can create this sheet yourself, or download the template here.
- Click on any cell within the sheet, and select Table from the Insert tab, or use the shortcut Control-T. In Excel 2011 for Mac, click on the Table tab, then click the New icon in the Table Options section of the ribbon.
- This will bring up the Create Table dialog box and if your data is arranged correctly with headings on each column, you can leave the tick box “My table has headers” selected, and press OK.
- When the table has been created, it will format the table using alternate fill colour. You can easily change the colours from the Table Styles section on the Table Tools Design tab.
- Note that you can also change the name of the table in the Table Name box in the far right of the Design tab. In Excel 2011 for Mac, click on Rename on the tables ribbon. (The Table Name box has been highlighted in Figure 1). This is similar to creating a named range, and makes your table easier to refer to in formulas and PivotTables.
- Now try typing in an extra heading such as “GST” in cell E1. You’ll see that the table automatically expands to include the additional column. If you add a formula in cell E2, you’ll see that the formula is automatically copied down the entire column in the table.
More information on Using Tables in Financial Modelling
Structured Reference Tables are covered in the Data Analysis & Dashboard Reporting in Excel course which runs as a live workshop in Sydney, Melbourne and Brisbane.
