How to copy formulas in Excel with or without changing references (2023)

In this tutorial, you will learn different ways to copy formulas in Excel: how to copy formulas to a column or to all selected cells, how to copy a formula exactly without changing cell references or formatting, and more.

Copying formulas in Excel is one of the easiest tasks that is usually done with one click. I say "general" because there can be very specific cases that require special tricks, like copying a set of formulas without changing cell references, or typing the same formula into multiple nonadjacent cells.

Luckily, Microsoft Excel offers many ways to accomplish the same task, and the same goes for copying formulas. In this tutorial, we will discuss different ways to copy formulas in Excel so that you can choose the most suitable one for your task.

• Copy the formula into a column
• Copy the formula to the whole column
• Copy formula to non-adjacent cells/ranges
• How to enter a formula in multiple cells
• How to copy formulas but not format them
• How to copy a formula without changing the references
• Shortcuts to copy formulas in Excel

How to copy the formula into a column

Microsoft Excel offers a really fast way to copy a formula into a column. You simply do the following:

1. Enter a formula in the top cell.
2. Select the cell with the formula and hover over a small square in the lower-right corner of the cell that pops upfilling handle. When you do this, the cursor turns into a thick black cross.
3. Hold down the fill handle and drag down the column over the cells where you want to copy the formula.

Likewise you candraw formulainneighboring cellsto the right, left or up.

If the formula containsrelative cell references(without the $ sign) they change automatically based on a relative position of rows and columns. So after copying the formula, check whether the cell references are set correctly and lead to the desired result. If necessary,Switch between absolute, relative and mixed referencesUse ofF4Key.

In the example above, to ensure that the formula was copied correctly, we select a cell in column C, e.g. B. C4, and look at the cell reference in the formula bar. As you can see in the screenshot below, the formula regarding line 4 is fine, just as it should be:

How to copy a formula without copying the format

If you copy a formula down by dragging the fill handle, not only the formula but also the source cell will be copiedFormatlike the font or background color, currency symbols, number of decimal places displayed, etc. In most cases this works fine, but sometimes it can mess up the existing formats in the cells the formula is copied to. A common example is overwritingalternating line shadinglike the following screenshot.

To avoid overwriting the existing cell format, drag the fill handle as shown above, release it and click the buttonAutoComplete optionsdrop down menu and selectfill in without formatting.

Copy the formula to the entire column

As you just saw, the Fill handler makes copying formulas in Excel really easy. But what if you need to copy a formula onto a sheet of ten hundred lines? It doesn't seem like a good idea to stretch the formula over hundreds of rows. Fortunately, Microsoft Excel offers a few quick fixes for this as well.

Double-click the plus sign to fill the entire column

To apply the formula to the entire column, double-click the plus sign instead of dragging it. For those who skipped the first section of this tutorial, the detailed steps are below.

To copy an Excel formula to the entire column, please do the following:

1. Enter your formula in the top cell.
2. Place your cursor in the lower-right corner of the cell with the formula, wait for it to turn into a plus sign, and then double-click the plus sign.

UseDouble-clicking the plus sign copies the formula down where there is some data in the adjacent column(s). As soon as an empty line occurs, the autofill stops. So if your worksheet contains spaces, you need to repeat the above process to copy the formula under a blank row or drag the fill handle as explained in the examples above:

Create an Excel spreadsheet to automatically copy a formula to all cells in a column

Among other great features ofExcel spreadsheetsLike predefined styles, sorting, filtering, and banded rows, automatically calculated columns make an Excel spreadsheet a truly wonderful tool for analyzing groups of related data.

When you enter a formula in a cell in a table column (any cell, not necessarily the top one), you create onecalculated columnand have your formula copied to all other cells in that column immediately. Unlike the Fill handler, Excel spreadsheets have no problem copying the formula to the entire column, even if the spreadsheet contains one or more blank rows:

To convert a range of cells to an Excel spreadsheet, just select all the cells and pressControl + T. If you prefer a visual form, select the range, go toinsertiontab >Table Mountainsgroup in the Excel ribbon and click theMesaKnopf.

Bribery.If you really don't want an Excel spreadsheet in your worksheet, you can create it temporarily to make working with formulas easier, and then convert the spreadsheet back to a regular range in seconds. Just right click on the table and selectMesa>convert to areain the context menu.

Copy a formula to nonadjacent cells/ranges

It goes without saying that the fill handler is the fastest way to copy formulas in Excel. But what if you want to copy your Excel formula into non-contiguous cells or past the end of the source data? Just use the old way of copying and pasting:

1. Click the cell with the formula to select it.
2. PressControl + Cto copy the formula.
3. Select a cell or range of cells where you want to paste the formula (to select nonadjacent ranges, hold down the keycontrolKey).
4. PressCtrl + Vto insert the formula.
5. Press Enter to complete the pasted formulas.

UseThe copy/paste shortcuts copy the formula and format. ACopy formula without formattingchoose a suitable oneTakeOption in ribbon or context menu as shown inCopy an Excel formula without formatting.

Enter a formula in multiple cells with a single keystroke (Ctrl + Enter)

In situations where you need to enter the same formula in more than one adjacent or non-adjacent cell on a worksheet, this method can save you time.

1. Select all the cells where you want to enter the formula. To select nonadjacent cells, hold down the keycontrolKey.
2. PressF2to enter edit mode.
3. Enter your formula in a cell and pressControl + introductionInstead ofGet into. That's it! The formula is copied to all selected cells and Excel adjusts the relative references of the cells accordingly.

How to copy an Excel formula but without formatting

As you already know, when you copy a formula to a column in Excel, you can use theFill without format optionThis allows you to copy the formula but keep the existing formatting of the target cells. Excelget copyThe feature offers even more flexibility in terms of paste options.

1. Select the sale that contains the formula.
2. Copy this cell by pressingControl + C. Alternatively, you can right-click the cell and selectCopyin the context menu or click on theCopybutton on theHaustab >clipboard.
3. Select all the cells where you want to copy the formula.
4. Right click on the selected cells and selectformulaslowInsert options:
   

To see more paste options, click the arrow belowTakebutton on the band. For example, you can chooseFormulas and number formatto paste only the formula and number format, e.g. B. Percent format, currency format and the like:

Bribery.If you're not sure which paste option works best for you, hover your mouse over different icons to preview this or that paste option.

Copy the formula in Excel without changing the references

Excel formulas rarely appear alone in a spreadsheet. In most cases, you enter a formula in a cell and then copy it to other cells in the same column or row to perform the same calculation on a group of data. And if your formula includesrelative cell references(without the $) Excel automatically adjusts them so that each formula works with data in its own row or column. Most of the time, that's exactly what you want. For example if you have the formula=A1*2in cell B1 and copy this formula into cell B3, the formula changes to=A3*2.

But what if you want Excelcopy the formula exactlywithout changing the cell references? Depending on the task, choose one of the following solutions.

Copy or move a single formula without changing cell references

If you just need to copy or move a formula, you can easily make an exact copy.

1. Select the cell with the formula that you want to copy.
2. Select the formula in the formula bar with the mouse and pressControl + CaCopythat. If you want itmovementthe formula, pressStrg + Xto cut it
   
3. Press theEscbutton to exit the edit bar.
4. Select the target cell and pressCtrl + Vto paste the formula there.

Alternatively, you can switch to edit mode and copy the formula into the cell as text:

1. Select a cell with the formula.
2. PressF2(or double-click the cell) to switch to edit mode.
3. Select the formula in the cell with the mouse and pressControl + Cto copy it.
   
4. Select the target cell and pressCtrl+V. This will paste the formula exactly without changing the cell references since the formula was copied as text.

Bribery.fastCopy a formula from the cell abovewithout changing the reference, select the cell where you want to paste the formula and presscontrol + '.

Copy a set of formulas without changing cell references

Use one of the following methods to move or copy a range of Excel formulas so cell references don't change.

Method 1. Use absolute or mixed cell references

If you need to make oneexact copy of the formulasconrelative cell references(like A1), the best way would be to change themabsolute references($A$1) to fix the reference to a specific cell so that it remains static no matter where the formula is moved. In other cases you may need to usemixed cell references($A1 or A$1) to lock a column or row. Doesn't make much sense so far? Well, let's consider the following example.

Suppose you have a table that calculates fruit prices in EUR based on the USD price in column B and the exchange rate in cell C2:

As you can see in the screenshot above, the formula contains an absolute cell reference ($C$2) to fix the price in cell C2 and a relative cell reference to cell B5, since this reference should be adjusted for each row. And this approach works well as long as the formulas stay in column C.

But let's see what happens if you need to move the prices in EUR from column C to column F, for example. If you copy the formulas in the usual way, by copying/pasting the cells, the formula will be in cell C5 (=B5*$C$2) changes to =D5*$C$2 when pasted into cell F5, causing your calculations to be wrong!

To fix this, just change a relative reference (B5) to amixed reference$B5 (absolute column and relative row). By putting the dollar sign ($) in front of the column letter, you anchor the reference to column B wherever the formula goes.

And now, if you copy or move the formulas from column D to column F or any other column, thethe column reference does not changebecause you locked it with the dollar sign ($B5).

The concept ofExcel cell referencesIt can be hard to understand at first, but trust me, it's worth your time and effort because it will save you a lot more time in the long run. For example, see how to calculate the entire table with a single formulamixed cell references.

However, if you already have a bunch of formulas with relative cell references in your Excel spreadsheet and need to quickly make an exact copy of those formulas, but don't feel like you can get the correct references, one of the following methods might be a solution . .

Method 2. Copy Excel formulas without changing references via Notepad

1. Enter formula view mode by pressing the buttonCtrl + `Shortcut or any other method described inHow to view formulas in Excel.
2. Select all the cells with the formulas that you want to copy or move.
3. PressControl + Cto copy the formulas thatStrg + Xto cut them. Use the last link when you want to move formulas to a new location.
   
4. Open Notepad or another text editor and pressCtrl + Vto paste the formulas there. then pressControl + Ato select all formulas, andControl + Cto copy them as text.
5. In your Excel spreadsheet, select the top left cell where you want to paste the formulas and pressCtrl + V.

Method 3. Copy formulas exactly with Excel Find and Replace

To copy a set of Excel formulas without changing their cell references, you can use the Excelsearch and replacefunction as follows.

1. Select the cells with the formulas you want to copy.
2. About himHaustab, go toeditiongroup and clickSearch and select>Substitute…or just pressCtrl + H, which is the shortcut to launch theDialog in Excel.
3. insideSearch and replaceIn the dialog box, type the equal sign (=).find thatCrate. insidereplace withIn the box, enter a symbol or string that is not used in any of your formulas, such as ', # or \.
   The purpose of this step is to convert the formulas to text strings, which prevents Excel from changing the cell references during the copying process.

   UseDo not use an asterisk (*) or question mark (?) to replace them, as these are wildcard characters in Excel and using them would complicate later steps.

   (Video) Copy or Move Formulas Without Changing Cell References In Excel - 3 Methods

4. Click on thatreplace everythingbutton and close thesearch and replaceDialog. All formulas in the selected range are converted to text strings:
   
5. Now you can select any cell, pressControl + CTo copy them, select the top cell in thecurrent worksheetwhere you want to paste the formulas and pressCtrl + V. Since Excel does not interpret formulas without an equals sign as formulas, they are copied exactly without changing the references.
6. UseSearch and replaceagain to undo the change. Select both regions with the original and copied formulas (to select nonadjacent regions, press and holdcontrol). PressCtrl + Hto open the Find and Replace dialog box. This time, type the backslash (\) (or whatever character you used for the first replacement) in thefind thatfield, y = in thereplace withcheckbox and click thereplace everythingButton. Completed!

Shortcuts to copy Excel formulas to other cells

1. Copy a formula down

Ctrl+D- Copy a formula from the cell above and adjust the cell references.

For example, if you have a formula in cell A1 and want to copy it to cell A2, select A2 and pressCtrl+D.

2. Copy a formula to the right

Strg + R- Copy a formula from the left cell and adjust cell references.

For example, if you have a formula in cell A2 and want to copy it to cell B2, select B2 and pressStrg + R.

Bribery.The two keyboard shortcuts above can also be used to copy formulas across multiple cells. The trick is to select both the source cell and the target cell before pressing the shortcut. For example, if you want to copy the formula from A1 to the next 9 rows, select cells A1:A10 and pressCtrl+D.

3. Copy a formula exactly

control + '- Copies a formula from the cell above to the currently selected cellExactlyand exits the cell in edit mode.

This is a quick way to make an exact copy of a formula.without changing the cell references. For example, to copy a formula from cell A1 to cell A2 so that the references don't change, select A2 and presscontrol + '.

UseDon't confuse the shortcutcontrol + '(Ctrl + inverted comma) copies exactly one formula from the cell aboveCtrl + `(ctrl + engraved accent key) which will be activatedShow formula mode in excel.

Well, that's all I have to say about copying formulas in Excel. If you know other methods to quickly move or copy formulas in Excel spreadsheet, please share. Thanks for reading and I hope to see you on our blog next week!

You might also be interested in

• How to create formulas in Excel
• Microsoft Excel formulas with examples
• How to view formulas in Excel
• How to hide and lock formulas in Excel
• Edit, evaluate and debug formulas in Excel
• Excel formulas not working, not updating, not calculating: fixes and solutions