Formulas in Excel are not copied - a simple solution! (2023)

As an Excel user, I know how frustrating it can be when formulas don't reproduce as expected.

I have learned from my own experience that common problems such as incorrectly formatted cells, incorrect references or Excel calculation settings can prevent formulas from copying correctly.

This guide explores seven reasonsFormulas may not reproduce in Exceland provide possible solutions.

Cause #1: The workbook's calculation mode is set to manual

Excel has two calculation modes: automatic and manual.

Autocalculation mode is Excel's default setting, and Excel automatically recalculates all formulas in the workbook whenever any cell specified in the formula changes.

However, you can get a workbook withThe calculation mode is set to manual, which means that Excel will not automatically recalculate the formula.

For example, consider the following data set in a workbook where the calculation mode has been changed to manual.

This data set shows the description, quantity and price of various electronic devices purchased from a particular company.

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

Therefore, we proceed as follows:

1. Select cell D2 and enter the following formula:

=B2*C2

2. drag or double-clickFilling handleoperation forcopy the formula into the column.

Note that the total cost is the same for each row in column D of the data set and should be corrected.

how to fix it

We can do one of the following to resolve the problem that the formulas cannot be copied because the workbook is set to manual calculation mode:

• Change the calculation method of the worksheet toautomaticwith the optionofficiallycard, opencalculation capabilitiesCollapsible menucalculategroup and selectautomatic.

• Calculate the entire workbook by pressingF9or clickdo the math nowactive buttoncalculateclubofficiallyLet me underline.

Either of the above steps will copy the formula into the column and give us the exact total cost in each row of column D of the dataset:

Read also:VLOOKUP Not Working - 7 Possible Causes + Fixes!

Reason #2: The charging handle is disabled

thisFilling handleIn Excel, it's the small green square in the lower-right corner of the selected cell or range of cells, as shown in the image 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 will be copied into the column.

However, in some cases the fill handle may be disabled, so we may not be able to drag the fill handle, or double-clicking will go into edit mode instead of copying the formula to the column.

how to fix it

We activate the filling handles by following the following steps:

1. clickdocumentOpen ExcelBehind the sceneswindow.

2. clickchoicesIn the left sidebar of the background Excel window.

3. existsExcel optionsdialog box, selectAdvancedIn the left sidebar, selectEnable fill handles and drag and drop cellsoptions on the right and click OK.

When the fill handle is on, we can copy a formula to a column by dragging or double-clicking the fill handle.

Read also:Excel shortcut not working - how to fix it?

Cause 3: There are empty cells in the cell range

Excel stops copying formulas when it encounters an empty cell in a range.

For example, look at the empty data set in line 5 below.

When we enter the formula in cell D2...

...and by double-clicking the fill handle to copy the formula to the column, we'll notice that Excel stops copying the formula to cell D4:

how to fix it

make sure you don'tempty cellin the relevant cell range. In this case we subtractempty cellD5 removing blank line 5:

• Right-click the number header for line 5 and select Delete from the menu that appears.

together withdelete the empty cells, we can double-click the fill handle in the cell where Excel stops copying the formula, and the formula is copied below the column:

Reason 4: The formula contains absolute references

absolute referenceIn Excel, formulas do not change when copied to another cell.

Therefore, formulas containing absolute references will not be adjusted to the new cell when copied.

For example, the following dataset has a formula with an absolute reference to cell D2:

When we double-click or drag the fill handle to copy the formula to column D, we get the same total cost in each cell, which needs to be fixed.

how to fix it

Change absolute references to formulas to relative referencesremove the dollar signIn the reference material, as shown below:

notes:you can delete manuallyDollar symbolOr 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 easily adjusts to each cell below the column, giving us the exact total value for each item:

Cause 5: The worksheet or some cells are protected

A protected Excel worksheet is a worksheet that is locked to prevent users from changing certain aspects of the worksheet's content, structure, or formatting.

If the worksheet or specific cells are protected and the cell being copied to is locked, the formula may not be copied.

For example, the following data set is in a protected sheet with only cell D2 containing an unlocked formula:

If we double click or drag the fill tool to copy a formula below a column, the formula is not copied and instead Excel gives us a warning message saying that the cell we are trying to change is in a protected worksheet.

how to fix it

We had to unprotect the sheet to copy the formulas to the columns.

We remove the protection from the sheet by following the steps below:

1. existsreviewtab, clickRemove the protection from the sheetactive buttonProtectclub.

2. If the author of the worksheet has password-protected it, Excel will prompt you for the password. Enter the password the author uses to protect the sheetRemove the protection from the sheetdialog box and click OK.

The worksheet becomes unprotected.

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

The formula is copied into the column, but you'll notice an error next to the range of cells in column D.

When you hover over the indicator, an error message appears that saysliving cellsThere is a formula and it is not locked to prevent accidental change.

4. To remove the error indicator, click the down arrow on the error indicator and selectignore the error.

Remove the error flags from each cell in the range of cells:

Cause 6: The fill type autocorrect option is disabled in tables

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

but ifFill in the formula in the form Create calculated columnsThe autocorrect option is disabled.

Suppose we have the following Excel spreadsheet that shows the description, quantity and price of various electronic products purchased by a particular company.

Enter the following formula in cell D2 to calculate the total value:

=[@quantity]*[@[price(USD)]]

when wepress Enter, we realize that the formula is not automatically copied to the column as expected:

how to fix it

To solve the problem with formulas not automatically copied to columns, we need to enable the auto-correct option of the Fill type in the table using the following steps:

1. clickdocumentOpen ExcelBehind the sceneswindow.

2. clickchoicesIn the left sidebar of the Backstage window.

3. clickprovingIn the left sidebar of the Excel Options dialog box, clickAutocorrect optionsbutton on the right.

4. existsautomatic correctiondialog box, openAuto-format as you typecard, select itFill in the formulas in the tables to create the calculated columnsoption and click OK.

5. Click OK in the Excel Options dialog box.
6. Select the cell containing the formula in the Excel spreadsheet and click the end of the formula in the formula bar.

7. Press Enter and the formula is automatically copied into the column.

Cause 7: There are merged cells in the target cell range

If the merged cells are within the range of cells we want to copy the formula to, it will prevent the formula from being copied as expected.

Check out the dataset below. First, notice that cells D8 and D9 are joined.

We want to enter a formula in cell D2 that calculates the total cost of the first item in the list and copy that formula down the column to calculate the total cost of the electronics.

We use the following steps.

1. Select cell D2 and enter the following formula:

=B2*C2

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

Instead of copying the formula into the column, Excel displays a warning message indicating that all merged cells must be the same size for the formula to be copied into the column.

how to fix it

mustunattached cellsFixed an issue where merged cells in the target cell range caused the formula not to be copied.

Split the merged cells by following the steps below:

1. Select the merged cells D8 and D9.

2. existsDomcard, openMerging and centeringCollapsible menuI knowgroup and selectdivide the cells:

Connected cells arenot connected now:

Now, when we double-click the fill handle in cell D2, the formula is copied into the column as expected:

Read also:Why is the merge and center gray?

This guide explains seven possible reasons why formulas are not copied in Excel and offers solutions. We hope you find this guide useful.

Other Excel articles you may also like:

• Excel fill table not working
• Excel is not responding - how to fix it!
• Formula Bar in Excel - Show/Hide the Formula Bar!
• Excel crashes while opening a file
• There is not enough memory to complete this operation in Excel - how to fix it?