Use calculated columns in an Excel table (2024)

Excel

Import and analyze data

Tables

Use calculated columns in an Excel table

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 More...Less

Calculated columns in Excel tables are a fantastic tool for entering formulas efficiently. They allow you to enter a single formula in one cell, and then that formula will automatically expand to the rest of the column by itself. There’s no need to use the Fill or Copy commands. This can be incredibly time saving, especially if you have a lot of rows. And the same thing happens when you change a formula; the change will also expand to the rest of the calculated column.

Note:The screen shots in this article were taken in Excel 2016. If you have a different version your view might be slightly different, but unless otherwise noted, the functionality is the same.

Create a calculated column

Create a table. If you're not familiar with Excel tables, you can learn more at: Overview of Excel tables.
Insert a new column into the table. You can do this by typing in the column immediately to the right of the table, and Excel will automatically extend the table for you. In this example, we created a new column by typing "Grand Total" into cell D1.
Tips:
- You can also add a table column from the Home tab. Just click on the arrow for Insert > Insert Table Columns to the Left.
Type the formula that you want to use, and press Enter.

In this case we entered =sum(, then selected the Qtr 1 and Qtr 2 columns. As a result, Excel built the formula: =SUM(Table1[@[Qtr 1]:[Qtr 2]]). This is called a structured reference formula, which is unique to Excel tables. The structured reference format is what allows the table to use the same formula for each row. A regular Excel formula for this would be =SUM(B2:C2), which you would then need to copy or fill down to the rest of the cells in your column

To learn more about structured references, see: Using structured references with Excel tables.
When you press Enter, the formula is automatically filled into all cells of the column— above as well as below the cell where you entered the formula. The formula is the same for each row, but since it's a structured reference, Excel knows internally which row is which.

See Also
Types of calculations you can perform in formulas What are the Different Tableau Products?Calculation operators and precedence in Excel Math Equation |Types of Equations with Solution

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 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.
If you input a new formula that is different from existing formulas in a calculated column, the column will automatically update with the new formula. You can choose to undo the update, and only keep the single new formula from the AutoCorrect Options button. This is generally not recommended though, because it can prevent your column from automatically updating in the future, since it won't know which formula to extend when new rows are added.
If you typed or copied a formula into a cell of a blank column and don't want to keep the new calculated column, click Undo twice. You can also press Ctrl + Z twice

A calculated column can include a cell that has a different formula from the rest. This creates an exception that will be clearly marked in the table. This way, inadvertent inconsistencies can easily be detected and resolved.

Note: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 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.

Error notification will only appear if you have the background error checking option enabled. If you don't see the error, then go to File > Options > Formulas > make sure the Enable background error checking box is checked.

If you're using Excel 2007, click the Office button , then Excel Options > Formulas.
If you're using a Mac, go to Excel on the menu bar, and then click Preferences > Formulas & Lists > Error Checking.

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.