How To Get Rid Of Formulas In Excel

Download Article

Excel makes information technology like shooting fish in a barrel to copy your formula across an entire row or cavalcade, but yous don't always get the results you desire. If you end up with unexpected results, or those awful #REF and /DIV0 errors, it tin can be extremely frustrating. But don't worry—you won't need to edit your 5,000 line spreadsheet cell-past-cell. This wikiHow teaches you easy ways to copy formulas to other cells.

Open your workbook in Excel. Sometimes, yous have a large spreadsheet full of formulas, and you lot want to re-create them exactly. Irresolute everything to absolute prison cell references would be tedious, especially if you just want to modify them back over again afterward. Employ this method to rapidly motility formulas with relative cell references elsewhere without changing the references.^[i] In our example spreadsheet, we want to copy the formulas from column C to column D without changing anything.

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

If you're but trying to re-create the formula in a single cell, skip to the last step ("Try alternate methods") in this section.

two

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

Advertisement

three

Find and supervene upon "=" with some other character. Type "=" into the "Notice what" field, and then type a different character into the "Supplant with" box. Click Replace All to turn all formulas (which ever brainstorm with an equal's sign) into text strings get-go with some other character. Always utilize a character that y'all have not used in your spreadsheet. For example, supervene upon it with # or &, or a longer string of characters, such as ##&.

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

Do not utilize the characters * or ?, since these will brand later steps more hard.

Copy and paste the cells. Highlight the cells y'all want to copy, then press Ctrl + C (PC) or Cmd + C (Mac) to copy them. Then, select the cells you want to paste into, and printing Ctrl + 5 (PC) or Cmd + V (Mac) to paste. Since they are no longer interpreted every bit formulas, they will be copied exactly.

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

Use Find & Supervene upon once again to reverse the change. At present that you lot have the formulas where you want them, apply "Replace All" again to contrary your change. In our example, we'll expect for the character string "##&" and supervene upon information technology with "=" again, and then those cells become formulas once again. You can now continue editing your spreadsheet as usual:

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

half dozen
Attempt alternate methods. If the method described above doesn't work for some reason, or if you are worried about accidentally changing other prison cell contents with the "Replace all" selection, there are a couple other things you lot can endeavour:
- To re-create a unmarried jail cell'southward formula without irresolute references, select the jail cell, then copy the formula shown in the formula bar near the meridian of the window (not in the cell itself). Press Esc to close the formula bar, then paste the formula wherever yous need information technology.
- Printing Ctrl and ` (usually on the aforementioned key as ~) to put the spreadsheet in formula view mode. Copy the formulas and paste them into a text editor such as Notepad or TextEdit. Copy them once more, and then paste them dorsum into the spreadsheet at the desired location. Then, press Ctrl and ` again to switch back to regular viewing way.
Advertisement

Type a formula into a blank cell. Excel makes it piece of cake to propagate a formula down a column or across a row past "filling" the cells. As with whatsoever formula, starting time with an = sign, then use whichever functions or arithmetics you lot'd like. We'll use a elementary example spreadsheet, and add column A and column B together. Press Enter or Render to calculate the formula.

*Example Spreadsheet*
	Column A	Cavalcade B	Column C
row ane	10	ix	19
row 2	20	viii
row three	30	vii
row 4	xl	6

two

Click the lower right corner of the jail cell with the formula you lot desire to copy. The cursor volition become a bold + sign.

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

*Case Spreadsheet*
	Column A	Cavalcade B	Column C
row 1	ten	nine	=A1+B1
row 2	20	8	=A2+B2
row 3	thirty	7	=A3+B3
row 4	40	half-dozen	=A4+B4

*Example Spreadsheet*
	Column A	Column B	Column C
row 1	x	9	xix
row 2	20	viii	28
row 3	30	7	37
row 4	40	6	46

You tin can also double-click the plus sign to fill the entire cavalcade instead of dragging. Excel will terminate filling out the column if it sees an empty cell. If the reference data contains a gap, you will accept to repeat this footstep to fill out the column beneath the gap.
Another way to fill the entire column with the same formula is to select the cells directly beneath the 1 containing the formula and so printing Ctrl + D.^[2]

Type the formula into ane cell. Every bit with whatever formula, start with an = sign, then employ whichever functions or arithmetics y'all'd like. We'll employ a simple example spreadsheet, and add column A and cavalcade B together. When you lot press Enter or Render, the formula will calculate.

*Instance Spreadsheet*
	Cavalcade A	Column B	Column C
row 1	10	9	19
row two	20	8
row 3	xxx	7
row 4	40	6

ii

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

Select the cells you want to copy the formula to. Click on i and drag up or down using your mouse or the arrow keys. Unlike with the column or row fill method, the cells y'all are copying the formula to do not need to exist adjacent to the cell you are copying from. You tin can hold down the Control key while selecting to copy non-adjacent cells and ranges.
four

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

Advertisement

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

*Relative References*
	Column A	Column B	Column C
row 2	50	7	=A2*two
row three	100
row four	200
row 5	400

two

Understand why they're chosen relative references. In an Excel formula, a relative reference uses the relative position of a prison cell accost. In our example, C2 has the formula "=A2", which is a relative reference to the value two cells to the left. If yous re-create the formula into C4, then information technology will notwithstanding refer to two cells to the left, now showing "=A4".

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

This works for cells outside of the same row and column as well. If yous copied the same formula from cell C1 into cell D6 (not shown), Excel would change the reference "A2" to a jail cell i column to the right (C→D) and 5 rows beneath (two→7), or "B7".

Use an absolute reference instead. Let's say you don't want Excel to automatically modify your formula. Instead of using a relative cell reference, you can arrive absolute by calculation a $ symbol in front of the column or row that yous desire to continue the same, no matter where you copy the formula too.^[three] Hither are a few example spreadsheets, showing the original formula in larger, assuming text, and the upshot when you copy-paste it to other cells:

Relative Column, Absolute Row (B$3): The formula has an absolute reference to row 3, and then information technology always refers to row 3:

	Column A	Column B	Column C
row 1	l	7	= B$3
row 2	100	=A$3	=B$3
row 3	200	=A$3	=B$3
row 4	400	=A$3	=B$3

Accented Column, Relative Row ($B1): The formula has an absolute reference to cavalcade B, and so it always refers to column B.

	Column A	Cavalcade B	Column C
row 1	l	vii	= $B1
row 2	100	=$B2	=$B2
row iii	200	=$B3	=$B3
row 4	400	=$B4	=$B4

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

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

4

Utilize the F4 key to switch between absolute and relative. Highlight a prison cell reference in a formula past clicking it and press F4 to automatically add or remove $ symbols. Keep pressing F4 until the accented or relative references y'all'd like are selected, then press Enter or Return.

Advertisement

Add New Question

Question

When I try to pull down formula, it stays the aforementioned and does not alter with row, what can I practice?

Become to Formulas, Calculation Options, and modify them from Transmission to Automatic.
Question

When I click and drag, it copies the format besides. I don't want to copy the format, just the formula?

Krisztian Toth

Community Answer

Right after the elevate there should be an icon in the lower right corner of the highlighted area. Hover over that and select from the various fill options, among which you can find an option to fill without format.
Question

How do I copy a date formula I have created (that includes the calendar week day as well every bit date) and then that information technology runs in sequence?

Krisztian Toth

Community Answer

Double click into the jail cell, re-create your formula, double click into the destination cell, and then printing Ctrl+V or Control+V.

Inquire a Question

200 characters left

Include your electronic mail address to get a message when this question is answered.

Submit

VideoRead Video Transcript

If you lot re-create a formula to a new prison cell and come across a dark-green triangle, Excel has detected a possible error. Examine the formula carefully to see if anything went wrong.^[4]
If you accidentally did supplant the = character with ? or * in the "copying a formula exactly" method, searching for "?" or "*" will not requite you the results you lot expect. Correct this by searching for "~?" or for "~*" instead.^[5]
Select a cell and press Ctrl ' (apostrophe) to fill it with the formula direct higher up it.

Advertizement

Different versions of Excel may non show exactly the same screenshots in the same ways as are displayed hither.

Nigh This Article

Article Summary Ten

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

Did this summary help you?

Thanks to all authors for creating a page that has been read 486,936 times.

Is this article up to appointment?

Source: https://www.wikihow.com/Copy-Formulas-in-Excel

Posted by: bookercantences88.blogspot.com

How To Get Rid Of Formulas In Excel

VideoRead Video Transcript

Nigh This Article

Is this article up to appointment?

0 Response to "How To Get Rid Of Formulas In Excel"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel