Use a calculated column in an Excel table

When you enter a formula into an empty table column, the column automatically fills the rest of the rows in the column. Excel calls this a calculated column. Calculated columns can reduce errors that might be introduced by manual filling or by using copy and paste. In addition, calculated columns continue to adjust as you add or delete rows in the table.

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 it is necessary.

Do any of the following:

Create a calculated column

1. Click a cell in an empty table column that you want to convert into a calculated column.

   Tip  If it is necessary, insert a new column in the table. Select a table column to the right of where you want to insert a blank table column. On the Home tab, under Cells, click Insert. After you insert the column, then click on a cell in the empty column that you want to convert.

2. Type the formula that you want use.

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

Note  If you type or move a formula in a table column that already contains data, a calculated column is not automatically created. To overwrite data in the rest of the column, drag the fill handleThe lower-right corner of the selection. When you hold the pointer over the fill handle, the pointer changes to indicate that it is selected. You can copy contents to adjacent cells or fill in a series, such as dates, by dragging the fill handle. from the selected cell.

Change the formula in a calculated column

To update a calculated column, edit just one copy of the formula. The change propagates to all rows.

1. Select a cell in the calculated column.

2. Edit the formula, and then press RETURN.

	Notes
	If you change a formula in a calculated column that contains one or more exceptions—such as a formula that differs from the formula for the calculated column—Excel will not automatically update the calculated column. If you edit or copy more than one formula, the column will not be updated, but Excel notifies you of the inconsistencies with the exception marker so that you can resolve them.

Add exceptions to your calculated column

A calculated column can include formulas that differ from the column formula. Because Excel can't tell whether the calculated column should have more than one formula, Excel clearly marks most exceptions. In this manner, unintended inconsistencies can easily be detected and resolved.

The exception marker is a small green triangle in the upper-left column of the affected cell.

• Excel shows an exception when you:

  • Type data other than a formula in a calculated column cell.

  • Type a new formula in a calculated column that already contains one or more exceptions.

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

  • Type a formula in a calculated column cell, and then click Undo .

  When you delete a formula from one or more cells in the calculated column, Excel does not show an exception in your table.

Delete a calculated column

1. Select the calculated column.

2. On the Home tab, under Cells, click Delete.