How to copy formula in Excel down the column

Download Article Written by Nicole Levine, MFA Last Updated: June 29, 2021 References Tested Download Article

How to copy formula in Excel down the column

Download Article   Written by Nicole Levine, MFA

Last Updated: June 29, 2021 References Tested

Download Article   ARTICLE VIDEO   X

This article was written by Nicole Levine, MFA. Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.

The wikiHow Tech Team also followed the article's instructions and verified that they work.

This article has been viewed 490,411 times.

Excel makes it easy to copy your formula across an entire row or column, but you don't always get the results you want. If you end up with unexpected results, or those awful #REF and /DIV0 errors, it can be extremely frustrating. But don't worryyou won't need to edit your 5,000 line spreadsheet cell-by-cell. This wikiHow teaches you easy ways to copy formulas to other cells.

Steps

Method 1Method 1 of 4:Using Find and Replace to Copy Formulas Exactly

1Open your workbook in Excel. Sometimes, you have a large spreadsheet full of formulas, and you want to copy them exactly. Changing everything to absolute cell references would be tedious, especially if you just want to change them back again afterward. Use this method to quickly move formulas with relative cell references elsewhere without changing the references.[1] X Research source Go to source  In our example spreadsheet, we want to copy the formulas from column C to column D without changing anything.


Example Spreadsheet Column A Column B Column C Column D row 1

944

Frogs

=A1/2  row 2

636

Toads

=A2/2  row 3

712

Newts

=A3/2  row 4

690

Snakes

=A4/2

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

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

3Find and replace "=" with another character. Type "=" into the "Find what" field, and then type a different character into the "Replace with" box. Click Replace All to turn all formulas (which always begin with an equal's sign) into text strings beginning with some other character. Always use a character that you have not used in your spreadsheet. For example, replace it with # or &, or a longer string of characters, such as ##&.


Example Spreadsheet Column A Column B Column C Column D row 1

944

Frogs

##&A1/2  row 2

636

Toads

##&A2/2  row 3

712

Newts

##&A3/2  row 4

690

Snakes

##&A4/2

  • Do not use the characters * or?, since these will make later steps more difficult.

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


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 4

690

Snakes

##&A4/2

##&A4/2

5Use Find & Replace again to reverse the change. Now that you have the formulas where you want them, use "Replace All" again to reverse your change. In our example, we'll look for the character string "##&" and replace it with "=" again, so 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 4

690

Snakes

=A4/2

=A4/2

6Try alternate methods. If the method described above doesn't work for some reason, or if you are worried about accidentally changing other cell contents with the "Replace all" option, there are a couple other things you can try:

  • To copy a single cell's formula without changing references, select the cell, then copy the formula shown in the formula bar near the top of the window (not in the cell itself). Press Esc to close the formula bar, then paste the formula wherever you need it.
  • Press Ctrl and ` (usually on the same 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 again, then paste them back into the spreadsheet at the desired location. Then, press Ctrl and `  again to switch back to regular viewing mode.

Method 2Method 2 of 4:Filling a Column or Row with a Formula

1Type a formula into a blank cell. Excel makes it easy to propagate a formula down a column or across a row by "filling" the cells. As with any formula, start with an = sign, then use whichever functions or arithmetic you'd like. We'll use a simple example spreadsheet, and add column A and column B together. Press Enter or Return to calculate the formula.


Example Spreadsheet Column A Column B Column C row 1

10

9

19  row 2

20

8  row 3

30

7  row 4

40

6

2Click the lower right corner of the cell with the formula you want to copy. The cursor will become a bold + sign.

3Click and drag the cursor across the column or row you're copying to.  The formula you entered will automatically be entered into the cells you'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 Column C row 1

10

9

=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

19  row 2

20

8

28  row 3

30

7

37  row 4

40

6

46

  • You can also double-click the plus sign to fill the entire column instead of dragging. Excel will stop filling out the column if it sees an empty cell. If the reference data contains a gap, you will have to repeat this step to fill out the column below the gap.
  • Another way to fill the entire column with the same formula is to select the cells directly below the one containing the formula and then press Ctrl + D.[2] X Research source Go to source

Method 3Method 3 of 4:Copying a Formula into Multiple Cells by Pasting

1Type the formula into one cell. As with any formula, start with an = sign, then use whichever functions or arithmetic you'd like. We'll use a simple example spreadsheet, and add column A and column B together. When you press Enter or Return, the formula will calculate.


Example Spreadsheet Column A Column B Column C row 1

10

9

19  row 2

20

8  row 3

30

7  row 4

40

6

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

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

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

Method 4Method 4 of 4:Using Relative and Absolute Cell References

1Use a relative cell reference in a formula.  In an Excel formula, a "cell reference" is the address a cell. You can type 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*2  row 3

100  row 4

200  row 5

400

2Understand why they're called relative references. In an Excel formula, a relative reference uses the relative position of a 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 copy the formula into C4, then it will still 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 3

100  row 4

200

=A4*2  row 5

400

  • This works for cells outside of the same row and column as 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 column to the right (CD) and 5 rows below (27), or "B7".

3Use an absolute reference instead. Let's say you don't want Excel to automatically change your formula. Instead of using a relative cell reference, you can make it absolute by adding a $ symbol in front of the column or row that you want to keep the same, no matter where you copy the formula too.[3] X Research source Go to source  Here are a few example spreadsheets, showing the original formula in larger, bold text, and the result when you copy-paste it to other cells:

  • Relative Column, Absolute Row (B$3): The formula has an absolute reference to row 3, so it always refers to row 3:

    Column A Column B Column 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$3

=B$3  

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

    Column A Column B Column 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$1): The formula has an absolute reference to column B of row 1, so it always refers to column B of row 1.

    Column A Column B Column C row 1

50

7

=$B$1 row 2

100

=$B$1

=$B$1  row 3

200

=$B$1

=$B$1  row 4

400

=$B$1

=$B$1  

4Use the F4 key to switch between absolute and relative. Highlight a cell reference in a formula by clicking it and press F4 to automatically add or remove $ symbols. Keep pressing F4 until the absolute or relative references you'd like are selected, then press Enter or Return.

Community Q&A

  • Question When I try to pull down formula, it stays the same and does not change with row, what can I do?

Community Answer Go to Formulas, Calculation Options, and change them from Manual to Automatic.

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

Krisztian Toth Community Answer Right after the drag 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 week day as well as date) so 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+V.

Video

By using this service, some information may be shared with YouTube.

Tips

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

Warnings

  • Different versions of Excel may not show exactly the same screenshots in the same ways as are displayed here.

You Might Also Like

Use Summation Formulas in Microsoft Excel

How toUse Summation Formulas in Microsoft Excel

Write a Simple Macro in Microsoft Excel

How toWrite a Simple Macro in Microsoft Excel

Make a Spreadsheet in Excel

How toMake a Spreadsheet in Excel

Make a Bar Graph in Excel

How toMake a Bar Graph in Excel

Unprotect an Excel Sheet

How toUnprotect an Excel Sheet

Create a Mortgage Calculator With Microsoft Excel

How toCreate a Mortgage Calculator With Microsoft Excel

Unhide Rows in Excel

How toUnhide Rows in Excel

Create a Graph in Excel

How toCreate a Graph in Excel

Change from Lowercase to Uppercase in Excel

How toChange from Lowercase to Uppercase in Excel

Convert Notepad to Excel

How toConvert Notepad to Excel

Open a Password Protected Excel File

How toOpen a Password Protected Excel File

Link Sheets in Excel

How toLink Sheets in Excel

Create a Drop Down List in Excel

How toCreate a Drop Down List in Excel

Download Microsoft Excel

How toDownload Microsoft Excel

References

  1. http://www.extendoffice.com/documents/excel/598-excel-copy-without-changing-formulas.html
  2. https://www.excel-exercise.com/ctrl-d-the-secret-revealed/
  3. http://office.microsoft.com/en-us/excel-help/move-or-copy-a-formula-HP010102342.aspx
  4. http://www.pryor.com/blog/copy-excel-formulas-down-to-fill-a-column/
  5. http://office.microsoft.com/en-us/excel-help/find-or-replace-text-and-numbers-on-a-worksheet-HP001216390.aspx

About This Article

Written by: Nicole Levine, MFA wikiHow Technology Writer This article was written by Nicole Levine, MFA. Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.  This article has been viewed 490,411 times.   Co-authors:  25 Updated: June 29, 2021 Views:490,411 Categories: Featured Articles | Microsoft Excel Article SummaryX

To copy a formula into multiple adjoining cells in Microsoft Excel, type the formula into a cell, and then press Enter or Return to calculate it. Hover your mouse cursor over the bottom-right corner of the cell so the cursor turns to a crosshair, then drag the crosshair down 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 copy a formula to cells that aren't touching the formula cell, click the cell once to select it, and then press Control + C (on a PC) or Command + C (on a Mac) to copy 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 it into the selected cells. In other languagesРусский:копировать формулы в Excel Español:copiar fórmulas en Excel Italiano:Copiare Formule in Excel Français:copier des formules dans Excel Bahasa Indonesia:Menyalin Rumus di Excel Português:Copiar Fórmulas no Excel Nederlands:Formules kopiëren in Excel العربية:نسخ الصيغ الحسابية في برنامج إكسل हिन्दी:एक्सेल (Excel) में फॉर्मूला कॉपी करें (Copy Formulas in Excel) Tiếng Việt:Sao chép công thức trong Excel 日本語:Excelで数式をコピーする ไทย:คัดลอกสูตร (Formulas) ใน Excel Türkçe:Excel'de Formüller Nasıl Kopyalanır 中文:在Excel中复制公式 한국어:엑셀에서 수식을 복사하는 법

  • Print
  • Send fan mail to authors Thanks to all authors for creating a page that has been read 490,411 times.

Reader Success Stories

Baskar Dhanagopalan

Baskar Dhanagopalan

Jul 16, 2016 "The trick to change the '=' to '##' for copying formulas from one cell to another without changing the reference is wonderful. That helps me a lot."

Bonney Bennett

Bonney Bennett

Jul 13, 2016 "Wow! I needed to copy the exact formulas several times. By using the find/replace for the "=" sign, it was a piece of cake!"

Tracey Rucker

Tracey Rucker

Jan 19, 2017 "I was drawing a complete blank how to copy a formula across a row.  Now I know two ways! "

Anonymous

Anonymous

Nov 9, 2016 "The details and the screenshot pictures definitely made it easier to follow along."

Joe

Joe

Dec 17, 2016 "I regularly use the site, it is fantastic."

Abdelrahman Mohammed

Abdelrahman Mohammed

Aug 9, 2016 "Totally helped me, thank you, wikiHow."

Felix Gwesele

Felix Gwesele

Sep 28, 2016 "Easy to understand and it worked."

Rohit Kumar

Rohit Kumar

Aug 17, 2016 "It is most helpful for students."     More reader stories Hide reader stories

Is this article up to date? Yes No  Thanks for sharing feedback!  Read more articles like this one. Thanks for sharing feedback!  Read more articles like this one. We're sorry this article wasnt helpful. Here are some similar articles that might be more helpful to you. We're sorry this article wasnt helpful. Here are some similar articles that might be more helpful to you.

Video liên quan