How Do You Copy A Formula In Excel

Download Commodity

Download Article

Excel makes information technology easy to copy your formula across an entire row or column, only y'all don't always become the results you desire. If you end up with unexpected results, or those atrocious #REF and /DIV0 errors, information technology can be extremely frustrating. Only don't worry—you won't need to edit your 5,000 line spreadsheet jail cell-by-cell. This wikiHow teaches you easy ways to re-create formulas to other cells.

Open your workbook in Excel. Sometimes, you lot have a large spreadsheet full of formulas, and you want to copy them exactly. Changing everything to absolute prison cell references would be tedious, especially if you simply want to modify them back once more later on. Utilise this method to speedily motion formulas with relative cell references elsewhere without changing the references.^[1] In our example spreadsheet, nosotros want to copy the formulas from cavalcade C to column D without changing annihilation.

*Case Spreadsheet*
	Cavalcade A	Cavalcade B	Column C
row 1	944	Frogs	=A1/2
row 2	636	Toads	=A2/two
row iii	712	Newts	=A3/2
row four	690	Snakes	=A4/two

If you're just trying to copy the formula in a single jail cell, skip to the concluding step ("Effort alternate methods") in this section.

2

Press Ctrl+H to open up the Discover window. The shortcut is the aforementioned on Windows and macOS.

Advertisement

Find and replace "=" with another grapheme. Type "=" into the "Find what" field, and and so blazon a different character into the "Supervene upon with" box. Click Replace All to turn all formulas (which always begin with an equal'southward sign) into text strings outset with another character. Always use a character that yous have not used in your spreadsheet. For example, supplant it with # or &, or a longer cord of characters, such every bit ##&.

*Example Spreadsheet*
	Cavalcade A	Column B	Cavalcade C
row i	944	Frogs	##&A1/2
row 2	636	Toads	##&A2/2
row three	712	Newts	##&A3/2
row 4	690	Snakes	##&A4/2

Do not use the characters * or ?, since these volition make after steps more than difficult.

Copy and paste the cells. Highlight the cells you want to re-create, and so printing Ctrl + C (PC) or Cmd + C (Mac) to re-create them. And then, select the cells y'all want to paste into, and printing Ctrl + 5 (PC) or Cmd + V (Mac) to paste. Since they are no longer interpreted as formulas, they will exist copied exactly.

*Instance Spreadsheet*
	Column A	Column B	Column C	Column D
row 1	944	Frogs	##&A1/2	##&A1/ii
row two	636	Toads	##&A2/2	##&A2/2
row 3	712	Newts	##&A3/two	##&A3/2
row 4	690	Snakes	##&A4/2	##&A4/ii

Use Find & Replace again to reverse the change. Now that you have the formulas where yous want them, use "Replace All" again to reverse your change. In our example, we'll wait for the character string "##&" and supersede information technology with "=" again, and so those cells go formulas once once more. You lot can now go on editing your spreadsheet as usual:

*Example Spreadsheet*
	Cavalcade A	Column B	Column C	Column D
row i	944	Frogs	=A1/2	=A1/two
row ii	636	Toads	=A2/two	=A2/2
row iii	712	Newts	=A3/2	=A3/2
row four	690	Snakes	=A4/two	=A4/2

6
Endeavour alternate methods. If the method described above doesn't piece of work for some reason, or if you are worried about accidentally changing other jail cell contents with the "Replace all" option, at that place are a couple other things you can try:
- To re-create a single cell'southward formula without changing references, select the cell, and then copy the formula shown in the formula bar near the top of the window (not in the cell itself). Press Esc to shut the formula bar, then paste the formula wherever y'all need it.
- Press Ctrl and ` (normally on the same key equally ~) to put the spreadsheet in formula view mode. Re-create the formulas and paste them into a text editor such as Notepad or TextEdit. Copy them again, then paste them dorsum into the spreadsheet at the desired location. Then, printing Ctrl and ` over again to switch back to regular viewing mode.
Advertisement

ane

Blazon a formula into a bare cell. Excel makes it easy to propagate a formula downward a column or beyond a row by "filling" the cells. Every bit with whatsoever formula, start with an = sign, and so utilise whichever functions or arithmetic you'd similar. We'll apply a simple example spreadsheet, and add together column A and column B together. Press Enter or Return to calculate the formula.

*Example Spreadsheet*
	Cavalcade A	Column B	Cavalcade C
row 1	x	9	19
row 2	20	8
row 3	thirty	7
row four	forty	6

2

Click the lower right corner of the jail cell with the formula y'all want to copy. The cursor will get a bold + sign.

Click and elevate the cursor across the column or row you're copying to. The formula you entered will automatically be entered into the cells y'all've highlighted. Relative cell references will automatically update to refer to the cell in the same relative position rather than stay exactly the same. Here's our example spreadsheet, showing the formulas used and the results displayed:

*Example Spreadsheet*
	Column A	Column B	Cavalcade C
row 1	10	nine	=A1+B1
row 2	20	8	=A2+B2
row 3	30	7	=A3+B3
row 4	40	6	=A4+B4

*Example Spreadsheet*
	Column A	Column B	Column C
row 1	10	9	xix
row 2	twenty	eight	28
row iii	30	7	37
row 4	twoscore	6	46

Y'all can also double-click the plus sign to fill up the entire column instead of dragging. Excel will stop filling out the column if it sees an empty prison cell. If the reference data contains a gap, yous will have to repeat this step to make full out the column below the gap.
Another way to fill the unabridged cavalcade with the aforementioned formula is to select the cells direct beneath the 1 containing the formula and then printing Ctrl + D.^[ii]

one

Type the formula into one jail cell. Equally with any formula, start with an = sign, then employ whichever functions or arithmetics you'd like. We'll use a elementary case spreadsheet, and add column A and column B together. When you printing Enter or Return, the formula will calculate.

*Example Spreadsheet*
	Cavalcade A	Cavalcade B	Column C
row 1	10	9	19
row 2	xx	eight
row 3	30	vii
row 4	40	6

2

Select the prison cell and press Ctrl+C (PC) or ⌘ Control+C (Mac). This copies the formula to your clipboard.
3

Select the cells you desire to copy the formula to. Click on 1 and drag upwardly or downward using your mouse or the arrow keys. Unlike with the cavalcade or row fill method, the cells you are copying the formula to do not need to exist adjacent to the cell you lot are copying from. You lot can hold down the Control key while selecting to copy non-adjacent cells and ranges.
4

Printing Ctrl+V (PC) or ⌘ Control+V (Mac) to paste. The formulas now appear in the selected cells.

Advertising

Apply a relative cell reference in a formula. In an Excel formula, a "jail cell reference" is the address a cell. You tin can type these in manually, or click on the prison cell you wish to use while you lot are entering a formula. For example, the following spreadsheet has a formula that references jail cell A2:

*Relative References*
	Column A	Cavalcade B	Column C
row 2	50	7	=A2*2
row 3	100
row iv	200
row five	400

two

Understand why they're called relative references. In an Excel formula, a relative reference uses the relative position of a prison cell address. In our example, C2 has the formula "=A2", which is a relative reference to the value two cells to the left. If you re-create the formula into C4, then it volition yet refer to two cells to the left, now showing "=A4".

*Relative References*
	Column A	Column B	Column C
row 2	50	7	=A2*2
row iii	100
row 4	200		=A4*2
row five	400

This works for cells outside of the same row and column every bit well. If you copied the same formula from cell C1 into cell D6 (not shown), Excel would change the reference "A2" to a cell one cavalcade to the right (C→D) and 5 rows below (ii→vii), or "B7".

Use an absolute reference instead. Let'south say you don't want Excel to automatically change your formula. Instead of using a relative cell reference, yous tin make it absolute by adding a $ symbol in forepart of the column or row that you want to keep the aforementioned, no matter where y'all re-create the formula too.^[3] Here are a few example spreadsheets, showing the original formula in larger, bold text, and the effect when y'all copy-paste information technology to other cells:

Relative Cavalcade, Absolute Row (B$three): The formula has an absolute reference to row 3, so information technology always refers to row 3:

	Column A	Column B	Cavalcade C
row 1	50	7	= B$3
row 2	100	=A$3	=B$3
row 3	200	=A$3	=B$3
row 4	400	=A$iii	=B$3

Absolute Cavalcade, Relative Row ($B1): The formula has an absolute reference to column B, so it always refers to column B.

	Cavalcade A	Cavalcade B	Cavalcade C
row 1	50	7	= $B1
row 2	100	=$B2	=$B2
row 3	200	=$B3	=$B3
row 4	400	=$B4	=$B4

Absolute Column & Row ($B$i): The formula has an absolute reference to column B of row 1, so information technology e'er refers to column B of row 1.

	Cavalcade A	Column B	Cavalcade C
row 1	50	7	= $B$i
row ii	100	=$B$1	=$B$i
row 3	200	=$B$i	=$B$1
row four	400	=$B$1	=$B$one

4

Utilise the F4 key to switch between accented and relative. Highlight a cell reference in a formula past clicking it and press F4 to automatically add together or remove $ symbols. Keep pressing F4 until the accented or relative references yous'd similar are selected, then press Enter or Return.

Advertising

Add together New Question

Question

When I effort to pull down formula, information technology stays the same and does not change with row, what tin can I do?

Get to Formulas, Calculation Options, and change them from Manual to Automatic.
Question

When I click and elevate, it copies the format as well. I don't desire to copy the format, just the formula?

Krisztian Toth

Community Answer

Right afterwards the drag there should exist an icon in the lower correct corner of the highlighted area. Hover over that and select from the diverse make full options, among which y'all tin can discover an pick to fill without format.
Question

How do I copy a date formula I have created (that includes the week day equally well equally date) and then that it runs in sequence?

Krisztian Toth

Community Answer

Double click into the cell, copy your formula, double click into the destination cell, then press Ctrl+V or Command+Five.

Ask a Question

200 characters left

Include your email address to get a bulletin when this question is answered.

Submit

VideoRead Video Transcript

If you copy a formula to a new cell and see a green triangle, Excel has detected a possible error. Examine the formula advisedly to see if anything went wrong.^[4]
If you accidentally did replace the = character with ? or * in the "copying a formula exactly" method, searching for "?" or "*" will not give you lot the results you expect. Correct this by searching for "~?" or for "~*" instead.^[v]
Select a cell and press Ctrl ' (apostrophe) to fill up it with the formula straight above information technology.

Advertizing

Different versions of Excel may not prove exactly the same screenshots in the same means as are displayed here.

Advertizing

About This Article

Article Summary Ten

To re-create a formula into multiple adjoining cells in Microsoft Excel, type the formula into a cell, and then press Enter or Return to summate it. Hover your mouse cursor over the bottom-right corner of the prison cell and so the cursor turns to a crosshair, then drag the crosshair down to copy the formula to other cells in the cavalcade. If you lot'd rather copy the formula to cells in a row, drag the crosshair left or right. To copy a formula to cells that aren't touching the formula jail cell, click the prison cell once to select it, and and so press Control + C (on a PC) or Command + C (on a Mac) to re-create the formula. Now, select the cell or cells you want to copy the formula to, then press Control + V (on a PC) or Command + V (on a Mac) to paste information technology into the selected cells.

Did this summary help you?

Thank you to all authors for creating a folio that has been read 486,240 times.