Create, edit, or remove a calculated column in an Excel table

In a Microsoft Office Excel table, you can quickly create a calculated column. A calculated column uses a single formula that adjusts for each row. It automatically expands to include additional rows so that the formula is immediately extended to those rows. You need to enter a formula only once. You don't 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.

What do you want to do?


Create a calculated column

  1. 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.

  1. 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.
  • 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 Button image on the Quick Access Toolbar. If you typed or copied a formula into a cell of a blank column, click Undo Button image twice.

Top of Page Top of Page

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 Button image 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.

Top of Page Top of Page

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.

Top of Page Top of Page

Delete a calculated column

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

Excel Ribbon Image

 Tip   You can also press DELETE.

Top of Page Top of Page

 
 
Applies to:
Excel 2007