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.

Looking for Mac info?

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.

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

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.

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

  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.

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

  1. To delete a calculated column, select the calculated column.

  2. On the Home tab, in the Cells group, click Delete.

    The Cells group on the Home tab

    --OR--

    Press DELETE.

Top of Page

Excel automatically creates a calculated column when you enter a formula in an empty table column. When you use calculated columns, you’ll need to enter a formula only once and it automatically fills down the column—there’s no need to use the Fill or Copy command. In addition, a calculated column continues to adjust as you add or delete rows in the table so that the formula is immediately extended to those rows.

To create a calculated column in a table:

  1. Click a cell in an empty column that you want to use as the calculated column.

    Tip: If necessary, insert a new column in the table. Right-click the column to the right of where you want to add the blank column, and choose Insert Columns.

  2. Type the formula you want to use, and press ENTER.

    Note: If you type a formula in a cell that already has data, it doesn’t create a calculated column.

    In the below example, the SUM formula in cell D2 was automatically filled down all the cells in the calculated column D.

    Calculated column

Edit a calculated column

To update a calculated column, click any cell in the calculated column and edit the formula in that cell and press ENTER.

Delete a calculated column

Right-click the calculated column, and click Delete Columns.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×