Learn to copy formulas across multiple cells in a column, down the column to cells that are not adjacent and copy formulas using reference to cells that are mixed or absolute and more.
Formulas are copied is among the most commonly used and simple tasks you can do in spreadsheets which is based primarily on formulas. Instead of typing the same formula repeatedly in Excel it is possible to quickly copy and paste a formula in one area into several cells.
When you’ve written a formula in Excel it is possible to use to copy or paste command for multiple cells multiple adjacent cells, or even whole columns. If you don’t use it correctly, you’ll end up with the dreadful #REF and the /DIV0 error. This article will explain the various methods you can copy formulas into Excel.
Table of Contents
How to Copy and Paste Formulas in Excel
Microsoft Excel offers a variety of ways to copy formulas using absolute cell references, relative cell references, and mixed reference.
- Copy formulas from one cell to the next
- Copy formula one cell into multiple cells
- Copying formula across the entire column
- Formula copying without formatting
- Copy formulas into non-adjacent cells
- Copy formulas and formulas, without changing cell references
How do I copy the Formula from one Cell to another in Excel
Sometimes, you might want to copy the formula from one cell to the next in Excel to avoid typing the entire formula all over to save time doing it.
Let’s say that we are using this table.
There are a few ways to copy formulas from one cell to the next.
First, choose the cell that contains the formula, then right-click then, in the contextual menu choose Copy for the copy of your formula. You can also use the option ‘Copy’ within the ‘Clipboard’ section of the tab ‘Home.
However, you can could also copy formulas by using keystrokes like Ctrl +C. This is a much more efficient and time-saving technique.
When we get to the cell where we’d like to paste the formula into, and press to paste the shortcut Ctrl + V to paste the formula. Right-click the cell you wish to paste and select the options in the menu under “Paste Options that include either the simple “Paste (P) option, or paste using the ‘Formula (F)’ option.
Alternately, you can select “Paste Special” below the icons for six pastes to open the ‘Paste Special’ dialog box. There are a variety of choices, such as the paste option available from the contextual menu. Choose ‘All’ or ‘Formulas’ in the Paste section, and then click “OK”.
The cell that has pasted formula must contain the identical formulas (as as in the copied cell) however with different cell reference. The address of the cell is automatically adjusted by Excel to match the row number of the copied cell.
Copy Formula from one cell to Multiple Cells
The paste function works exactly the same way when we select several cells or a set of cells.
Choose the cell that contains the formula , and then press Ctrl+C for copying the formula. Select all cells in which you wish to copy the formula. Then, use Ctrl+V for the formula to be pasted. You can also choose one of the methods of paste for putting the formula in (like we did with the single cell).
Copy Formula to an Entire Column or Row
In Excel Excel, you can swiftly duplicate a formula for the entire column or row.
For copying a formula into either a row or column first, you must enter the formula into the cell. Select the cell with the formula (D1) and then move your mouse over a tiny green square in the lower left edge within the cell. When you hover your cursor, it will change to the black plus symbol (+), that is known as”the Fill Handle. Hold and click on that fill handle and move it any way that you would like (column or row) over the cells in order to duplicate the formula.
If the formula is copied into an array of cells, the cell references of the formula will adjust automatically according to the location of columns and rows, and the formula will calculate using the values contained within those cell references (See below for more details).).
In the example above the formula in D1 (=A1*B1)/2) transferred to D2 The relative reference is changed based on its position (=A2*B2)/2) and so on.
Similar to that, it is possible to drag this formula in adjacent cells on the left, to the right or upwards.
Another method to copy the formula across a column is to double-click the handle for fill instead of moving it. If you double-click on the fill handle, it instantly applies the formula for as long as data is available in the cell adjacent to it.
Copy a Formula to a Range Without Copying Formatting
If you paste a formula into an array of cells using the fill handle and the formula is copied, it also copies the formatting of the cell that is in the formula including the color of the font or background as well as percentages, currency and time, for example (as as shown below).
To avoid copying the cell’s formatting To avoid copying the formatting of the cell, move the handle for fill, and select Auto Fill Options’ at the lower right-hand corner of the final cell. In the drop-down menu, choose “Fill without Formatting’.
The outcome:
Transfer the Excel Formula with Only Number Formatting
If you wish to duplicate the formula, you can do so by copying just the formula and formatting, such as decimal points, percentage format and so on.
Copy the formula and then select all cells that you wish to duplicate the formula. In the tab ‘Home you can click the arrow just beneath the ‘Paste’ icon in the ribbon. After that, click the ‘Formulas & Number Formatting’ icon (the icon that has the % FX) in the dropdown menu to paste the formula only and the formatting of numbers.
This option copies only formulas and numbers, but does not take into consideration other formatting options for cells such as font color, background color, etc.
Copy a Formula to Non-Adjacent/Non-Contiguous Cells
If you wish duplicate a formula into non-adjacent cells, or ranges that are not adjacent it is possible using the the Ctrl key.
Choose the cell that has this formula, and then press Ctrl and C to copy the formula. Select the non-adjacent cells or ranges by pressing and holding your Ctrl key. After that use Ctrl and V to copy the formula, and press Enter to finish.
Copying Formulas Without Changing Cell References in Excel
If a formula is transferred to a different cells, Excel instantly changes its cell reference to correspond with the new place of origin. Cell references are based on the location of a relative cell’s address, and therefore are referred to as relative cell references (without the $). For instance, if you have the formula ‘=A1*B1’ inside cell C1 and copy that formula into cell C2 the formula will change to “=A2*B2”. The methods we have previously discussed use reference numbers that are relative to each other.
If you are copying a formula using reference to cell types the formula automatically alters references to make the formula be referring to the columns and rows. If you are using absolute references in formulas, the formula is copied, but without altering the references to cells.
If you place the dollar symbol ($) before the column’s letter as well as the row’s number in the cell (For example, $A$1) this transforms this cell in to an absolute one. No which place it is copied from the formula which has the cell’s absolute reference it will never. However, if you have a references to mixed or relative cells in your formula, you can use one of the following methods to copy the formula without changing the cell reference.
Copy Formula using Absolute Cell Reference using Copy-Paste Method
Sometimes, you’ll need to copy or apply the exact formula in the column, but without altering the references to cells. If you wish to move or copy an exact formula using absolute reference, follow these steps:
First, choose the cell that has the formula you wish to copy. After that, click in the bar for formulas, then select the formula with the mouse, and then press Ctrl and C to copy the formula. If you wish to change the formula, use Ctrl + the X key for cutting it. Then, press the Esc key to remove your formula bar.
Alternately, select the cell using the formula, and then press the the F2 button or double-click the cell. This puts the cell in editing mode. Select the formula within the cell, and press Ctrl+C to paste the formula into the cell in text.
Select the cell you want to paste into and click Ctrl+V to copy the formula.
The exact formula is copied into the destination cell, without reference modifications.
Copy Formulas using mixed or absolute cell references
If you’re looking to copy or move exact formulas, without altering cell references, it is recommended to convert cell references to absolute references. For instance, adding a ($) sign to a relative cell references (B1) transforms it into an absolute reference ($B$1) which means it is static regardless of the location where it is copied, or transferred.
However, there are times when you require multi-cell references ($B1 or B$1) by including the dollar ($) sign before the column’s letter or row number to secure the column or row in the correct position.
Let us go over an example. Let’s say that you own this spreadsheet which calculates month-long savings, through subtracting the rental (B9) of the earnings (in the column A) each month.
In the following example the formula employs an absolute reference to a cell ($B$9) to secure the rent amount in cell B9 and a relative cell reference to cell B2 since it has to be adjusted for every row to be matched with every month. B9 is converted to an absolute reference to cell ($B$9) since you wish to subtract the exact rent amount from the monthly earnings.
Let’s say you wish to transfer the balances of column C to column E. If you copy the formula (by the usual copy/paste technique) in Cell C2 (=B2-$B$9) will be changed to =D2-$B$9 after being pasted into cell E2, your calculations will be completely wrong!
In this case, you can modify the cell’s relative reference (B2) to a mixed cell reference ($B2) by adding the ‘$’ symbol before the column in the formula in cell C2.
Now, if you copy or transfer this formula to E2 or another cell, and then apply the formula to in the column column’s reference remains the same, while rows will get changed for each cell.
Copy Paste Excel Formulas Without Changing References Using Notepad
You can view every formula within your Excel spreadsheet using Show Formula options. To access this, click on the tab ‘Formulas’ and click the option to show Formulas’.
Alternately, you can switch to the view mode of formulas using or pressing the Ctrl + ` shortcut, which will display every formula on your worksheet. The grave accent button (`) in the upper right corner on your keyboard, on the row that has the numbers keys (below the ESC key and prior to the key for number 1).
Select all cells that contain formulas that you wish to copy, and use Ctrl +C to copy the formulas or press Ctrl + Z to cut the cells. Open Notepad then press Ctrl and V for the paste of formulas into the notepad.
Then, choose the formula, take a copy( Ctrl + C) it out of the notepad and then copy it and paste( Ctrl + V) into the cell you wish to have exactly the same formula to be copied. You can duplicate then paste one at a time several or at all at once.
After you’ve pasted the formulas, switch off the view mode for formulas using Ctrl + ` or go back to Formulas’> “Show formulas”.
Copy the Exact Formulas Using Excel’s Find and Replace
If you’re looking to copy a set of Exact formulas You can also make use of Excel’s Find and Replace tool to accomplish this.
Choose all cells with formulas you wish to copy. Go to the ‘Home tab, select ‘Find and Select’ in the Editing tab, and choose the option ‘Replace or hit the keys Ctrl and H to open the Find and Replace dialogue box.
Within the Find and Replace dialog box Enter an equal symbol (=) in the ‘Find What’ field. In the ‘Replace With field, type in the sign or character which isn’t already in your formulas, such as the number # or for example. After that, click the “Replace All button.
There will be a prompt box that reads ‘We created six replacements’ (because we selected 6 cells that have formulas). Click OK and “Close” to close the dialogs. This replaces the equal (=) signs with hash (#) signs, and transforms the formulas in text strings. The formulas’ cell references will not be altered when copied.
You can now select the cells you want to copy, then click Ctrl+Cto copy them, then copy them into the destination cells using the keys Ctrl and V..
In the end, you must switch your (#) signs back in to (=) signs. To do this, choose both areas (original and copied range) then press Ctrl+H for the Find & Replace dialog box. Enter in the hash (#) sign in the ‘Find What field’, and the equivalent to (=) sign in the field ‘Replace With then select the ‘Replace All’ button. Close the dialog.
The text strings are then converted back to formulas, and you’ll get the following result:
Done!