06 Alternative Methods: How to Copy Formula in Excel?

It is seriously an important part of Advanced Excel – how to copy formula in Excel? The reason behind this when we copy formulas across rows or columns, it automatically adjusts the formula for the proper column and row. If we copy formulas down through a column, the row number in the formula will be automatically adjusted to the current row where the formula is being copied to. If we copy formulas across rows, the column letter in the formula will be automatically adjusted to the current column where the formula is being copied to. This automatic adjustment of row and column addressing is called relative cell referencing because the cell addresses change relative to where the formula is placed.

But we should know when we use the relative, absolute and mixed cell reference in the formula because it varies according to the data structure and requirement.

■ Note: We had detail discussed on Cell Reference in a separate tutorial, suggested you read this tutorial: 03 Types of Excel Cell Reference: Relative, Absolute & Mixed

Additionally, request you to read  05 Best Methods: How To Create Formula in Excel?

(A) STEP 1: APPLY THE CELL REFERENCES IN THE FORMULA

After entering a formula in a cell, we duplicate the formula without retyping the formula for other cells that need a similar formula. We can use the ‘Formulas‘ option or ‘Formulas and number formats‘ option in the Paste Special dialog box to copy the formula in the active cell to adjacent cells down a column or across a row, depending on how the data are organized. Cell references in copied formulas adjust based on their relative locations to the original formula.

In the given example, we will find out the Project Manager Name, Country and Total Sales based on the Project Code. In this case, we use a two-dimensional array in the VLOOKUP formula (2D VLOOKUP) to make the formula more dynamic. Even it is more beneficial for a better understanding of the importance of cell references during copy the formula.

In cell G2, we use the VLOOKUP formula to get the name of the Project Manager. Before applying the cell references, the formula seems to be:

=VLOOKUP(F2,A2:D19,MATCH(G1,A1:D1,0),0)

In this case, all the cell reference is relative.

The syntax for the VLOOKUP function is

F2 – lookup_value. In this case, it refers to the Project Code AUS-36 and all the codes are arranged vertically or column-wise. If we copy the formula horizontally or row-wise, the column address has been changed accordingly but the row number doesn’t change, e.g., G2, H2, I2 etc.

So in this case, we should fix the column address by selecting the cell and press the F4 key thrice. As a result, the cell reference converts to a mixed cell reference where the column address is absolute but the row number remains relative.

A2:D19 – table_array. It is the range of cells or table from where required data to be retrieved. If we copy the formula either horizontally or vertically in any direction, it always needs to be fixed.

So in this case, we should fix the table_array by selecting the range and press the F4 key once. As a result, the range converts to absolute cell reference where both the column address and row number are absolute.

MATCH(G1,A1:D1,0) – col_index_num. It is the count of column numbers between the lookup column and the result column. We can manually type the column number but in this case, we use the MATCH function to retrieve the column number dynamically when the formula is copied to another location.

The syntax for the MATCH function is 

➢ In the MATCH function, G1 is lookup_value which refers to the Project Manager which is located in the subject heading and the heading is arranged horizontally. If we copy the formula vertically or column-wise, the row number has been changed accordingly, but the column address doesn’t change, e.g., G2, G3, G4 etc.

So in this case, we should fix the row number by selecting the cell and press the F4 key twice. As a result, the cell reference converts to a mixed cell reference where the row number is absolute but the column address remains relative.

➢ In the MATCH function, A1:D1 is lookup_array and it refers to the subject heading of the original table where the lookup_value (i.e., Project Manager) to be found.

We should fix it by selecting the range and press the F4 key once. So, it will convert to absolute cell reference where both the column address and row number are absolute.

➢ In the MATCH function, 0 is the match type and it refers to the exact match.

0 – range-lookup. The value zero refers to the exact match.

After applying cell references, the formula seems to be:

=VLOOKUP($F2,$A$2:$D$19,MATCH(G$1,$A$1:$D$1,0),0)

(B) STEP 2: HOW TO COPY FORMULA IN EXCEL DOWN / RIGHT ANY DIRECTION

(01) METHOD 1: HOW TO COPY FORMULA IN EXCEL ➢ USING ‘FORMULAS AND NUMBER FORMATS’ IN PASTE SPECIAL

It is the best choice to copy the formula in the desired range with the ‘Formulas and number formats’ option in the Paste Special dialog box. As a result, only the formula of a copied cell will be pasted over the range instead of the cell formatting.

⇒ After applying the cell references in the formula, select and copy (Ctrl+C) the cell (i.e., G2) ➪ Extend the selection to the desire range by Shift+ Right Arrow (➡) and Shift+Down Arrow (⬇) ➪ After selection, press Alt+E+S+R (sequentially press Alt, E, S, R) or Alt+Ctrl+V+R (hold the Alt+Ctrl+V keys and then press R key) which will select the ‘Formulas and number formats‘ option in the Paste Special dialog box ➪ Finally press Enter or click OK to accept the condition.

 

⇒ Equivalently, select and copy (Ctrl+C) the cell (i.e., G2) ➪ Extend the selection to the desired range by Shift+ Right Arrow (➡) and Shift+Down Arrow (⬇) ➪ Right-click on the selection ➪ Mouse over the Paste Special arrow and a list open ➪ Click Paste Special from the list which will open the Paste Special dialog box.

Select the ‘Formulas and number formats‘ option in the Paste Special dialog box ➪ Finally press Enter or click OK to accept the condition.

As a result, the formula will be copied in Excel’s selected range and returns the value based on the conditions.

(02) METHOD 2: HOW TO COPY FORMULA IN EXCEL ➢ USING ‘FORMULAS’ IN PASTE SPECIAL

Another best choice to copy the formula in the desired range with the ‘Formulas’ option in the Paste Special dialog box. As a result, only the formula of a copied cell will be pasted over the range instead of the cell formatting.

⇒ After applying the cell references in the formula, select and copy (Ctrl+C) the cell (i.e., G2) ➪ Extend the selection to the desired range by Shift+ Right Arrow (➡) and Shift+Down Arrow (⬇) ➪ After selection, press Alt+E+S+F (sequentially press Alt, E, S, F) or Alt+Ctrl+V+F (hold the Alt+Ctrl+V keys and then press F key) which will select the ‘Formulas‘ option in the Paste Special dialog box ➪ Finally press Enter or click OK to accept the condition.

⇒ Equivalently, select and copy (Ctrl+C) the cell (i.e., G2) ➪ Extend the selection to the desired range by Shift+ Right Arrow (➡) and Shift+Down Arrow (⬇) ➪ Right-click on the selection ➪ Click on the Paste Special which will open the Paste Special dialog box ➪ Select the ‘Formulas‘ option in the Paste Special dialog box ➪ Finally press Enter or click OK to accept the condition.

⇒ Equivalently, select and copy (Ctrl+C) the cell (i.e., G2) ➪ Extend the selection to the desired range by Shift+ Right Arrow (➡) and Shift+Down Arrow (⬇) ➪ Right-click on the selection ➪ Click on the ‘Formulas‘ icon.

As a result, the formula will be copied to the selected range and returns the value based on the conditions.

(03) METHOD 3: HOW TO COPY FORMULA IN EXCEL ➢ USING AUTOFILL IN EXCEL

The Excel AutoFill method of copying formulas is helpful if we’re copying a formula to surrounding cells. The Autofill in Excel quickly copies the formula across rows or columns in the worksheet by dragging the selection’s fill handle (the small square in the bottom-right corner of the selected cell or range). Excel copies the original selection to the cells that we highlight while dragging. If the cell references in a formula are relative, Excel automatically adjusts the formula for the destination cell.

For more control over the AutoFill operation, drag the fill handle with the right mouse button, and we’ll get a shortcut menu with additional options.

(i). After placing the cell references in the formula (if required), position the mouse on the AutoFill box in the lower right corner of a cell with a formula. Make sure the mouse pointer turns into a black cross.

(ii). Click and drag the fill handle with the right mouse button to include the cells either across the row or across the column to which we want to copy the formula.

(iii). A shortcut menu will open and then select the ‘Fill Without Formatting’.

As a result, the formula will copy without cell formatting.

(iv). Suppose we copy the formula horizontally, first we select those filled cells and drag the fill handle downward with the right mouse button and choose ‘Fill Without Formatting; from the shortcut menu.

■ Note: (i) Please keep in mind that the Autofill in Excel only works in any one direction either across the row (horizontally) or across the column (vertically).

(ii) We can hold and drag the fill handle with the left mouse button, but in this case, the formula will be copied to the new range of cells along with the copied cell formatting. If we concern about the cell formatting, then we hold the right mouse button instead of the left mouse button.

(iii) Easiest Way in Excel Copy Formula Down a Column: It is the easiest way in Excel to copy formula down a column (Vertically) by double-clicking the fill handle (when it turns into a plus sign).

The formula will copy downward by double-clicking the plus sign till the availability of data at the left side.

If there is an empty row AutoFill stops. In that case, copy the formula from the above cell to a new cell and repeat the process.

(04) METHOD 4: HOW TO COPY FORMULA IN EXCEL ➢ USING CTRL+ENTER

(i) Select the cell G2 where we apply the formula and valid cell references in the formula ➪ Then extend the selection by Shift+ Arrow keys.

(ii) Press the F2 key to enter the cell edit mode.

Simply press Ctrl+Enter (instead of the Enter key).

As a result, the formula will be copied to the entire range without copying the cell formatting.

(05) METHOD 5: HOW TO COPY FORMULA IN EXCEL ➢ USING ‘FILL DOWN’ AND ‘FILL RIGHT’ OPTIONS

Another way to copy formula in Excel either any direction (down a column or across the right) with the Excel Shortcut. But we should aware of the cell formatting because this method fills the same cell formatting of copied cell to the entire range.

• Ctrl+D – Fill Down along with formulas and cell formatting of the copied cell.

• Ctrl+R – Fill Right along with formulas and cell formatting of the copied cell.

Select the cell G2 where we apply the formula and valid cell references in the formula ➪ Then extend the selection by Shift+ Arrow keys ➪ After selection, press Ctrl+D for fill down and then Ctrl+R for fill right or vice versa.

(06) METHOD 6: HOW TO COPY FORMULA IN EXCEL ➢ USING COPY (CTRL+C) AND PASTE (CTRL+V)

Very simple way of copy formula in Excel is copy (Ctrl+C) and paste (Ctrl+V).  But this method fills the same cell formatting of copied cell to the entire range, so existing cell formatting has been removed after pasting.

If there is no limitation on the cell formatting, then we can follow this method.

Copy (Ctrl+C) the cell G2 where we apply the formula and valid cell references in the formula ➪ Then extend the selection by Shift+ Arrow keys ➪ Press Ctrl+V or press Enter.

(C) STEP 3: HOW TO COPY EXACT FORMULA IN EXCEL (WITHOUT CHANGING CELL REFERENCES)

When we paste a formula to a different location, Excel adjusts the cell references in the formula based on a new location. Sometimes, for a single cell or small dataset, we can copy the exact formula by converting the cell references to absolute references — but this isn’t always beneficial. In this case, require an exact copy of the formula without changing the cell references.

01. HOW TO COPY EXACT FORMULA IN EXCEL ➢ USING FORMULA BAR (ONLY FOR SINGLE CELL)

1. Select cell E3.

2. Select and copy the formula from the formula bar.

Alternatively, press the F2 key to activate edit mode and then press Ctrl+Shift+Home to move the cursor to the start of the formula and select all the formula text. Or we can drag the mouse to select the entire formula.

Note that use Ctrl+Shit+End to select the entire formula when the formula is more than one line long, but optional for formulas that are a single line.

3. Press Ctrl+C (or Choose Home ➪ Clipboard ➪Copy).

This copies the selected formula to the Clipboard.

4. Press Esc to end edit mode

5. Select cell F3 and press F2, for edit mode.

7. Press Ctrl+V (or Choose Home ➪ Clipboard ➪ Paste), followed by Enter.

This operation pastes an exact copy of the formula text into cell F3.

■ Note: By this method, we can paste the formula only in one cell instead of multiple cells. If we try to paste the formula in multiple cells, Excel returns us a warning message

02. HOW TO COPY EXACT FORMULA IN EXCEL ➢ USING TEXT TO COLUMNS (ONLY FOR SINGLE COLUMN)

Another advanced method to copy the exact formula to the range of cells arranged in a single column by the Text to Columns because of text to columns only applicable for a single column. If we want to copy formulas from multiple columns then we apply similar steps repeatedly for every column. With the help of Text to Columns, We can convert the formulated cells in a column to ‘text’  and paste them easily to a new location.

Step to Start:

(i) Either select only the range (i.e., E2:E11) or select the entire column (i.e. column E). Here we select the entire column by pressing Ctrl+Spacebar. Remember that no merge cells exist there.

(ii) Then press Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard’.

➢ In Step 1 of 3, by default  Delimited is selected ➪ Press Enter or click Next.

➢ In Step 2 of 3, by default, all the checkboxes remain unchecked, except Tab checkbox ➪ Press Enter or click Next.

➢ In Step 3 of 3, by default, the ‘General’ option is selected but we select the ‘Text’ option radio button ➪ Then press Enter or click Finish.

As a result, all the cells with the formula converted to text, and the formula in the cell is exposed.

(iii) Copy the range of cells (i.e., E2:E11) with a keyboard shortcut Ctrl+C and paste them in the desired location (i.e., in cell B14 and pasted range is B14:B23).

As a result, the text range remains the same (unmodified) in the pasted area.

(iv) In the next step, again we convert the range from ‘Text’ to ‘General’ with the help of ‘Convert Text to Columns Wizard’.

Press Alt+D+E (sequentially press Alt, D, E) or Alt+A+E (sequentially press Alt, A, E) which will open the ‘Convert Text to Columns Wizard’.

➢ In Step 1 of 3, by default  Delimited is selected ➪ Press Enter or click Next.

➢ In Step 2 of 3, by default, all the checkboxes remain unchecked, except Tab checkbox ➪ Press Enter or click Next.

➢ In Step 3 of 3, by default, the ‘General’ option is selected but we select the ‘Text’ option radio button ➪ Then press Enter or click Finish.

As a result, the text is converted to general and the formula is exactly copied to the new location.

■ Note: We had detail explained on Convert Text to Columns Wizard in a separate tutorial. Request you read this tutorial: 10 Examples of Text to Columns || How to Split Cells/Columns in Excel

03. HOW TO COPY EXACT FORMULA IN EXCEL ➢ USING ‘PASTE SPECIAL’ (FOR ENTIRE RANGES)

We can exactly copy the entire range along with the formula using the ‘Paste Special’ dialog box and easily paste it to another location.

➢ METHOD 1:  At first, Select the entire range by Ctrl+A and then copy it by Ctrl+C ➪ Then select only the first cell where to paste the range ➪ Press Alt+E+S+R (sequentially press Alt, E, S, R) or Alt+Ctrl+V+R (press Alt+Ctrl+V, then R) which will select the ‘Formulas and number formats‘ in the Paste Special dialog box ➪ Finally press Enter or click OK to accept the formula.

■ Note: We can also avail the ‘Formulas’ option in the Paste Special dialog box using the Excel shortcut Alt+E+S+F (sequentially press Alt, E, S, F) or Alt+Ctrl+V+F (press Alt+Ctrl+V, then F)

As a result, the range with formula is pasted over a new location without copying any formatting.

➢METHOD 2: If we want to copy both formula and cell formatting from the copied range then follow the following steps:

(i) CHANGE THE COLUMN WIDTH: At first, Select the entire range by Ctrl+A and then copy it by Ctrl+C ➪ Then select only the first cell where to paste the range ➪ Press Alt+E+S+W (sequentially press Alt, E, S, W) or Alt+Ctrl+V+W (press Alt+Ctrl+V, then W) which will select the ‘Column widths‘ in the Paste Special dialog box ➪ Finally press Enter or click OK to accept the formula.

As a result, similar column widths from the copied range has been adjusted to the new location.

(ii) PASTE THE FORMULAS AND NUMBER FORMATS: Simultaneously, press Alt+E+S+R (sequentially press Alt, E, S, R) or Alt+Ctrl+V+R (press Alt+Ctrl+V, then R) which will select the ‘Formulas and number formats‘ in the Paste Special dialog box ➪ Finally press Enter or click OK to accept the formula.

As a result, the formulas and the number formats from the copied range are copied to the new location.

(iii) PASTE THE FORMATTING: Simultaneously, press Alt+E+S+T (sequentially press Alt, E, S, T) or Alt+Ctrl+V+T (press Alt+Ctrl+V, then T) which will select the ‘Formats‘ option in the Paste Special dialog box ➪ Finally press Enter or click OK to accept the formula.

As a result, similar formatting from the copied range is pasted to the new location.

■ Note: We had detail explained on Excel Paste Special in a separate tutorial. Request you read this tutorial: Paste Special in Excel Vs Break Link – Which one is Better?

04. HOW TO COPY EXACT FORMULA IN EXCEL ➢ USING ‘FIND AND REPLACE’ (FOR ENTIRE RANGES)

We can replace the equality sign with uncommon text, then paste the range to another location and finally replace this uncommon text with an equality sign.

(i) Select the range with Ctrl+A ➪ then press Ctrl+H which will open the ‘Find and Replace’ dialog box.

➢ type equality (=) sign in front of the ‘Find what:’ box.

➢ type any uncommon text such as ‘change’ in front of the ‘Replace with:’ box.

At last, press the Replace All button, and Excel replaces all the equality signs with the text ‘change’ in the entire range.

(ii) Select the entire range with Ctrl+A ➪ then copy with shortcut Ctrl+C ➪ paste it to another location ➪ then press Ctrl+H which will open the ‘Find and Replace‘ dialog box.

➢ type the text ‘change’ in front of the ‘Find what:’ box.

➢ type equality (=) sign in front of the ‘Replace with:’ box.

Finally, press the Replace All button, and Excel replaces all the text ‘change’ with equality (=) signs in the entire range. As a result, the formula restored in a new location and works perfectly.

■ Note: We had detail explained on Find and Replace in a separate tutorial. Request you read this tutorial: 07 Points Guided You How to Find And Replace in Excel?