Last year I recorded a short video about the “Fill Handle” (the little black cross that allows you to quickly copy or autofill) in Excel not working. If you haven’t seen the video, check it out here.

I received a query this week about the video – “The autofill just copies the content of the cell, not the formula”. In other words:

autofill_copies_values_not_formulas1

Point the mouse at the fill handle at the bottom right hand corner of C2 and double click. Excel should copy the formula to C3:C6. In fact Excel does copy the formula down but all cells appear to have the same value:

autofill_copies_values_not_formulas2

The reason this happened was because Excel was set to Manual Recalculation. To solve the problem, select any cell in the worksheet and press F9. Immediately, every formula in the file is updated.

Switching Manual Recalculation back to Automatic is done via the Excel options and is done differently depending on the version of Excel.

Excel 2013: File > Options > Formulas section

Excel 2010: File > Options > Formulas section

Excel 2007: Office Button > Options > Formulas section

Excel 2011: Excel >Preferences  > Calculation section