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.

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

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

  1. On the File tab, click Options.
  2. Click Proofing.
  3. Under AutoCorrect options, click AutoCorrect Options.
  4. Click the AutoFormat As You Type tab.
  5. 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

button that is displayed in the table column after you enter a formula. Click Control AutoCorrect Options, and then clear the Fill formulas in tables to create calculated columns check box to turn this option off.

Top of Page Top of Page

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.

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 2010