Formulas Not Copying Down in Excel – How to Fix!

As an Excel user, I know how frustrating it can be when formulas don’t copy down as expected.

From my experience, I learned that common issues like incorrect cell formatting, incorrect references, or Excel calculation settings might prevent formulas from copying down properly.

This tutorial examines seven reasons formulas may not copy down in Excel and offers possible solutions.

Reason #1: Workbook Calculation Mode is Set to Manual

Excel has two calculation modes: Automatic and Manual. 

Automatic calculation mode is Excel’s default setting, where Excel automatically recalculates all formulas in the workbook whenever a change is made to any referenced cell in the formula. 

However, you may get a workbook where the calculation mode has been set to Manual, meaning Excel will not automatically recalculate formulas. 

For example, consider the following dataset in a workbook where the calculation mode has been switched to Manual.

The dataset shows the descriptions, quantities, and prices of various electronic devices bought by a particular firm.

We want to use a formula to return the total prices of the items in column D. 

So, we proceed as follows:

1. Select cell D2 and enter the formula below:

=B2*C2

2. Drag or double-click the fill handle feature to copy the formula down the column.

Notice that the total price is the same in every row of column D of the dataset, which needs to be corrected. 

How to Fix 

We can do any of the following to fix the issue of formulas not copying down because the workbook is set to Manual calculation Mode:

• Switch the worksheet’s calculation mode to Automatic by selecting the Formulas tab, opening the Calculations Options drop-down on the Calculations group, and choosing Automatic.

• Calculate the entire workbook by pressing F9 or clicking Calculate Now button on the Calculation group of the Formulas tab.

Either of the above steps will copy the formula down the column and give us the correct total price in every row of column D of the dataset:

Also read: VLOOKUP Not Working – 7 Possible Reasons + Fix!

Reason #2: The Fill Handle is Disabled

The fill handle in Excel is the small green square located in the bottom right corner of a selected cell or cell range, as shown below:

If the fill handle is enabled, when we enter a formula in a cell and double-click or drag the fill handle, the formula is copied down the column. 

However, in some situations, the fill handle may be disabled such that we may not be able to drag the fill handle, or if we double-click it, we enter into edit mode instead of copying the formula down the column.

How to Fix

We enable the Fill Handle using the steps below:

1. Click File to open Excel’s Backstage window.

2. Click Options on the left sidebar of Excel’s Backstage window.

3. On the Excel Options dialog box, select Advanced on the left sidebar, select the Enable fill handle and cell drag-and-drop option on the right and click OK.

With the fill handle enabled, we can copy the formula down the column by dragging or double-clicking the fill handle. 

Also read: Excel Shortcuts Not Working - How to Fix?

Reason #3: There are Blank Cells in the Cell Range

Excel will stop copying down a formula when it encounters a blank cell in the range.

For example, look at the following dataset with an empty row 5.

When we enter a formula in cell D2….

….and double-click the fill handle to copy the formula down the column, we notice that Excel stops copying the formula in cell D4:

How to Fix

Ensure there are no blank cells in the relevant cell range. In this case, we delete the blank cell D5 by deleting the empty row 5:

• Right-click the numeric header of row 5 and select Delete option on the menu that appears.

With the blank cell removed, we can double-click the fill handle in the cell where Excel stopped copying the formula, and the formula is copied down the column:

Also read: Excel Showing Formula Instead Of Result

Reason #4: The Formula Contains Absolute References

Absolute references in Excel do not change when you copy a formula to another cell.

Therefore, a formula containing absolute references does not adjust to new cells when copied down. 

For example, the dataset below has a formula with absolute references in cell D2:

When we double-click or drag the fill handle to copy the formula down column D, we get the same total price in every cell, which needs to be corrected.

How to Fix

Change the absolute references in the formula to relative references by removing the dollar signs in the references, as shown below:

Note: You can either manually delete the dollar signs or select each cell reference in the formula and press F4 several times until all the dollar signs are removed.

Now when we drag or double-click the fill handle, the formula adjusts appropriately in every cell down the column, giving us the correct total price for each item:

Reason #5: The Worksheet or Particular Cells are Protected

A protected Excel worksheet is a worksheet that is locked to prevent users from making changes to particular aspects of the worksheet’s contents, structure, or formatting. 

If the worksheet or specific cells are protected, formulas may not copy down if the cells being copied to are locked.

For example, the following dataset is on a protected worksheet with only cell D2 containing the formula unlocked:

If we double-click or drag the fill handle tool to copy the formula down the column, the formula is not copied, and instead, Excel gives us a warning message indicating that the cells we are trying to change are on a protected sheet.

How to Fix

We must unprotect the worksheet to copy the formula down the column.

We use the following steps to unprotect the worksheet:

1. On the Review tab, click the Unprotect Sheet button on the Protect group.

2. If the worksheet’s author protected it with a password, Excel requests you to enter the password. Enter the password used by the author to protect the worksheet on the Unprotect Sheet dialog box and click OK.

The worksheet becomes unprotected. 

3. Drag or double-click the fill handle tool to copy the formula down the column.

The formula is copied down the column, but you notice an error indicator next to the cell range in column D.

When you hover the cursor over the indicator,  it displays the error message showing that the active cell has a formula and is not locked to protect it from being altered unknowingly. 

4. To remove the error indicator, click the down arrow on the error indicator and choose Ignore Error.

The error indicator is removed from every cell in the cell range:

Reason #6: The Fill Formulas in Tables AutoCorrect Option is Disabled

By default, when we enter a formula in a cell in an Excel table, the formula is automatically copied to the other cells down the column.

However, formulas are not automatically copied if the Fill Formulas in tables to create calculated columns AutoCorrect option is disabled.

Suppose we have the following Excel table showing the description, quantity, and price of various electronic gadgets bought by a particular company.

Let’s enter type the following formula into cell D2 to compute the total price:

=[@Qty]*[@[Price(USD) ]]

When we press Enter, we realize that the formula is not automatically copied down the column as expected:

How to Fix

To fix the issue of the formula not being automatically copied down the column, we must enable the fill formulas in tables AutoCorrect option using the following steps:

1. Click File to open Excel’s Backstage window. 

2. Click Options on the left sidebar of the Backstage window.

3. Click Proofing on the left sidebar of the Excel Options dialog box and click the AutoCorrect Options button on the right.

4. On the AutoCorrect dialog box, open the AutoFormat As You Type tab, select the Fill formulas in tables to create calculated columns option, and click OK.

5. Click OK on the Excel Options dialog box. 
6. Select the cell containing the formula on the Excel table and click at the end of the formula on the formula bar.

7. Press Enter, and the formula is automatically copied down the column.

Reason #7: There are Merged Cells in the Target Cell Range

If merged cells are in the cell range to which we want to copy a formula, they will prevent the formula from copying down as expected. 

Let’s consider the dataset below. First, notice that cells D8 and D9 are merged.

 We want to enter a formula in cell D2 that computes the total price of the first item on the list and copy the formula down the column to calculate the total prices of the electronic items. 

We use the steps below.

1. Select cell D2 and type in the formula below:

=B2*C2

2. Double-click or drag down the fill handle feature to copy the formula down the column.

Excel does not copy the formula down the column but instead displays a warning message indicating that all the merged cells must be the same size to copy the formula down the column.

How to Fix

We must unmerge the merged cells to fix the problem of the formula not copying down because of merged cells in the target cell range.  

We use the steps below to split the merged cells:

1. Select the merged cells D8 and D9.

2. On the Home tab, open the Merge & Center drop-down on the Alignment group and choose Unmerge Cells:

The merged cells are now unmerged:

Now, when we double-click the fill handle in cell D2, the formula copies down the column as expected:

Also read: Why is Merge and Center Grayed Out?

This tutorial explained seven possible reasons for formulas not copying down in Excel and offered solutions. We hope you found the tutorial helpful.

Other Excel articles you may also like:

• Excel Fill Series Not Working
• Excel Not Responding – How To Fix It!
• Formula Bar in Excel – Show/Hide Formula Bar!
• Excel Crashes When Opening File
• Not Enough Memory to Complete This Action in Excel – How to Fix?