Micro How to… Format Data as a Table How

Step 1. Highlight the data table

Step 2. On the Ribbon, open the Insert tab, and select ‘Table’ (Shortcut CTRL + T).

Step 3. Hit Ok on the Create Table dialogue box.

You should now have a table that looks like the following:

Why should you format data as a table?

Data formatted as a table allows you to easily reference required data using simple named ranges for the table. It also lets you easily create pivot tables, graphs and access the data from advanced features like Power Query.

Easier Referencing

In the above example, the Revenue column is calculated using the price reference table on the right (in cells I1:J5). The formula in cell F2 is as follows:

=VLOOKUP(\$D2,\$I\$2:\$J\$5,2,FALSE)*E2

If we change the price reference to a table as well, as in the below example using the steps from above:

We can instead reference the data table itself, using named ranges automatically created by Excel. This makes the old VLOOKUP formula look like this instead:

=VLOOKUP([@ItemID],Table2,2,FALSE)*[@Quantity]

The @ sign tells Excel to look at the current row of the data table, so in plain English we are asking Excel to look for the Item ID from the current row in Table 2 (the price reference table), return the data from the 2nd column, and then multiply it by the quantity in the current row.

This makes it far easier to understand your formula, for yourself and others using your workbooks, and also means that if the data in the price reference table ever grows (e.g. you start selling more, different things that require new Item IDs to be added), the VLOOKUP will not need to be updated as it will always look at the entirety of the data, instead of the fixed reference of \$I\$2:\$J\$5 in the original formula.

Pivot Tables

Select any cell within the data table, go to the Insert tab in the Ribbon, and press the PivotTable button. Excel will automatically try to make a PivotTable for the entirety of the data table.