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 **Pr****oject 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 key**s.

(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 key**s ➪ 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?

## Premium Courses on Coursera

## Premium Courses on Udemy

**04 Alternative Methods: How to Wrap Text in Excel?**

**04 Easy Methods: How to Delete Rows/Columns/Cells in Excel?**

**04 Safest Methods: How to Delete Blank Rows in Excel?**

**06 Easy Steps How to Insert Delta Symbol in Excel?**

**How To Use Excel Symbol (Degree Symbol, Check Mark Symbol)**

**12 Examples || How to Use Excel Go To Special?**

**07 Points Guided You How to Find And Replace in Excel?**

**05 Points Should Learn How to Freeze Panes in Excel?**

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

#### Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

* VERIFY & CONFIRM YOUR EMAIL * We respect your privacy and take protecting it seriously*.