by In this tutorial, you will learn a few different ways of copying formulas in Excel - how to copy formula down a column, to all of the selected cells, 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 in a mouse click. I say "usually" because there can be very specific cases that require special tricks, like copying a range of formulas without changing cell references or entering the same formula in multiple non-adjacent cells.
Luckily, Microsoft Excel offers many ways to do the same task, and it is true for copying formulas. In this tutorial, we are going to discuss different ways to copy formulas in Excel so that you could choose the one best suited for your task.
How to copy formula down a column
Microsoft Excel provide a really quick way to copy a formula down a column. You just do the following:
- Enter a formula in the top cell.
- Select the cell with the formula, and hover the mouse cursor over a small square at the lower right-hand corner of the cell, which is called the Fill handle. As you do this, the cursor will change to a thick black cross.
- Hold and drag the fill handle down the column over the cells where you want to copy the formula.
In a similar manner, you can drag formula into adjacent cells to the right, to the left or upwards.
If the formula includes relative cell references (without the $ sign), they will automatically change based on a relative position of rows and columns. So, after copying the formula, verify that the cell references have got adjusted properly and produce the result that you want. If necessary, switch between absolute, relative and mixed references by using the F4 key.
In the above example, to make sure the formula was copied correctly, let's select some cell in column C, say C4, and view the cell reference in the formula bar. As you can see in the screenshot below, the formula is all right - relative to row 4, exactly as it should be:
How to copy a formula down without copying formatting
Copying a formula down by dragging the fill handle not only copies the formula, but also the source cell formatting such as font or background color, currency symbols, the number of displayed decimal places, etc. In most cases, this works just fine, but sometimes it can mess up the existing formats in cells where the formula is being copied to. A common example is overwriting alternate row shading like in the following screenshot.
For prevent overwriting the existing cell formatting, drag the fill handle as demonstrated above, release it, click the Auto Fill Options drop-down menu, and select Fill Without Formatting.
Copy formula to the entire column
As you've just seen, the fill handle makes copying formulas in Excel really easy. But what if you need to copy a formula down a ten-hundred-line sheet? Dragging the formula over hundreds of rows does not look like a good idea. Luckily, Microsoft Excel provides a couple of quick solutions for this case as well.
Double-click the plus sign to fill the whole column
To apply the formula to the entire column, double-click the plus sign instead of dragging it. For those who have skipped the first section of this tutorial, the detailed steps follow below.
To copy an Excel formula to the whole column, do the following:
- Input your formula in the top cell.
- Position the cursor to the lower right corner of the cell with the formula, wait until it turns into the plus sign, and then double-click the plus.
Note. Double-clicking the plus sign copies the formula down as far as there is some data in the adjacent column(s). As soon as an empty row occurs, the auto fill stops. So, if your worksheet contains any gaps, you will have to repeat the above process to copy the formula below an empty row or drag the fill handle as explained in the previous examples:
Create an Excel table to copy a formula to all cells in a column automatically
Among other great features of Excel tables such as predefined styles, sorting, filtering and banded rows, automatically calculated columns is what makes an Excel table a truly wonderful tool for analyzing groups of related data.
By entering a formula into one cell in a table column (just any cell, not necessarily the top one), you create a calculated column and have your formula instantly copied to all other cells in that column. Unlike the fill handle, Excel tables have no problem with copying the formula across the entire column even if the table has one or more empty rows:
To convert a range of cells to an Excel table, simply select all the cells and press Ctrl + T. If you prefer a visual way, select the range, go to the Insert tab > Tables group on the Excel ribbon, and click the Table button.
Tip. If you don't really want an Excel table in your worksheet, to can create it temporary, to make the work with formulas easier, and then you can convert the table back to a usual range in a second. Just right-click the table and choose Table > Convert to Range in the context menu.
Copy a formula to non-adjacent cells / ranges
It goes without saying that the fill handle is the fastest way to copy formula in Excel. But what if you want to copy your Excel formula in non-contiguous cells or beyond the end of the source data? Just use the old good copy & paste way:
- Click the cell with the formula to select it.
- Press Ctrl + C to copy the formula.
- Select a cell or a range of cells where you want to paste the formula (to select non-adjacent ranges, press and hold the Ctrl key).
- Press Ctrl + V to paste the formula.
- Press Enter to complete the pasted formulas.
Note. The copy/paste shortcuts copy the formula and formatting. To copy the formula without formatting, choose an appropriate Paste option on the ribbon or in the right-click menu, as demonstrated in Copying an Excel formula without formatting.
Enter a formula into multiple cells with a single key stroke (Ctrl + Enter)
In situations when you need to input the same formula in more than one cell on a worksheet, adjacent or non-adjacent ones, this method can be a time-saver.
- Select all the cells where you want to enter the formula. To select non-contiguous cells, press and hold the Ctrl key.
- Press F2 to enter the edit mode.
- Input your formula in one cell, and press Ctrl + Enter instead of Enter. That's it! The formula will get copied to all of the selected cells, and Excel will adjust relative cell references accordingly.
Tip. You can use this method to enter any data, not just formulas, in multiple cells at a time. A few other techniques are described in the following tutorial: How to enter the same data into all selected cells at a time.
How to copy an Excel formula but not formatting
As you already know, when copying a formula down a column in Excel, you can use the Fill Without Formatting option that lets you copy the formula but keep the existing formatting of the destination cells. Excel's Copy & Paste feature offers even more flexibility with regard to paste options.
- Select the sell containing the formula.
- Copy that cell by pressing Ctrl + C. Alternatively, right click the cell and select Copy from the context menu, or click the Copy button on the Home tab > Clipboard.
- Select all the cells to which you want to copy the formula.
- Right-click the selected cells and choose Formulas under Paste Options:
For more paste options, click the arrow below the Paste button on the ribbon. For example, you can select Formulas & Number Formatting to paste only the formula and the number formatting such as percent format, currency format, and the like:
Tip. If you are not sure which paste option works best for you, hover the mouse over different icons to see a preview of this or that paste option.
Copy formula in Excel without changing references
Excel formulas rarely occur in a spreadsheet in solitude. 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 a group of data. And if your formula contains relative cell references (without $), Excel automatically adjusts them so that each formula operates on data on its own row or column. Most of the time, this is exactly what you want. For example, if you have the formula =A1*2 in cell B1, and you copy this formula to cell B3, the formula will change to =A3*2.
But what if you want Excel to copy the formula exactly, without changing the cell references along the way? Depending on your particular task, choose one of the following solutions.
Copy or move a single formula without changing cell references
If you need to copy or move just one formula, making an exact copy is easy.
- Select the cell with the formula you want to copy.
- Select the formula in the formula bar using the mouse, and press Ctrl + C to copy it. If you want to move the formula, press Ctrl + X to cut it.
- Press the Esc key to exit the formula bar.
- Select the destination cell and press Ctl + V to paste the formula there.
Alternatively, you can enter the editing mode and copy the formula in the cell as text:
- Select a cell with the formula.
- Press F2 (or double-click the cell) to enter the editing mode.
- Select the formula in the cell using the mouse, and press Ctrl + C to copy it.
- Select the destination cell, and press Ctl+V. This will paste the formula exactly, without changing the cell references, because the formula was copied as text.
Tip. To quickly copy a formula from the above cell with no reference changed, select the cell where you want to paste the formula and press Ctrl + '.
Copy a range of formulas without changing cell references
To move or copy a range of Excel formulas so that no cell references are changed, use one of the following methods.
Method 1. Use absolute or mixed cell references
If you need to make an exact copy of formulas with relative cell references (like A1), the best way would be changing them to absolute references ($A$1) to fix the reference to a given cell, so that it remains static no matter where the formula moves. In other cases, you may need to use mixed cell references ($A1 or A$1) to lock either a column or row. Doesn't make much sense so far? Okay, let's consider the following example.
Supposing, you have a table that calculates the 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 includes an absolute cell reference ($C$2) to fix the exchange rate to cell C2, and a relative cell reference to cell B5 because you want this reference to adjust for each row. And this approach works well as long as the formulas remain in column C.
But let's see what happens if you need, say, to move the EUR prices from column C to column F. If you copy the formulas in a usual way by copying/pasting the cells, the formula from cell C5 (=B5*$C$2) will change to =D5*$C$2 when pasted in cell F5, making your calculations all wrong!
To fix this, just change a relative reference (B5) to a mixed 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, no matter where the formula moves.
And now, if you copy or move the formulas from column D to column F, or any other column, the column reference won't change because you locked it by the dollar sign ($B5).
The concept of Excel cell references may be difficult to grasp from the outset, but trust me it's worth your time and effort because it will save you much more time in the long run. For example, see how you can calculate the entire table with a single formula by using mixed cell references.
However, if you already have a ton of formulas with relative cell references in your Excel sheet, and you need to quickly make an exact copy of those formulas but you don't feel like you can get the references right, one of the following methods could be a solution.
Method 2. Copy Excel formulas without changing references via Notepad
- Enter the formula view mode by pressing the Ctrl + ` shortcut, or by using any other method described in How to show formulas in Excel.
- Select all the cells with the formulas you want to copy or move.
- Press Ctrl + C to copy the formulas, or Ctrl + X to cut them. Use the latter shortcut if you want to move formulas to a new location.
- Open Notepad or any other text editor and press Ctrl + V to paste the formulas there. Then press Ctrl + A to select all the formulas, and Ctrl + C to copy them as text.
- In your Excel worksheet, select the upper-left cell where you want to paste the formulas, and press Ctrl + V.
Notes:
- You can paste the formulas only in the same worksheet where your original formulas are located, unless the references include the sheet name, otherwise the formulas will be broken.
- The worksheet should be in formula view mode. To verify this, go to the Formulas tab > Formula Auditing group, and check if the Show Formulas button is toggled on.
- After pasting the formulas, press Ctrl + ` to toggle off the formula view mode.
Method 3. Copy formulas exactly by using Excel's Find and Replace
To copy a range of Excel formulas without changing their cell references, you can use the Excel Find and Replace feature in the following way.
- Select the cells with the formulas that you want to copy.
- On the Home tab, go to the Editing group, and click Find & Select > Replace… Or, simply press Ctrl + H, which is the shortcut for launching the Find & Replace dialog in Excel.
- In the Find & Replace dialog window, type the equal sign (=) in the Find what box. In the Replace with box, input some symbol or a string of characters that is not used in any of your formulas, like ', # or \.
The purpose of this step is to turn formulas into text strings, which will prevent Excel from changing the cell references during the copy process.
Note. Do not use an asterisk (*) or question mark (?) for replacement, since these are wildcard characters in Excel and using them would make later steps more difficult.
- Click the Replace All button and close the Find and Replace dialog. All the formulas in the selected range will turn into text strings:
- Now, you can select any cells, press Ctrl + C to copy them, select the top cell in the current worksheet where you want to paste the formulas, and press Ctrl + V. Since Excel does not interpret the formulas without the equal sign as formulas, they will be copied exactly, without changing references.
- Use Find & Replace again to reverse the change. Select both regions, with the original formulas and copied ones (to select non-adjacent regions, press and hold Ctrl). Press Ctrl + H to open the Find & Replace dialog. This time, enter the back slash (\) (or any other character you used for the first replacement) in the Find what box, and = in the Replace with box, and click the Replace All button. Done!
Shortcuts to copy Excel formula to other cells
1. Copy a formula down
Ctrl + D - Copy a formula from the cell above and adjusts the cell references.
For example, if you have a formula in cell A1 and you want to copy it to cell A2, select A2 and press Ctrl + D.
2. Copy a formula to the right
Ctrl + R - Copy a formula from the cell to the left and adjusts the cell references.
For example, if you have a formula in cell A2 and you want to copy it to cell B2, select B2 and press Ctrl + R.
Tip. Both of the above shortcuts can be used to copy formulas to multiple cells too. The trick is to select both the source cell and target cells 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 press Ctrl + D.
3. Copy a formula down exactly
Ctrl + ' - Copies a formula from the cell above to the currently selected cell exactly and leaves the cell in edit mode.
This is a quick way to make an exact copy of a formula without changing cell references. For example, to copy a formula from cell A1 to A2 so that no references are changed, select A2 and press Ctrl + '.
Note. Don't confuse the shortcut Ctrl + ' (Ctrl + single quote) that exactly copies a formula from the cell above with Ctrl + ` (Ctrl + grave accent key) that activates show formulas mode in Excel.
Well, this is all I have to say about copying formulas in Excel. If you know some other methods to quickly move or copy formula in Excel sheets, please do share. I thank you for reading and hope to see you on our blog next week!
202 comments
I have data in sheet2 as
A1=sheet1! A1
A2=sheet1! B1
A3=sheet1! C1.
Now, I need to copy/drag to whole sheet2 by giving leaving A4 blank & again as
A5=sheet1! A2
A6=sheet1! B2
A7=sheet1! C2.
Please give ideas
Thanks a lot, very useful!
Here is my issue,
I want to copy a cell from my table on my sheet2 cell B4, on my sheet 1 cell A1.
I made macro to add row with new information on my table sheet 2.
My formula for my sheet 1 cell A1 is: A1='sheet2'!B4
But every time I run my macro, my formula changing for A1='sheet2'!B5
How should I write my formula to keep A1='sheet 2'!B4 ?
Hello!
To create a reference with a permanent cell address, use the INDIRECT function:
=INDIRECT("Sheet2!B4")
or
=INDIRECT(ADDRESS(4,2,1,1,"Sheet2"))
I've got a tip.
I found out that if you use ctrl+x it will copy all of your referances to the new location. but if you want to copy all the information you ctrl+c copy the group of cells with the various relations then paste it to a new open location for temporary storage then go back to the group and ctrl+x the original group and paste it to the new desired location. then go back to the copied group and ctrl+c to copy it again and paste it back to the original location. go back to the copied group and delete it. What you are left with are two groups that reference the same cells This works for pretty much everything except things ctrl+x won't ie. dropdown list data locations.
You just saved me SO MUCH TIME. Thank you Paul!
Hello! When i drag the formula down, it doesn't adjust to the row in the formula, it only copies the original formula exactly and doesn't change to the correct row it's in. Is there a way for the formula to enable it to adjust to the specific row its in? Thank you!
Hi Patrick,
Use a relative reference for the row coordinate that needs to be changed, for example A1 (relative) and not $A$1 (absolute). For more information, please see Absolute and relative cell references in Excel.
Hello!
I can't guess what formula you are using. But I recommend that you read this guide above.
For example, if I have a cell =A1 and if I want to drag it down to be =A3 not =A2, is it possible?
how to copy/fll
c4, c8, c12... continue
Hi!
Select the range C4:C7 and copy it down. Read more about how to insert empty cells when filling down a series in this article.
Hi there,
How can I copy a formula with the return value with the original font color?
Example:
=XLOOKUP($D3,NoOwnersDec15!$P$2:$P$1799,NoOwnersDec15!$Q$2:$W$1799,"Not Found",0,1)&" "
The lookup value and the return array (NoOwnersDec15) has a red font but I'm not able to return the same font color red using the option (values and source & source formatting or keep the source formatting).
Thank you,
Rose
Hello!
If I understand the problem correctly, then Excel formulas cannot change or copy the font color. This is possible with a VBA macro.
Method 3 for copying formulas without changing cell references! The one where you change the = to /, and back again!
This is a life changer! I wish I knew this 5 years ago.
Thank you!
I wish to copy the formula =VLOOKUP(AL6,Sheet1!E4:F11,2,FALSE) all the way to the last Excel cell in the column. I wish the only thing that changes is the AL cells such that:
=VLOOKUP(AL10,Sheet1!E4:F11,2,FALSE)
=VLOOKUP(AL2102,Sheet1!E4:F11,2,FALSE)
=VLOOKUP(AL10147,Sheet1!E4:F11,2,FALSE)
Please assist
Hi!
Simply, lock the array with the $ sign (absolute reference) and drag the formula down to copy it to the below cells:
=VLOOKUP(AL10,Sheet1!$E$4:$F$11,2,FALSE)
Hello
I want to copy cell data from say Sheet1'A2 and paste it to Sheet2'A2, the copied data will however have a formula. Is there a way I can copy the Same data from Sheet2'A2 and paste it to Sheet3'A2 as Values without having to do it manually?
"Sheet1 has lots of columns and the final data doesn't need all columns, that's where Sheet2 comes in to format the data with the required columns and arrangement."
Hi everyone!
Hope your are doing great...
Please help me out to resolve my query.. i need to paste the formula reference cell in another sheet in filtered cells but it changes the sequence rows..
for example:
Sheet 1:
Row# Column#
A
1 500
2 900
3 1100
Sheet : 2 (Filtered cells)
Row# Column#
P
1 ='sheet 1'!A1 (500)
3 ='sheet 1'!A3 (1100)
4 ='sheet 1'!A4 (0)
Kindly resolve it
Hi everyone!
Hope your are doing great...
Please help me out to resolve my query.. i need to paste the formula reference cell in another sheet in filtered cells but it changes the sequence rows..
for example:
Sheet 1: Sheet : 2 (Filtered cells)
Row# Column# Row# Column#
A P
1 500 1 ='sheet 1'!A1 (500)
2 900 3 ='sheet 1'!A3 (1100)
3 1100 4 ='sheet 1'!A4 (0)
Kindly resolve it
I am trying to select multiple cells (B2, H2, I2, J2, N2 & R2) from my S/S and insert the information into cell C2. I am using the below formula but it is not working. I need to add in mm after the numbers from Cells H2, I2 & J2 for measurements. Then also put line breaks in after each Cell.
=B2,(IF(H2="NUMBER", H2&"mm")CHAR(10),IF(I2="NUMBER",I2&"mm")CHAR(10),IF(J2="number",J2&'mm",))) CHAR(10),N2,CHAR(10),R2
I don't know what I am doing incorrectly but it is coming up as Error.
Hello!
I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:
=IF(ISNUMBER(B2),B2&"mm "&CHAR(10),"")&IF(ISNUMBER(H2),H2&"mm "&CHAR(10),"")&IF(ISNUMBER(I2),I2&"mm "&CHAR(10),"")
That worked fantastically and was just what i was after. Thank you so much :)
Hi
I want to copy formula and past it but I don't want to change column reference.
example I want to multiply B2=A2*B1 and I copy that and past it to multiple cells for example I past it to B3 and I get B3=A3*B2 but I want it to be B3=A3*B1, B4=A4*B1can I past formula without changing column reference.
Hi!
Pay attention to the following paragraph of the article above — Copy a range of formulas without changing cell references
Excel 2016 crashes when coping formulas in the formula view mode, even under safe mode. How to copy formulas of selected cells into the clipboard to paste into another editor for transposing?
Hello... Hoping for some guidance here...
In my case, A1 is |=NOW()|, B2 is |16:00:00| and B3 is the difference between the two |=A1-B1|. How do i convert B3 into decimal? I know the formula is |=B2*24|, but that is not giving me the actual result. I have tried conditional formatting for B3, but it is not giving me the actual result.
The one that works for me is when i manually enter, for example, |00:45:00| in E2 and E3 shows |0.75| instantly (E3 for formula is =E2*24) and conditional formatting for E2 is Custom>h:mm:ss
What am i missing here?
Hello!
I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:
=(B3-INT(B3))*24
If this is not what you wanted, please explain the problem in more detail.
no worries... here it is... i start work at 1600 hrs. (B1). A1 is =NOW(). B3 is the time i have spent in my shift. hence A1-B1. for example if it is 2009 hrs. at the moment, B2 will show 4:09 (without any conditional formatting). i want B2 in decimals.
now.... if i manually enter 4:09 in C1 and add =C1*24 formula on C2, i will get 0.75 as a result. reason? coz i added conditional formatting to C1 as Custom > h:mm:ss and C2 as Number
I need B2 (shift hours worked) in decimals...
Hi!
Explain why 4 hours 09 minutes is 0.75? As a number, this is 4.15
What result do you want to get exactly?
Read how to convert time to decimal number, hours, minutes or seconds in Excel in this article.
I m sorry. 04:09 is indeed 4.15 in decimals *when you type 04:09 manually*. i want difference of time now & 16:00:00 in decimals.
i have been through the article. maybe i did not pick up what i need.
I have a daily report that part of it has an opening number and closing number for the day i.e. a totalizator. Each sheet is ID as 1st, 2nd...31st. I need day 2 to pick up the closing number from day 1 as the opening number and day 3 to pick up the closing number from day 2,, etc. I can easily write in the cell appropriate cell in day 2. =1stL36 which will bring over the closing number from day 1 to the opening number in day 2. Long winded how do I copy and paste this to all the cells at one time so it will update the sheet? When I copy and paste the formula keeps the first sheet 1st. I just do not want to edit everyday. There are more items that have the same process so it is not like I only have to edit this one event.
Thanks
Hello!
If your worksheets are named 1,2,3, etc., this formula will return a reference to cell L36 of the previous worksheet.
=INDIRECT("'"&(--RIGHT(CELL("filename"), LEN(CELL("filename")) - SEARCH("]",CELL("filename"),1))-1)&"'!L36")
I hope it’ll be helpful.
Hi,
I'm using an autotext/shortcuts app to insert common used text and formulas into cells. I use the following formula a lot: =E2&" "&F2&" "&C2 to get the text of those 3 cells into one cell. The problem is I have to manually edit the row number.
How can I write the formula so it dynamically changes to the current row it is pasted in (the column must stay the same), for example if I paste it in A9 the formula must be =E9&" "&F9&" "&C9 & if I paste it in A11 then =E1&" "&F11&" "&C11 etc.
Thank you
Hi,
Please check out the following article on our blog, it’ll be sure to help you with your task: Relative and absolute cell reference: why use $ in Excel formula.
Hope you’ll find this information helpful.
Hi there,
I am doing a large data entry and need help.
For one of my columns I need to copy the value to the cell underneath it, but I need this to that for the entire column.
For example, I need it to copy every other line, if that makes since. So for line 4 I need it to automatically fill from line 3. So 3 and 4 should both have 11. Both 5 and 6 should have 10. What would the formula be for this and how do I implement it? thanks in advance
1.12
2.12
3.11
4. blank
5.10
6.blank
Hello!
The formula copies the value from the odd line to the next even line.
=IFERROR(SUMPRODUCT(A1,--(MOD(ROW(),2)=0)),"")
After that you can copy this formula down along the column. I hope it’ll be helpful.