Copy Formulas in Excel to a New Sheet Without Errors

To copy formulas to a new sheet in Excel, select the formula cells, press Ctrl+C, open the destination sheet, select the first destination cell, then use Home > Paste > Paste Special > Formulas. This transfers the formulas without copying the source formatting. Before you paste, decide whether the formulas should use data on the new sheet or keep referring to the original sheet; relative references can change when formulas move.

What You’ll Need

An Excel workbook containing the formulas you want to copy.
A destination worksheet in the same workbook. Create one with the plus button beside the sheet tabs if needed.
Access to the source data the formulas depend on. If formulas use data on another sheet or in another workbook, keep that data available and check the references after copying.

Step-by-Step

Choose how the new sheet should work. There are two common goals: copy formulas into an existing blank sheet, or make a full duplicate of the worksheet that contains them. Copying a range gives you control over which cells move. Duplicating a sheet is often better when you want its layout, formulas, formatting, and other worksheet content together. In either case, first decide whether calculations should use the new sheet’s own data or continue to use the original sheet’s data.
Create or open the destination sheet. To add a blank worksheet, click the plus sign beside the sheet tabs, then click the new tab. Rename it by double-clicking its tab, typing a clear name, and pressing Enter. If the destination sheet already exists, select its tab. For a full worksheet copy instead, right-click the source sheet tab, choose Move or Copy, select where the copy should go, tick Create a copy, and click OK. Check the copied sheet’s formulas afterward, especially if they refer to other sheets.
Select the formula range on the source sheet. Return to the sheet that contains the formulas. Click the first formula cell and drag to select the complete range, or click the first cell, hold Shift, and click the last cell. For a large continuous range, click its first cell, then hold Shift while pressing the appropriate arrow keys. Avoid selecting entire rows or columns unless you truly intend to copy all their contents; that can add unwanted formatting and make the destination harder to manage.
Copy the selected cells. Press Ctrl+C on Windows. A moving dashed outline appears around the copied range. On a Mac, use Command+C. If you want formulas plus their cell formatting, ordinary copying and pasting may be appropriate. If you want formulas alone, continue to the Paste Special step rather than using an ordinary paste.
Choose the correct starting cell on the destination sheet. Click the destination tab and select the cell where the upper-left cell of the copied range should go. To keep the same layout, choose the same cell address as the source range’s upper-left cell. For instance, if the source selection starts at D2, choose D2 on the new sheet. If you paste it somewhere else, Excel adjusts relative references based on how far the formula has moved. Also make sure the destination area is clear: pasting a multi-cell range over existing content replaces the cells in that area.
Paste formulas only. On Windows, open Home > Paste > Paste Special > Formulas. In some ribbon layouts, click the Paste dropdown to see Paste Special. You can also press Ctrl+Alt+V to open the Paste Special dialog, choose Formulas, and click OK. On a Mac, use the Paste Special command from Excel’s ribbon or menu; the exact menu layout can vary by release. Excel for the web may show a smaller set of Paste Special choices, so use Excel desktop if the Formulas option is unavailable. To copy formatting too, choose an appropriate paste option or use ordinary paste instead.
Check the copied formulas and results. Click a few destination cells and inspect the formula bar. Confirm that each formula points to the intended cells and that the displayed results make sense. Check the first and last cells in the pasted range, since a shifted reference or an unexpected blank at an edge can be easy to miss. If formulas show an error, look at the reference and the data it needs before changing the formula.

For example, suppose quantities are in B2:B10 and unit prices are in C2:C10, and you calculate totals in D2:D10. With a quantity of 4 in B2 and a unit price of 15 in C2, the formula in D2 is =B2*C2, which returns 60. If you copy D2 to D3 on the same sheet, Excel normally changes the references to B3 and C3. Pasting the formula into D2 on a new sheet can likewise make it refer to B2 and C2 on that destination sheet. If those destination cells are empty, the result may be zero even though the source sheet has data.

Common Mistakes

Assuming the formula will keep using the source sheet. A relative reference such as B2 usually adjusts as a formula is copied. When you paste onto another sheet, check whether the result points to cells on the new sheet or whether you need the source sheet’s data. Explicit references to a different sheet are different from references to cells on the formula’s own sheet.
Confusing formulas with displayed results. Regular paste copies formulas, while Paste Special > Values copies their current calculated results instead. If you choose Values by mistake, the destination cells will not recalculate when their inputs change. Click a destination cell and inspect the formula bar to confirm it contains a formula.
Expecting Paste Special > Formulas to copy formatting. This option is designed to transfer formulas, not reproduce the source’s full appearance. Number formats, fills, borders, and other formatting may not come along. If the destination needs the same look, copy the formatting separately or use ordinary paste and then check the result.
Pasting over existing content. A pasted range replaces cells in its destination footprint. Check that the top-left destination cell is correct and that the full area below and to the right is available before pasting.
Breaking a dependency by copying only part of the range. A formula can rely on a helper cell, lookup table, or input elsewhere on the sheet. Copying just the visible calculation cells does not automatically bring along every cell those formulas depend on. Identify those inputs before relying on the new sheet.
Expecting a copied formula to preserve an absolute cell’s worksheet. Dollar signs lock a row or column when a formula is filled, but they do not by themselves lock the worksheet. Check the full reference when the formula must keep using a particular sheet or input cell.

Tips

Use ordinary paste for a complete visual copy. If you want formulas, cell formatting, and other copied cell properties together, Ctrl+V is usually quicker than Paste Special. Review the result because ordinary paste can also bring along formatting you did not want.
Keep the same cell positions when possible. If the source formulas and their input cells occupy the same addresses on the new sheet, pasting into the matching starting cell makes the layout easier to understand. It does not guarantee that references will point to the right data, so still inspect them.
Make a full sheet copy for a repeated template. Right-click the tab and use Move or Copy with Create a copy selected when the new sheet should retain the existing worksheet layout and contents. Rename the copy promptly so it is clear which sheet is the original and which is the new one.
Use the formula bar to review references. Click a copied cell and examine its formula there. This is more reliable than judging by the displayed result alone, particularly when two sheets contain similar-looking data.
Check calculation results after copying. Compare a few results with the source sheet or calculate one by hand. If the workbook is set to manual calculation, press F9 on Windows to recalculate. On a Mac, use Excel’s Formulas tab or the available recalculation command because keyboard shortcuts can differ.
Keep a clean source copy. If the formulas are important, save the workbook before making a large set of changes. A saved checkpoint makes it easier to recover if you paste into the wrong area or alter a worksheet you meant to preserve.

Troubleshooting

The destination shows zeros or unexpected values. Select a cell and inspect its formula. If it refers to blank cells on the destination sheet, decide whether the formula should use local inputs or data on the original sheet. You can either copy the required input data to the new sheet or edit the reference so it points to the intended source. Do not replace references at random: first identify what each part of the formula is meant to calculate.

The cells contain numbers but no formulas. You may have chosen Paste Special > Values. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if you have not made other changes you need to keep, then copy the source range again and select Formulas in Paste Special. If undo is no longer appropriate, repeat the copy and paste carefully. Check a destination cell’s formula bar to verify the correction.

The formula refers to the wrong row or column. This usually happens because a relative reference changed when you pasted at a different position. Compare the source and destination formulas and check how far the destination cell is from the source cell. If the formula needs to refer to a fixed row or column as it is copied, edit the reference in the source formula before copying, or correct the destination formula. Remember that locking a row or column does not necessarily make a reference stay on the original worksheet.

Excel displays a reference error or a name error. Check whether the formula refers to a sheet that was renamed, deleted, or not included in the workbook. If the formula needs a source sheet, keep that sheet and confirm the sheet name in the formula. If you copied formulas from a different workbook, check whether they still refer to that workbook and whether it is available. A missing input range or unavailable external workbook can cause errors even when the paste itself worked.

Paste Special is unavailable or the result looks incomplete. Copy the range again, then select a single destination cell before opening Paste Special. Excel’s available choices can depend on what is on the clipboard and whether you are using desktop Excel or the web version. If the Formulas choice is not present in the web app, try Excel for Microsoft 365 on Windows. If some cells did not copy, confirm that the original selection included the whole range and that no merged cells or protected-sheet restrictions are preventing the paste.

Frequently Asked Questions

How do I copy formulas without changing the formatting?
Copy the range, select the destination’s upper-left cell, then use Home > Paste > Paste Special > Formulas. This transfers formulas without intentionally bringing over the source formatting. Check the destination’s number formats if the results need to display as dates, percentages, or currency.

Will formulas update automatically on the new sheet?
Formulas recalculate when their inputs change, provided calculation is enabled and their references point to the intended inputs. Copying a formula does not copy every input it depends on. Check the references and make sure the needed source data is still available.

How can I keep a formula linked to the original sheet?
Use a reference that names the original sheet, rather than relying on a relative reference to a cell on the formula’s own sheet. If you already have formulas that explicitly refer to another sheet, inspect a pasted example to confirm that the sheet reference remains as intended. You can also copy the entire worksheet, but still verify formulas that link to other sheets.

Can I copy formulas to a different workbook?
Yes. Open both workbooks, copy the source range, and paste it into the destination workbook. Formulas that rely on other sheets or workbooks may continue to refer to their original locations, so check the formula bar and test the results. If you need a self-contained workbook, bring over the required source data and update the references deliberately.

Can I copy an entire sheet, including its formulas?
Yes. Right-click the sheet tab, choose Move or Copy, select the destination position, tick Create a copy, and click OK. This is useful for making a template or a working duplicate. Review formulas, links, and any sheet-specific content after the copy, especially if the new worksheet will use different inputs.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.