# Use calculated columns in an Excel table

By default, a calculated column is automatically created in a Microsoft Excel table when you enter a formula in a table column. A calculated column uses a single formula that adjusts for each row and automatically expands to include additional rows in that column so that the formula is immediately extended to those rows. You need to enter a formula only once to have it automatically filled down to create a calculated column—there’s no need to use the **Fill** or **Copy** command.

You can enter additional formulas in a calculated column as exceptions, but Excel notifies you of any inconsistencies so that you can resolve them, if needed. You can also update the formula in a calculated column by editing the calculated column. If you don’t want formulas to automatically fill down to create calculated columns in a table, you can turn this table option off. When you no longer need a calculated column, you can delete it.

- Create a calculated column
- Include calculated column exceptions
- Edit a calculated column
- Stop creating calculated columns
- Delete a calculated column

## Create a calculated column

- Click a cell in a blank table column that you want to turn into a calculated column.

** Tip ** If needed, insert a new column in the table. Select a table column to the left of which you want to insert a blank table column. On the **Home** tab, in the **Cells** group, click the arrow next to **Insert**, and then click **Insert Table Columns to the Left**. You can also right-click a cell in the table column, click **Insert**, and then click **Table Columns to the Left**.

- Type the formula that you want to use.

The formula that you typed is automatically filled into all cells of the column — above as well as below the active cell.

** Notes **

- Copying or filling a formula into all cells of a blank table column also creates a calculated column.
- Formulas that reference other table cells will display structured references instead of regular cell references. For more information, see Using structured references with Excel tables.
- If you enter a formula in a column below the table, a calculated column is created, but the rows outside of the table cannot be used in a table reference.
- If you type or move a formula in a table column that already contains data, a calculated column is not automatically created. However, the
**AutoCorrect Options**button is displayed to provide you with the option to overwrite the data so that a calculated column can be created. You do not have this option if you copy a formula into a table column that already contains data. - You can quickly undo a calculated column. If you used the
**Fill**command or CTRL+ENTER to fill an entire column with the same formula, click**Undo**on the**Quick Access Toolbar**. If you typed or copied a formula into a cell of a blank column, click**Undo**twice.

## Include calculated column exceptions

A calculated column can include formulas that are different from the column formula, which creates an exception that will be clearly marked in the table. This way, inadvertent inconsistencies can easily be detected and resolved.

Calculated column exceptions are created when you do any of the following:

- Type data other than a formula in a calculated column cell.
- Type a formula in a calculated column cell, and then click
**Undo**on the**Quick Access Toolbar**. - Type a new formula in a calculated column that already contains one or more exceptions.
- Copy data into the calculated column that does not match the calculated column formula.

** Note ** If the copied data contains a formula, this formula will overwrite the data in the calculated column.

- Delete a formula from one or more cells in the calculated column.

** Note ** This exception is not marked.

- Move or delete a cell on another worksheet area that is referenced by one of the rows in a calculated column.

## Edit a calculated column

- In a calculated column that does not contain exceptions, do one of the following:
- Click any cell, and then edit the formula in that cell.
- Copy another formula into any cell of the calculated column.

** Notes **

- If you edit or copy more than one formula, the column will not be updated, but Excel notifies you of any inconsistencies so that you can resolve them.
- If you change a formula in a calculated column that contains exceptions, Excel cannot automatically update the calculated column. However, the
**AutoCorrect Options**button is displayed to provide you with the option to overwrite all formulas in that column with the modified formula so that a calculated column can be created.

## Stop creating calculated columns

The option to automatically fill formulas to create calculated columns in an Excel table is on by default. If you don’t want Excel to create calculated columns when you enter formulas in table columns, you can turn the option to fill formulas off. If you don’t want to turn the option off, but don’t always want to create calculated columns as you work in a table, you can stop calculated columns from being created automatically.

### Turn calculated columns on or off

- On the
**File**tab, click**Options**. - Click
**Proofing**. - Under
**AutoCorrect options**, click**AutoCorrect Options**. - Click the
**AutoFormat As You Type**tab. - Under
**Automatically as you work**, select or clear the**Fill formulas in tables to create calculated columns**check box to turn this option on or off.

** Tip ** You can also click the **AutoCorrect Options**

**Control AutoCorrect Options**, and then clear the

**Fill formulas in tables to create calculated columns**check box to turn this option off.

### Stop creating calculated columns automatically

- After entering the first formula in a table column, click the
**AutoCorrect Options**button that is displayed, and then click**Stop Automatically Creating Calculated Columns**.

## Delete a calculated column

- To delete a calculated column, select the calculated column.
- On the
**Home**tab, in the**Cells**group, click**Delete**.

** Tip ** You can also press DELETE.