Friday, December 2, 2016

Use a calculated column in an Excel table

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 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 Cell error indicator 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.

Cell error indicator

  • 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 Undo button .

      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.

    Home tab, Cells group

See also

Create or delete an Excel table

Format an Excel table

Enter a formula

Use names in formulas

No comments:

Post a Comment