top of page

Excel AutoFill Anomaly

Today I experienced a problem with Excel that I had never encountered before. After using the Auto Fill feature (either by pressing CTRL + D or pulling the small handle at the bottom right edge of a cell) about a billion times to pull down formulas to below cells, suddenly today the formulas failed to indicate the values entered in succeeding referenced cells, and instead showed the value from the cell that was the original reference point. So in the example below, you see how in cell C5 the formula adding cells A5 and B5 doesn't give the result 476, but instead shows the value for the formula in C2, where I begin the selection before pressing CTRL + D.

For a while this problem drove me nuts. What the hell was I doing wrong? Did the cells need to be reformatted?

The solution to the problem was to go to File . . . Options . . . Formulas and look in the Calculations Options section. The anomaly arises when the Workbook Calculation is set to Manual, rather than Automatic. When it's reset back to Automatic . . .

. . . the spreadsheet should immediately update and the formulas will be displayed correctly.

bottom of page