Use formulas in an Excel table

After you create an Excel table, you’ll want to know how to work with its calculated columns and formulas.

When you enter a formula in a table column, Excel creates a calculated column. This column uses a single formula that’s automatically extended to additional rows in the column and adjusted for each row. You just enter a formula once, and Excel immediately fills it down to create the calculated column—there’s no need to use the Fill or Copy command.

Here’s how you enter a formula to create a calculated column:

  1. Click the first cell in a blank table column where you want to start a calculated column.

    To insert a new blank column in the table, right-click a cell in the table column to the right of which you want to insert the column, and click Insert > Table Columns to the Left.

  2. Type the formula you want and press Enter.

    The formula is automatically filled into all cells of the column—above as well as below the active cell.

    Tip:  If you select cells in the table when you create the formula, you’ll notice they may be shown as structured references (predefined table and column names) instead of regular cell references in your formula. For more information about that, see Use structured references in Excel table formulas.

    Notes: 

    • Copying or filling a formula into all cells of a blank table column also creates a calculated column.

    • If you type or move a formula in a table column that already has data, the AutoCorrect Options button appears so you can choose to overwrite the data to create a calculated column. This option isn’t available if you copy a formula into a table column that already has data.

Include calculated column exceptions

When you include formulas that are different from the calculated column formula, Excel creates an exception that’s clearly marked with a green indicator in the upper-left corner of the cell. This way, you can find and resolve inadvertent inconsistencies.

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 doesn’t match the calculated column formula.

  • Delete a formula from one or more cells in the calculated column. This exception isn’t marked.

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

Edit the calculated column formula

To update the formula in a calculated column that doesn’t have exceptions, you can edit any formula in that column. Here’s how:

  • Just click any cell in the calculated column and change the formula.

You can also copy another formula into any cell of the calculated column.

Notes: 

  • If you edit or copy more than one formula, the calculated column won’t be updated, but Excel notifies you of any inconsistencies so you can resolve them.

  • If you change a formula in a calculated column that has exceptions, Excel can’t automatically update the calculated column. But you can click the AutoCorrect Options button that appears and choose to overwrite all formulas in that column with the updated formula, which automatically creates a new calculated column.

Stop creating calculated columns

To stop a calculated column from being created automatically after you enter the first formula:

  • Click the AutoCorrect Options button that appears on the next row, and pick Stop Automatically Creating Calculated Columns.

Stop automatically creating calculated columns

If you don’t want Excel to create calculated columns when you enter formulas in table columns, pick Control Auto Correct Options and clear the Fill formulas in tables to create calculated columns box under Automatically as you work,.

If you no longer need a calculated column, you can simply delete it. Right-click the column and pick Delete > Table Columns.

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!

×