Copying Formula in Excel

Excel is one of the most powerful spreadsheet software supporting various functions and formulas. We can apply the existing formulas to the desired excel cells. However, sometimes, there may be cases when we need to use the same formula in different cells or a range of cells. In such cases, we can copy-paste the excel formula to the respective cells instead of applying the formula multiple times for those cells.

This tutorial discusses the different methods of copying a formula from an Excel cell. This article will help us understand how to copy formulas in single cells, multiple cells, non-adjacent cells, down a column, etc., including absolute and mixed references.

How to copy and Paste Formulas in Excel?

Although copy-pasting is easy in Excel, the method does not always work for copying formulas. Sometimes, copying the formulas in other cells, especially when cells aren’t relative, can be a little tricky. When we don’t copy-paste the Excel formulas correctly, we usually get formula errors like #REF!, #DIV/0!, etc.

Fortunately, Microsoft Excel provides several ways to perform the same task, which applies to copying Excel formulas. The following are the most common methods to copy-paste formulas in Excel, depending on specific scenarios:

• Copying a Formula from one Excel cell to Another
• Copying a Formula from one Excel cell to Multiple cells
• Copying a Formula to Entire Row or Column
• Copying a Formula without formatting
• Copying a Formula to Non-adjacent Excel cells
• Copying a Formula without changing cell references

Let us now understand each method in detail:

Copying a Formula from one Excel cell to Another

It is easy to copy a formula from one cell to another, avoiding entering it again and again and saving time. We can easily copy the formula using various ways.

Suppose we have the following Excel sheet where the values from cells A2, B2, and C2, are added in cell D2. We need to copy the formula from cell D2 to D3.

When copying the formula from cell D2, we must first select the specific cell. We must press the right-click button and select the ‘Copy’ option to copy the entire cell content and the formula. Alternately, we can also use the typical keyboard shortcut ‘Ctrl + C’ to copy selected contents in Excel.

After copying the cell with the formula, we need to go to the cell where we must paste the formula (D3 in our case). Again, we need to press the right-click button and choose ‘Formula’ under the ‘Paste Options’ from the contextual menu (right-click menu).

Alternately, we can launch Paste Special dialogue box by clicking the ‘Paste Special’ option from the contextual menu. Next, we can choose ‘Formulas’ under the ‘Paste’ section and click the OK button. This will paste only the formula from the copied cell.

The cell references are automatically adjusted as soon as we paste the copied formula into an Excel cell. However, the formula remains the same as the one in the copied cell. Thus, in our example, cell D3 has the same formula, but values are taken from cells A3, B3, and C3. The cell references are self-adjusted to match the row number or column of the pasted cell.

Note: The self-adjustment only happens while using relative references or mixed references. While dealing with absolute reference, the exact formula is copied. Thus, we must use absolute cell references to copy formulas with the same/exact cell references.

Copying a Formula from one Excel cell to Multiple cells

When copying a formula to multiple cells, we can follow the same steps as the previous method. We have to copy the cell with the formula using the “Ctrl+C” shortcut and then paste it on the destination cells. However, we must select all the destination cells before pasting the formula. To select multiple cells, we can click on the desired cells, one by one, while holding down the Ctrl key. Also, we can hold down the Shift key while selecting multiple contiguous cells and click on the first and last cells.

If we need to enter the formula in multiple cells, Excel also allows us to do the same with a single keystroke. The method works for both adjacent and non-adjacent cells.

• First, we need to select all the cells to type the formula. We can select contiguous cells using the drag feature of the mouse. Besides, we can click on each non-contiguous cell while holding the Ctrl key to select non-adjacent cells.
  
• We need to press the F2 key to go to the Edit mode.
  
• Lastly, we must enter the formula in an editing mode and press the Ctrl and Enter keys together (i.e., Ctrl + Enter).
  
  This will copy the entered formula into all selected cells with adjusted relative cell references.
  

Copying a Formula to Entire Row or Column

In Excel, copying formula from one cell to other respective cells throughout the entire row or column is also possible. It is very easy to perform, no matter how many cells are in a row or column.

To copy a formula to an entire row, we must enter the formula in the first desired cell of the corresponding row. Next, we must select the formula cell and move the mouse cursor to a small square present at the bottom right corner of the respective cell. As soon as we reach the square, our cursor will change to a black plus (+) sign. It is generally referred to as the Fill Handle.

We need to click and hold on the fill handle and drag it to the left or right side over the cells to copy the corresponding formula to destination cells.

In the above image, we need to drag to the right side of the cells from B5 to D5:

In the same way, we can drag the fill handle to the upside or downside for copying the formula to the desired column. Also, we can double-click the fill handle to automatically drag it till all the relative adjacent cells are filled. The formula will be copied to cells as far as there is data to adjacent cells.

When copying the Excel formulas using the drag feature, cell references for copied formulas are automatically adjusted based on relative locations of rows or columns. The results are also calculated based on the adjusted references to the destination cells.

Copying a Formula without formatting

By default, Excel automatically copies the formula with the formatting of the copied source to the destination cell(s). Generally, it is faced when copying Excel formulas using the Excel fill handle. It automatically applies the source cell’s formatting like the font color, background color, percentage, currency, etc.

In the above image, the formula and formatting are copied from cell D2 to D7 after dragging the fill handle.

We need to perform one more step to copy the formula without the source formatting. After we drag the fill handle, we need to click the ‘drop-down’ icon from the bottom right corner of the last cell. It is called the ‘Auto Fill Options’ button. After getting the options from the drop-down menu, we must choose the ‘Fill Without Formatting’ option.

In the above image, there is no formatting like the font color, background color, currency sign, etc.

Copying a Formula with Only Number Formatting

Since we typically use numeric values in Excel while using formulas, we may need number formatting copied along with the formula. This will help us copy the formula and keep formatting like the currency, percentage, decimal points, etc.

In our example, the number formatting (e.g., currency sign) is also ignored when we copied the formula without formatting. However, sometimes, we may need to keep the number formatting in many scenarios. In such cases, we need to first copy the formula from the source cell as usual. After that, we must perform the following steps:

• Select all the destination cells.
• Go to the Home tab, click the drop-drop icon associated with Paste, and select Formulas & Number Formatting.
  
  In this way, we can copy the formula, ignoring all formatting, but keep the number formatting.

Copying a Formula to Non-adjacent Excel cells

Sometimes, we may need to copy a formula to Excel’s desired non-adjacent or non-contiguous cells/ ranges. It is an easy-to-use process and can be used while copying the formula beyond the end of source data. This method works in much the same way as the traditional copy-paste method, as in:

• First, we need to select the cell with a formula by clicking on it.
• We need to copy the cell’s contents using the keyboard shortcut ‘Ctrl + C’.
  
• Next, we must select all the non-contiguous cells to paste the formula. We can click on each non-contiguous cell while holding the Ctrl key. In this way, we can easily select the desired non-contiguous cells in Excel.
  
• After selecting the destination cells (non-contiguous cells), we must use the keyboard shortcut ‘Ctrl + V’ to paste the formula.
  
• Lastly, we must click the Enter key on the keyboard to complete the process.

Copying a Formula without changing cell references

When we need to copy a formula with the same cell references (without auto adjustment of references) in Excel, we can use any of the four methods discussed below:

Method 1: By Using the Absolute or Mixed Cell References

If we use the absolute cell references within the formulas and copy them into other cells, the cell references do not change. When using absolute references, the corresponding cells are locked and do not change by Excel automatically. No matter where we move or copy the formula, the cell absolute cell references remain static. For example, to make cell A1 an absolute reference, we need to add a dollar ($) sign like $A$1.

Sometimes, we may need to apply mixed cell references instead of absolute references, which enable us to lock either a row or a column by adding a dollar ($) sign accordingly. For example, using mixed cell references like $A1 and A$1, we typically lock column A and the first row, respectively.

Let us understand this with an example. Suppose we want to calculate our remaining salary/earnings of each month after subtracting the rent amount (which remains the same every month). For this, we use an absolute cell reference to lock the cell with the rent amount (i.e., cell $B$15) and a relative cell reference to cell (B2) with earning in the first month (i.e., Jan). To calculate the remaining balance of January month, the formula in our example sheet looks like this:

If we copy the formula from C2 to other cells of column C, we will get the remaining balance for each month. In this case, the relative cell references are automatically adjusted for each month, while the absolute cell reference ($B$15) is fixed.

Suppose we want to copy the balances from column C to column E with their formulas. If we try to copy-paste, we will usually get the wrong results. When normally pasted in column E, the relative cell reference from column B will be adjusted to column D. To fix the relative cell references, we need to change it to a mixed cell reference.

Therefore, while copying the formulas in cell E2, we add the dollar ($) sign before the column letter to lock the cells from column B, i.e., $B2. Besides, the absolute cell reference ($B$15) remains the same. Now, our formula while copying formulas in column E will look like this:

If we copy the formula from E2 to other cells in column E, the row numbers will be adjusted accordingly. We will get our formulas copied from column C to column E, such as:

If we copy the formulas from column E to any other column, we will get the same results as the column reference (B) will remain the same.

Method 2: By Using the Find and Replace

When copying a range of cells with Excel formulas but want to have exact cell references, we can use the Excel Find and Replace feature as discussed in the following steps:

• First, we need to select the Excel cells with formulas that we want to copy into other cells.
  
• Next, we must go to Home > Find & Select > Replace to launch the Excel Find & Replace dialogue box. We can also use the keyboard shortcut ‘Ctrl + H’ to open it quickly.
  
• We need to enter the equal (=) sign next to the ‘Find what’ box in the next window. Also, next to the ‘Replace with’ box, we must type some specific symbol or a string of characters that are not part of the formulas. For example – we can use symbols like #, or ‘. In our example, we use the (#) symbol.
  
  This step is very important as it converts the selected formulas into text strings, preventing Excel from changing cell references while copying.
• After entering values in the dialogue box, we must click the ‘Replace All’ button and close the dialogue box. All the selected formulas will change into text strings, as shown below:
  
• Again, we can select the same cells or a range and press the shortcut ‘Ctrl + C’ to copy contents. Since there is no equal sign, the content will be copied exactly, without changes in cell references. After copying the cells, we must go to the first cell of the destination range and paste the copied contents using the shortcut ‘Ctrl + V’.
  
• Now, we need to select the destination cells. We must again open the Find & Replace dialogue box and type (#) (or whatever character or symbol we used earlier) next to the ‘Find what’ box and (=) next to the ‘Replace with’ box. Finally, we must click the ‘Replace All’ button and close the dialogue box.
  
  This will convert the text strings back to the formulas. We will get the formulas copied into destination cells or a range with the exact cell references. Similarly, we can convert text strings back to formulas in the source cells.
  

Method 3: By using the Copy-Paste Method

We can copy or move an Excel formula with absolute reference from one cell to another using the copy-paste method in a specific way. Sometimes, we may need to copy or apply the formula from one cell to another with the exact reference. We can follow the below steps:

• First, we need to select the Excel cell with a formula that we want to copy.
• Next, we must click the formula bar and select/ highlight the entire formula in the formula bar, as shown below:
  
• After selecting the formula, we must click the shortcut ‘Ctrl + C’ to copy the formula. We can also cut the formula using the shortcut ‘Ctrl + X’ when we need to move the formula from one cell to another with the exact cell reference. After copying the formula, we must press the Esc key to leave the formula bar.
• Alternately, we can select the cell with a formula, double-click the cell (or press the F2 key on the keyboard) to go into the cell edit mode. After that, we can select the entire formula from the cell using the mouse and press the shortcut ‘Ctrl + C’ to copy it as text. Also, we must press the Esc key to leave the cell edit mode.
  
• Once the formula from the source cell is copied, we must select the destination cell and press the shortcut ‘Ctrl + V’ to paste the exact formula. There will be no automatic adjustment of cell references in the copied formula.
  

Method 4: By using the Text Editor like Notepad

Another simple method to copy a formula with the exact referencing is to use a notepad in the following way:

• First, we need to select the source cells with formulas and click the ‘Show Formulas’ option under the Formulas tab.
  
  Alternately, we can quickly use the keyboard shortcut (Ctrl + `) to enter the formula view mode.
• Once all the formulas are displayed, we must select corresponding cells and use ‘Ctrl + C’ or ‘Ctrl + X’ to copy or cut/move the formula.
  
• After copying the formulas, we need to open the notepad and paste the copied formulas into the notepad using the shortcut ‘Ctrl + V’.
  
• Now, we need to again copy the formulas from the notepad using ‘Ctrl + C’. We can copy a single formula or all the formulas at once.
  
• After copying the formulas from the notepad, we need to select the top or first cell of the destination range in Excel and paste the formulas using ‘Ctrl + V’. This will paste the copied formulas with the exact cell references as per the source.
  
• Lastly, we can turn off the formula view mode to get the respective values. To deactivate the formula view mode, we can go to ‘Formulas > Show Formulas’ or press the shortcut (Ctrl + `).