How do I prevent Excel from automatically replicating formulas in tables?

Question

I'm using Excel 2016. I have a table with headers and when I plug in a formula, Excel is automatically replicating the formula to all other cells in the column. While that would normally be fine, it's wrongly calculating the table headers. I thought I could just change the top row to exclude the header but Excel updates the rest of the column which I don't want.

I would like to either turn this automatic formula replication feature off or figure out a way to customize the formula in the top row so it doesn't calculate the header value.

Here's the formula I'm using and I didn't do anything special with the table outside of add a 'Totals' row:

=SUM(B2+C1-D2)

Answer 1

You can 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.

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.

Answer 2

In Excel 2016/365 you can also change a cell you want, let it auto-populate the rest of the column, then Ctrl+Z, this will undo auto-populate but keep the new formula/text you just changed in that one cell.

Answer 3

First, why do you wrap a simple formula into a SUM function? I always wonder why people do that when it's much shorter to write =B2+C1-D2 instead.

Second, if you used the true capabilities of SUM() then text, i.e. your column header, would be ignored instead of throwing an error. The + and - operators don't tolerate text, be it in a table or not. You could rewrite your formula to be

=Sum(B2,C1,D2*-1)

Third, be aware that cell referencing like that will behave erratically when you insert rows into the existing table (between existing rows). The row references will be off for anything below the inserted row and you will need to manually copy down the formula again to get correct results.

In order to get a formula that does not require adjusting, you may want to use structured referencing, where each row has exactly the same formula, instead of cell references, where row references are adjusted in each row. A possible formula for this would be (if your columns are labelled data1, data2 and data3 for columns B, C and D):

=SUM([@data1],OFFSET([@data2],-1,0),[@data3]*-1)

To get the data from the row above, Offset() is used on the cell in the current row (using the @ sign), with a negative row offset. Keep in mind that Offset is volatile, which may slow down very large datasets.