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.
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.
Tip: 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.
For example, enter =sum(, then select the Qtr 1 and Qtr 2 columns. As a result, Excel builds 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.
Calculate values in a PivotTable
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.
