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

In this tutorial, you'll learn several different ways to copy formulas in Excel - how to copy a formula down a column, across all selected cells, how to copy formulas exactly, and more.

Copying formulas in Excel is one of the easiest tasks, usually just a mouse click away. I say "usually" because there can be very specific situations that require special tricks, such as copying a series of formulas without changing the cell references, or inserting the same formula into several non-adjacent cells.

Fortunately, Microsoft Excel offers many ways to do the same task, including copy formulas. In this guide, we'll discuss different methods of copying formulas in Excel so you can choose the method that's best for your work.

How to copy a formula for one column

Microsoft Excel provides a very quick way to copy formulas into columns. All you have to do is:

Enter the formula in the top cell.
Select the cell containing the formula, hover over the small square in the lower right corner of the cell, this square is calledFilling handle.When you do this, the cursor will change to a thick black cross.
Hold down the fill handle and drag it down over the cell where you want to copy the formula.

You can do the sameattraction formulaA nurseneighboring cellRight, left or up.

If the formula includesrelative cell reference(without the $ sign), it will automatically change based on the relative position of the row and column. Therefore, after copying the formula, check that the cell references are set correctly and produce the desired result. If it is necessary,Switch between absolute, relative and mixed referencesusingF4key.

In the example above, to make sure the formula is copied correctly, we select a specific cell in column C, say C4, and look at the cell reference in the formula bar. As you can see in the screenshot below, the formula is correct - exactly relative to line 4:

How to copy formulas without copying formatting

Duplicating a formula by dragging the fill handle down not only copies the formula but also the original cellformSuch as font or background color, currency symbol, number of decimal places displayed, etc. This works fine most of the time, but sometimes it messes up the existing formatting in the cell where the formula is copied. A common example is superpowerAlternate line shadowJust like the picture below.

To prevent existing cell formatting from being overwritten, drag the fill handle as shown above, release and clickautocomplete optionsdrop-down menu and selectpadding without formatting.

Copy the formula across the entire column

As you can see, the fill handle reproduces formulas in Excel very easily. But what if you need to copy a formula into a table with 1000 rows? Carrying types across hundreds of rows doesn't seem like a good idea. Fortunately, Microsoft Excel offers quick solutions for this situation as well.

Double-click the plus sign to fill in 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 part of this guide, the detailed steps are as follows.

To copy an Excel formula to an entire column:

Enter the formula in the top cell.
Place the cursor in the lower right corner of the formula cell, wait for it to change to a plus sign, and double-click the plus sign.

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

Among other great featuresExcel TablicaThings like predefined styles, sorting, filtering and linking rows, and automatically calculated columns make Excel spreadsheets a really great tool for analyzing groups of related data.

By entering a formula in a cell (any cell, not necessarily the top cell) in a table column, you can createCalculated columnand immediately copy your formula to all other cells in that column. Unlike padding handles, Excel tables have no problem copying formulas across entire columns, even if the table has one or more empty rows:

To convert a range of cells to an Excel table, simply select all cells and pressCtrl+T.If you prefer the visual way, select a series, go toenter> tabsurfaceGroup on the Excel ribbon and clicktablebutton.

advice.If you really don't want to use an Excel table in your worksheet, you can create one temporarily to make working with formulas easier, and then you can convert the table to a regular range in seconds. Just right click on the panel and selecttable>conversion to rangein the context menu.

Copy formulas to non-adjacent cells/ranges

It goes without saying that the fill handle is the fastest way to reproduce formulas in Excel. But what if you want to copy an Excel formula to cells that are not next to or beyond the end of the original data? Just use the old copy and paste method:

Click the cell containing the formula to select it.
accordingCtrl+CCopy the formula.
Select the cell or range of cells to which you want to paste the formula (to select non-adjacent ranges, press and holdcontrol keykey).
accordingCtrl+VPaste the formula.
Press Enter to complete the pasted formula.

notes.Copy/paste shortcuts to copy formulas and formatting. reachCopy the formula without formatting, select the appropriate onePasteoptions in the ribbon or right-click menu, as shown inCopy Excel formulas without formatting.

Enter formulas in multiple cells with one keystroke (Ctrl + Enter)

This method saves time when you need to enter the same formula in multiple cells (adjacent or not) in a worksheet.

Select all the cells in which you want to enter the formula. To select cells that don't match, press and holdcontrol keykey.
accordingF2Enter edit mode.
Enter the formula in the cell and pressCtrl + EnterreplaceA nurse.This is! The formula will be copied to all selected cells and Excel will adjust the associated cell references accordingly.

advice.You can use this method to enter any data, not just formulas, into multiple cells at once. Some other techniques are covered in the following guides:How to enter the same data in all selected cells at once.

How to copy Excel formulas without copying formatting

As you know, when copying formulas to columns in Excel, you can useAbility to fill without formattingThis allows you to copy the formula but keep the existing formatting of the destination cell. excelCopy and pasteFunctions allow more flexibility in pasting options.

Select Sell by type.
Click to copy the cellCtrl+C.Alternatively, right-click the cell and selectcopyfrom the context menu or clickcopyactive buttonDom> tabclipboard.
Select all the cells you want to copy the formula to.
Right click on the selected cells and selectofficiallylobe, belowgluing options:

For more pasting options, click the arrow belowPasteRibbon buttons. For example, you can chooseTypes and formation of numbersPaste only formulas and number formatting, such as percentage formatting, currency formatting, etc.:

advice.If you're not sure which paste option is best for you, hover over the different icons to see a preview of each paste option.

Copy formulas in Excel without changing the references

Excel formulas rarely appear alone in spreadsheets. In most cases, you enter a formula in one cell and then copy it to other cells in the same column or row to perform the same calculation on the data set. If your formula containsrelative cell reference(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 a formula=A1*2In cell B1, copy this formula to cell B3 and the formula is done=A3*2.

However, if you want ExcelCopy the formula exactly, without changing cell references along the way? Depending on your specific task, choose one of the solutions below.

Copy or move individual formulas without changing cell references

If you just need to copy or move a formula, creating an exact copy is easy.

Select the cell that contains the formula you want to copy.
Use the mouse to select a formula in the formula bar and pressCtrl+Creachcopythe. if you wantmovementtype, printingCtrl + Xcut it out.
accordingEsckey to exit the formula bar.
Select the target cell and pressCtrl+VPaste the formula there.

Alternatively, you can enter edit mode and copy the formula into a cell as text:

Select the cell that contains the formula.
accordingF2(or double-click the cell) to enter edit mode.
Use the mouse to select the formula in the cell and pressCtrl+Ccopy it.
Select the target cell and pressCT+V.This will paste the formula exactly without changing the cell references because the formula is copied as text.

advice.hurry upCopy the formula from the top cellWithout changing the reference, select the cell where you want to paste the formula and pressCtrl + '.

Copy a range of formulas without changing cell references

To move or copy a range of Excel formulas without changing the cell references, use one of the following methods.

Method 1. Use absolute or mixed cell references

If you need to make oneexact copy of the formulaIrelative cell reference(like A1), the best way is to change them toabsolute reference($A$1) fixes the reference to the given cell so that it remains static no matter where the formula is moved. In other cases, you may need to usemixed cell reports($A1 or A$1) locks a column or row. Doesn't make much sense so far? Okay, let's consider the following example.

Suppose you have a table that calculates the prices of fruit in euros based on the dollar prices in column B and the exchange rate in cell C2:

As you can see in the screenshot above, the formula includes an absolute cell reference ($C$2) to specify the exchange rate in cell C2 and a relative cell reference in cell B5 because you want to make adjustments for each row. This works fine as long as the formula remains in column C.

But let's see what happens if you need to move the euro value from column C to column F. If you copy the formula in the usual way by copying/pasting cells, the formula in cell C5 (=B5*$C$2) will become =D5*$C$2, when pasted into cell F5, will make your calculations completely wrong!

To fix this, simply change the relevant reference (B5) tomixed report$B5 (absolute column and relative row). By placing a dollar sign ($) before the column letter, you can anchor the reference to column B regardless of where the type moves.

Now if you copy or move the formula from column D to column F or any other column thenColumn references are not changedBecause you locked it with the dollar sign ($B5).

meaning ofExcel cell referenceIt may be hard to master at first, but trust me, it's worth your time and effort because you'll save more time in the long run. For example, see how to calculate an entire array using a single formulamixed cell reports.

However, if you already have a large number of formulas with linked cell references in your Excel sheet, and you need to copy these formulas quickly and accurately, but find that you cannot get the references correctly, using one of the following methods may be the solution.

Method 2. Copy the Excel formula through Notepad without changing the references

Press to enter formula view modeCtrl + `shortcut or use any other method described inHow to display formulas in Excel.
Select all the cells that contain the formulas you want to copy or move.
accordingCtrl+Ccopy the formula orCtrl + Xcut them out. Use the last shortcut if you want to move the type to a new location.
Open Notepad or any other text editor and tapCtrl+VPaste the formula there. then pressCtrl + AThen select all typesCtrl+CCopy them as text.
In the Excel worksheet, select the upper left cell where you want to paste the formula and pressCtrl+V.

notes:

You can only paste formulassame worksheetWhere the source is a formula, unless the reference contains a sheet name, the formula will terminate.
The worksheet must be insideType display mode.To check it out go toofficially> tabType controlgroup and check ifshow the guybutton is enabled.
After pasting the formula, pressCtrl + `Disables the type preview function.

Method 3. Copy the formula exactly using Excel's Find and Replace

To copy a range of Excel formulas without changing the cell references, you can use Excelfind and replaceThe function is implemented as follows.

Select the cell that contains the formula you want to copy.
existsDomcard, go toprocessinggroup and click onfind and select>replace…Alternatively, just tapCtrl+H, which is a shortcut to bootDialog box in Excel.
from the insidefind and replaceType the equal sign (=) in the dialog box.what to look forBox. from the insideuse. . . replaceenter a symbol or character string that is not used in any formula, such as ', #, or \.
The purpose of this step is to convert the formula to a text string, which will prevent Excel from changing the cell reference during the copying process.
notes.Do not use asterisks (*) or question marks (?) for substitutions, as these are Excel wildcards and using them can make the following steps more difficult.
clickreplace everythingbutton and closefind and replacedialogue. All types in the selected range will be converted to text strings:
Now you can select any cell, pressCtrl+CTo copy them, select the top cellcurrent sheetwhere you want to paste the formula, then tapCtrl+V.Since Excel does not interpret formulas without an equals sign as formulas, they are copied exactly without changing the references.
usefind and replaceUndo the change again. Select two ranges using the original type and the copied type (to select non-adjacent ranges, press and holdcontrol key).accordingCtrl+HThe Find and Replace dialog box opens. this time, insidewhat to look forframe, i = uuse. . . replacebox and click onreplace everythingbutton. complete!

Shortcuts for copying Excel formulas to other cells

1. Copy the formula down

Ctrl+D- Copy the 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 the formula on the right

Ctrl+R- Copy formula from cell to left 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 pressCtrl+R.

advice.The above two shortcuts can also be used to copy formulas across multiple cells. The trick is to select both the source and destination cells before hitting 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 the formula exactly

Ctrl + '-Copy the formula from the top cell to the currently selected cellExactlyand put the cell in edit mode.

Here's a quick way to make an exact copy of the formulaCell references do not change.For example, to copy the formula from cell A1 to A2 without changing the references, select A2 and pressCtrl + '.

notes.Don't confuse the shortcutsCtrl + '(Ctrl+single quote) Copy the formula exactly from the top cellCtrl + `(Ctrl + key for extra emphasis) activateShow formula function in Excel.

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

Maybe you're right. . . I'm interested in

How to make formulas in Excel
The Microsoft Excel formula with examples
How to display formulas in Excel
How to hide and lock formulas in Excel
How to edit, evaluate and debug formulas in Excel
Excel formulas not working, not updating, not calculating: fixes and solutions