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 2016/365 you can also change a cell you want, let it autopopulate the rest of the column, then ctrl+z, this will undo autopoluate 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.

Answer 4

Adjusting AutoCorrect options is not optimal. You can do it on a col by col basis by:

1. let the column autopopulate your formula

2. delete any number of values in the autocalculated column (just one will do the trick).

3. adjust formulas in the column as you wish

Only able to test in Excel 2013.

Answer 5

May be a little late to open this query - but a couple of points:

• formula replication does not work if there are already different formula in the column - may need 3 different formula before Excel says it cannot guess which formula to replicate
• a solution could be to add 2 dummy rows at the top which will then prevent the replication - the benefit of this is that you are not disabling replication for any other tables that may be in the workbook; and I'm not sure if the setting may also inadvertently be copied to other new workbooks you create whilst you have the first workbook open
• finally, the old chestnut that =A1+B2 etc creates an error if A1 or B2 are not numeric; whereas =sum(A1,B2) works differently in that text will be ignored and effectively treated as zero

Answer 6

If your reference has letters in the middle these technics won't work. To add a zero before the reference in that case, create a new column next to it, add "0" to all the cells next to the references, add another column and use the function "concontenate" or whatever between the "0" cell and the one with the reference. Not only that, the =len function will correctly tell you the # of digits on references with letters in the middle (because excel does not view them as numbers per se, but rather text/general format) while on other references with 0s added before them with formatting this doesn't happen. Eg: 012345 =len gives 5 digits / 01A345 =len gives 6 digits