Fix Excel Formula Not Calculating When Dragging Down


Fill Handle is a Microsoft Excel feature that lets you fill the adjacent cells with the relevant content. Be it dates or apply formulas to some data that was applied to the first cell, Fill Handle will apply that formula to all the selected cells. Fill Handle is represented by a small black box at the bottom right corner of a cell.

Sometimes, when using the Fill Handle and dragging across cells to fill them, the feature may not work. This can happen because of several reasons, namely the Fill Handle being disabled or formatting errors. Some solutions are discussed below.

One simple fix is to select all the filled cells in a column before dragging down and populating the cells below.

Check Formula

Make sure that you are not missing any parenthesis. If you are using some complex formula that contains more than one pair of parentheses, it is easy to lose count of the parenthesis and missing a closing parenthesis to an opening one may cause problems in calculations.

If the Fill Handle does not populate the selected cells, the problem may be in the formula. Ensure that you are not enclosing numbers in double quotes, or you do not have any decimal separators or special symbols. Also, if you are using nested functions, keep in mind that 2007 onward, Excel only supports up to 64 nested functions.

Enable the Fill Handler Feature

The Fill Handle discussed above may not be displayed if it is disabled. This feature can be enabled in Excel settings by following the steps below.

  • Open Microsoft Excel and select “File” in the top left corner.
  • Now, select “Options” and select the “Advanced” tab in the Options window.
  • Under the “Editing Options” heading, check the “Enable fill handle and cell drag-and-drop”. Then click “OK”.

Change Calculation Settings

Sometimes, when using Fill Handle to populate cells, the cells are filled but with the same content that was in the first cell without applying the formula. This can happen if the calculation settings are set to Manual.

  • Select the “Formulas” tab in the ribbon menu and click “Calculation Options”.
  • In the menu, select “Automatic”.

After changing the settings, check if the calculations are done properly now.

Change Auto Fill Options

If the cells are not being filled with the appropriate data, changing the Auto Fill settings to your liking may fix the problem.

  • Select a cell and while holding the left mouse button, drag down cells.
  • Now, click the small icon next to the fill handle to open a context menu where different Auto Fill options can be found.
  • Select the option you want, and the cells will be filled appropriately.

Hopefully, you were able to fix your problem by following this guide.