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:
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.
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:
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:
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:
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:
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: