Excel 2016 Tips and Tricks

Excel 2016

Lesson – Working with Tables

C REATING A C ALCULATED C OLUMN  D ISCUSSION

A calculated column in a table contains a formula that performs the same calculation for each row in the table. When you enter a formula in any single cell in a column, Excel automatically applies the formula to all cells in the column.

A valid formula for a calculated column should only refer to data in the same table row as the cell in which it is entered. If the formula refers to data in other table rows, it will become an invalid formula in rows at either the top or bottom end of the table because it will refer to rows outside the table.

An exception to this rule is that the formula can refer to a cell outside the table which contains a constant, such as a tax rate, if the reference to the cell outside the table is an absolute reference.

If you create the formula by the pointing method (clicking on cells within the table, instead of typing cell references), Excel uses structured references, which creates a more robust and descriptive formula.

You can create a calculated column either by inserting a new column within a table and entering a formula in a cell in the column, or by typing a formula in any cell in the column immediately to the right of the table.  P ROCEDURES 1. Select any cell in the column you want to use as a calculated column. 2. Create the desired formula. 3. Press [Enter] to complete the formula. 4. Select the column header and enter a descriptive name to replace the default column name, if desired.  S TEP - BY -S TEP Create a calculated column.

OFFICEPRO, Inc.

Page 37