Hello and welcome back Excellers.  Today I want to show you how to achieve a fast formula fill by turning your data into an Excel table.  If you want to make managing your data really easy, then I would recommend turning your range of cells on your Excel workbook to an Excel Table.

Why Excel Data Tables?

Excel Tables really help you filter, sort, organise and make your data easier to view.  They also have an ideal structure for the source data of an Excel Pivot Table.

If your data set is contained within a Table then the table rows and columns can be sorted independently from the data in other rows and columns on the worksheet, which I find really handy. Not only that, when it comes to writing and applying formulas the data Table will help you update your cells quick as a flash. Let’s see how.

Create A Data Table.

First of all let’s convert a range of data to a Table.

• Click any cell in your data set
• Insert Tab – Tables Group – Table
• Excel automatically will select your data set
• Confirm your Table has Headers
• Hit Ok

• Your Excel Table is created. Nice

Now, the table is created to see how fast it is to create a formula and apply to all of the rows of data. First of all, let’s add another column to our table to calculate the average selling price per unit.

• Click on the Units Sold Column as we want to add a new column to the right of this one
• Home Tab – Cell Group – Cell
• Insert Table  Columns To The Right
• Name this column Average Selling Price

Now it’s time for formula magic!.

Formulas In An Excel Table.

Type your formula as normal into the first cell you want it calculated by. Do this by clicking on the cells you want to include in the formula. As you can see, Excel will automatically assign a name to the Table. Names are also assigned to the cell columns. These can be seen in the first row of the Table- @Units Sold and @Value.

These are referred to as structured references. The major benefit of this is that formulas are much easier to type and read. This is because they refer to descriptive attributes of the table (table and column names).

To include structured references in your formula, click the Excel table cells you want to reference instead of typing their cell reference in the formulas.

If you enter explicit cell references in a calculated column, it can be harder to see what the formula is calculating and if, for example, you add a column between the existing columns C and D, you’d have to revise your formula.

So structured references are useful.

Once you have entered the formula hit enter or return.  Excel immediately calculates the whole column of data for you.  Boom!.

How fast was that?. Particularly useful when dealing with very large data sets.