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.
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
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
-
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
-
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.
-
Turn calculated columns on or off
-
On the File tab, click Options.
If you're using Excel 2007, click the Office button
-
Click Proofing.
-
Under AutoCorrect options, click AutoCorrect Options.
-
Click the AutoFormat As You Type tab.
-
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.
If you're using a Mac, goto Excel on the main menu, then Preferences > Formulas and Lists > Tables & Filters > Automatically fill formulas.
-
-
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.
You can also create custom calculated fields with PivotTables, where you create one formula and Excel then applies it to an entire column. Learn more about Calculating values in a PivotTable.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
