Use Tables to manage data more efficiently

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Tables are a new feature of Excel 2007 that allow you to group together a block of data (as a table) and then reference it dynamically. Since almost all data in Excel is essentially laid out in tabular format (rows and columns), tables can and should be used frequently as they allow you to work with the data in a more flexible and meaningful way. They provide most of the benefits of named ranges and dynamic named ranges (which flex according to the amount of data) without you having to set these up separately.

Grouping data together as a table has the following benefits:

• The data range is dynamic – as you add more rows/columns the table expands and any formula columns extend down automatically to your new range. Any Pivot tables/charts etc based on this data also will automatically include the new data
• Formulas within the table (or to table totals) are structured references by default – they refer to the name of the column the data sits in, making the formulas more intuitive (in the same way as named ranges
• You can have a total row at the bottom of the table which will always be at the bottom of the dataset and will include all the relevant data even when rows are added/deleted.
• The data contained in the table is easier to view, filter and sort – for example with large data sets, scrolling down automatically keeps the headers visible within the table.

To create a table, select the data you want to turn into a table and then go to Insert -> Table (or press Ctrl + T) and confirm the range.

The data you selected will then have blue shading (by default). You can change the look of the table by clicking on the new ‘Table Tools’ ribbon which appears at the top when you are in a table. From here you can choose one of the Table Styles that appear, or alternatively create your own by picking an existing one as a base, right clicking on it, going to ‘Duplicate’ and then separately choosing the format for each element you require to be different.

In Table Options you can also set some of the other options for the table – the main basic ones being to give it a more meaningful name in the ‘Table Name’ box, and selecting ‘Total Row’ in the ‘Table Style Options’ tab if you want to have a total row at the bottom of your dataset.

In the Topic Example Workbook, in the ‘Tables’ sheet, there are formulas in Columns E and F. Once you have converted the data range to a table, try adding another salesperson name in Column A at the bottom of the table (if you don’t have a total row you can type a value into cell A10; if you do have a data row then insert a row before row 10 first. Note that if you copy multiple values and paste into cell A10 it will automatically insert the rows for you).

You will see that when a new data row is added, the table automatically extends downwards and any formulas in Column E and F are automatically extended down to the new row(s) so no longer is there any need to drag formulas down to your required data range! By their very structure, the formula in every row is by default the same which minimises the opportunity for hard-coded adjustments on particular rows (a bad practice which may lead to spreadsheet errors!).

The dynamic nature of tables is also beneficial when you are creating a graph or pivot table from the data. If the data source for a graph or pivot table is set as a normal cell range reference then if new data is added outside of that range then the pivot table or graph won’ take account of this new data. There are several ways to get around this: highlight entire columns as the data range (though this might not work if there are multiple datasets on the same worksheet!), use a dynamic named range, or alternatively use tables. With tables, when new rows or columns are added to the table these are automatically included in any reference to the table as a data source range.

Creating a Pivot table from your table can be done directly from the new table ribbon – in table tools click ‘Summarise with Pivot tables’. Alternatively when creating a Pivot table normally, put the table name as the ‘Data Source’.

Another benefit of Pivot Tables is that when scrolling down through the data within the table, once the row containing the headers are no longer visible the Column headings will show instead of the column Grid reference (A, B, C etc).

In the example workbook, if we delete the existing formula in cell F2 (=D2*E2) and then try to re-write it you will notice that Excel will now write the formula as =[@[Quantity Sold]]*[@[Price Per Product]] in Excel 2010 and as a slightly longer reference to the current row in Excel 2007.

This is because within the table, Excel refers to cells by its row/column heading rather than as a cell reference. This is called structured references and, like named ranges, they make the formulas easier to understand – =[@[Quantity Sold]]*[@[Price Per Product]] is more meaningful than =D2*E2. So if you and others who use your workbooks can get used to interpreting formulas written in this way it will make them more intuitive.

Initially at least this can be annoying though so if you want you can turn this off in Excel Options (Ribbon -> Excel Options -> Formulas -> Working with formulas -> uncheck ‘Use table names in formulas’).

In the Topic Example Workbook:

• Convert the range A1:F9 on the ‘Tables’ worksheet into a table
• Set the table Options – give it a more meaningful name (e.g. tblSales), and Change the format/options (such as adding a total row) if you wish
• Add new salespeople to the table at the bottom – ensure that the formulas in Columns E and F automatically extend to the new rows
• Delete the formula in cell F2 and re-write it as D2 * E2 with Excel writing it as a structured reference formula

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

• The table functionality doesn’t apply properly if the worksheet is protected. (You can’t define protection options for part of a table) so use alternatives such as Dynamic named ranges for worksheets which are protected
• You also can’t use Custom views where there are tables being used anywhere in the workbook
• Structured Reference formulas don’t allow for absolute/relative references so if you require these then switch the automatic structured reference formula’ option in Excel Options.