Micro How to… Format Data as a Table

How

Example Data, Unformatted

Step 1. Highlight the data table

Data Table Highlighted

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

Insert Table Button

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

Create Table Dialogue Box

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

Data Formatted as a Table

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:

Price reference data formatted as a table

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.

Create PivotTables easily

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s