For instance, if he has the formula =SUM(B: B) and then he inserts a column to the left of column B, the formula is automatically updated by Excel to =SUM(C: C).He doesn't want the formula to update; he still wants it to refer to column B after he inserts the new column.

You'll be able to ask any tech support questions, or chat with the community and help others.

into Cell B2 and then drag Fill Handle down to other cells, the references are incrementing as below screenshots shown.

In general, when you use a formula in a worksheet, for instance, in Cell C1, you use this formula =A1 B2, and the formula will automatically calculate with the change of data in A1 and B2.

Now if you want to stop the auto calculation, I can introduce two trick ways for you.

Any idea to prevent cell reference in a formula from incrementing/changing when applying Auto Fill in Excel? Prevent cell reference from incrementing with changing to absolute reference Prevent multiple cell references from incrementing with Kutools for Excel, and then drag the Fill Handle down to other cells.

Note: You can click on the reference cell in the Formula Bar, and then press the F4 key to add $ signs to this reference cell in the formula. In the opening Convert Formula References dialog box, check the To absolute option, and click the Ok button.One way to get the result you want is to use the OFFSET function to refer to column B.For instance, consider the following formula: If this formula is in a cell, and you insert a column before column B, then the formula doesn't update; it still refers to column B. Because the formula refers to column A and you didn't do anything to move column A.I can only assume that somehow the file had become corrupted.I also have had this problem with a very large spreadsheet that just stopped updating itself over the weekend, but having checked the above solutions, setting were already set to automatic, and sheet is too big to rebuild, so I was at a loss.This tip (3392) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Comments are closed.