*In this tutorial, you will learn different ways to copy formulas in Excel: how to copy formulas to a column or to all selected cells, how to copy a formula exactly without changing cell references or formatting, and more.*

Copying formulas in Excel is one of the easiest tasks that is usually done with one click. I say "general" because there can be very specific cases that require special tricks, like copying a set of formulas without changing cell references, or typing the same formula into multiple nonadjacent cells.

Luckily, Microsoft Excel offers many ways to accomplish the same task, and the same goes for copying formulas. In this tutorial, we will discuss different ways to copy formulas in Excel so that you can choose the most suitable one for your task.

- Copy the formula into a column
- Copy the formula to the whole column
- Copy formula to non-adjacent cells/ranges
- How to enter a formula in multiple cells
- How to copy formulas but not format them
- How to copy a formula without changing the references
- Shortcuts to copy formulas in Excel

## How to copy the formula into a column

Microsoft Excel offers a really fast way to copy a formula into a column. You simply do the following:

- Enter a formula in the top cell.
- Select the cell with the formula and hover over a small square in the lower-right corner of the cell that pops up
**filling handle**. When you do this, the cursor turns into a thick black cross. - Hold down the fill handle and drag down the column over the cells where you want to copy the formula.

Likewise you can**draw formula**in**neighboring cells**to the right, left or up.

If the formula containsrelative cell references(without the $ sign) they change automatically based on a relative position of rows and columns. So after copying the formula, check whether the cell references are set correctly and lead to the desired result. If necessary,Switch between absolute, relative and mixed referencesUse ofF4Key.

In the example above, to ensure that the formula was copied correctly, we select a cell in column C, e.g. B. C4, and look at the cell reference in the formula bar. As you can see in the screenshot below, the formula regarding line 4 is fine, just as it should be:

### How to copy a formula without copying the format

If you copy a formula down by dragging the fill handle, not only the formula but also the source cell will be copied**Format**like the font or background color, currency symbols, number of decimal places displayed, etc. In most cases this works fine, but sometimes it can mess up the existing formats in the cells the formula is copied to. A common example is overwritingalternating line shadinglike the following screenshot.

To avoid overwriting the existing cell format, drag the fill handle as shown above, release it and click the button*AutoComplete options*drop down menu and select**fill in without formatting**.

## Copy the formula to the entire column

As you just saw, the Fill handler makes copying formulas in Excel really easy. But what if you need to copy a formula onto a sheet of ten hundred lines? It doesn't seem like a good idea to stretch the formula over hundreds of rows. Fortunately, Microsoft Excel offers a few quick fixes for this as well.

### Double-click the plus sign to fill the entire column

To apply the formula to the entire column, double-click the plus sign instead of dragging it. For those who skipped the first section of this tutorial, the detailed steps are below.

To copy an Excel formula to the entire column, please do the following:

- Enter your formula in the top cell.
- Place your cursor in the lower-right corner of the cell with the formula, wait for it to turn into a plus sign, and then double-click the plus sign.

UseDouble-clicking the plus sign copies the formula down where there is some data in the adjacent column(s). As soon as an empty line occurs, the autofill stops. So if your worksheet contains spaces, you need to repeat the above process to copy the formula under a blank row or drag the fill handle as explained in the examples above:

### Create an Excel spreadsheet to automatically copy a formula to all cells in a column

Among other great features ofExcel spreadsheetsLike predefined styles, sorting, filtering, and banded rows, automatically calculated columns make an Excel spreadsheet a truly wonderful tool for analyzing groups of related data.

When you enter a formula in a cell in a table column (any cell, not necessarily the top one), you create one**calculated column**and have your formula copied to all other cells in that column immediately. Unlike the Fill handler, Excel spreadsheets have no problem copying the formula to the entire column, even if the spreadsheet contains one or more blank rows:

To convert a range of cells to an Excel spreadsheet, just select all the cells and pressControl + T. If you prefer a visual form, select the range, go to*insertion*tab >*Table Mountains*group in the Excel ribbon and click the**Mesa**Knopf.

Bribery.If you really don't want an Excel spreadsheet in your worksheet, you can create it temporarily to make working with formulas easier, and then convert the spreadsheet back to a regular range in seconds. Just right click on the table and select*Mesa*>*convert to area*in the context menu.

## Copy a formula to nonadjacent cells/ranges

It goes without saying that the fill handler is the fastest way to copy formulas in Excel. But what if you want to copy your Excel formula into non-contiguous cells or past the end of the source data? Just use the old way of copying and pasting:

- Click the cell with the formula to select it.
- PressControl + Cto copy the formula.
- Select a cell or range of cells where you want to paste the formula (to select nonadjacent ranges, hold down the keycontrolKey).
- PressCtrl + Vto insert the formula.
- Press Enter to complete the pasted formulas.

UseThe copy/paste shortcuts copy the formula and format. A**Copy formula without formatting**choose a suitable one*Take*Option in ribbon or context menu as shown inCopy an Excel formula without formatting.

## Enter a formula in multiple cells with a single keystroke (Ctrl + Enter)

In situations where you need to enter the same formula in more than one adjacent or non-adjacent cell on a worksheet, this method can save you time.

- Select all the cells where you want to enter the formula. To select nonadjacent cells, hold down the keycontrolKey.
- PressF2to enter edit mode.
- Enter your formula in a cell and pressControl + introductionInstead ofGet into. That's it! The formula is copied to all selected cells and Excel adjusts the relative references of the cells accordingly.

Bribery.This method allows you to enter any data, not just formulas, into multiple cells at once. Some other techniques are described in the following tutorial:How to enter the same data in all selected cells at once.

## How to copy an Excel formula but without formatting

As you already know, when you copy a formula to a column in Excel, you can use theFill without format optionThis allows you to copy the formula but keep the existing formatting of the target cells. Excel*get copy*The feature offers even more flexibility in terms of paste options.

- Select the sale that contains the formula.
- Copy this cell by pressingControl + C. Alternatively, you can right-click the cell and select
**Copy**in the context menu or click on the**Copy**button on the*Haus*tab >*clipboard*. - Select all the cells where you want to copy the formula.
- Right click on the selected cells and select
**formulas**low*Insert options*:

To see more paste options, click the arrow below**Take**button on the band. For example, you can choose**Formulas and number format**to paste only the formula and number format, e.g. B. Percent format, currency format and the like:

Bribery.If you're not sure which paste option works best for you, hover your mouse over different icons to preview this or that paste option.

## Copy the formula in Excel without changing the references

Excel formulas rarely appear alone in a spreadsheet. In most cases, you enter a formula in a cell and then copy it to other cells in the same column or row to perform the same calculation on a group of data. And if your formula includesrelative cell references(without the $) Excel automatically adjusts them so that each formula works with data in its own row or column. Most of the time, that's exactly what you want. For example if you have the formula`=A1*2`

in cell B1 and copy this formula into cell B3, the formula changes to`=A3*2`

.

But what if you want Excel**copy the formula exactly**without changing the cell references? Depending on the task, choose one of the following solutions.

### Copy or move a single formula without changing cell references

If you just need to copy or move a formula, you can easily make an exact copy.

- Select the cell with the formula that you want to copy.
- Select the formula in the formula bar with the mouse and pressControl + Ca
**Copy**that. If you want it**movement**the formula, pressStrg + Xto cut it - Press theEscbutton to exit the edit bar.
- Select the target cell and pressCtrl + Vto paste the formula there.

Alternatively, you can switch to edit mode and copy the formula into the cell as text:

- Select a cell with the formula.
- PressF2(or double-click the cell) to switch to edit mode.
- Select the formula in the cell with the mouse and pressControl + Cto copy it.
- Select the target cell and pressCtrl+V. This will paste the formula exactly without changing the cell references since the formula was copied as text.

Bribery.fast**Copy a formula from the cell above**without changing the reference, select the cell where you want to paste the formula and presscontrol + '.

### Copy a set of formulas without changing cell references

Use one of the following methods to move or copy a range of Excel formulas so cell references don't change.

#### Method 1. Use absolute or mixed cell references

If you need to make one**exact copy of the formulas**con**relative cell references**(like A1), the best way would be to change them**absolute references**($A$1) to fix the reference to a specific cell so that it remains static no matter where the formula is moved. In other cases you may need to use**mixed cell references**($A1 or A$1) to lock a column or row. Doesn't make much sense so far? Well, let's consider the following example.

Suppose you have a table that calculates fruit prices in EUR based on the USD price in column B and the exchange rate in cell C2:

As you can see in the screenshot above, the formula contains an absolute cell reference ($C$2) to fix the price in cell C2 and a relative cell reference to cell B5, since this reference should be adjusted for each row. And this approach works well as long as the formulas stay in column C.

But let's see what happens if you need to move the prices in EUR from column C to column F, for example. If you copy the formulas in the usual way, by copying/pasting the cells, the formula will be in cell C5 (=**B5***$C$2) changes to =**D5***$C$2 when pasted into cell F5, causing your calculations to be wrong!

To fix this, just change a relative reference (B5) to a**mixed reference**$B5 (absolute column and relative row). By putting the dollar sign ($) in front of the column letter, you anchor the reference to column B wherever the formula goes.

And now, if you copy or move the formulas from column D to column F or any other column, the**the column reference does not change**because you locked it with the dollar sign ($B5).

The concept ofExcel cell referencesIt can be hard to understand at first, but trust me, it's worth your time and effort because it will save you a lot more time in the long run. For example, see how to calculate the entire table with a single formulamixed cell references.

However, if you already have a bunch of formulas with relative cell references in your Excel spreadsheet and need to quickly make an exact copy of those formulas, but don't feel like you can get the correct references, one of the following methods might be a solution . .

#### Method 2. Copy Excel formulas without changing references via Notepad

- Enter formula view mode by pressing the buttonCtrl + `Shortcut or any other method described inHow to view formulas in Excel.
- Select all the cells with the formulas that you want to copy or move.
- PressControl + Cto copy the formulas thatStrg + Xto cut them. Use the last link when you want to move formulas to a new location.
- Open Notepad or another text editor and pressCtrl + Vto paste the formulas there. then pressControl + Ato select all formulas, andControl + Cto copy them as text.
- In your Excel spreadsheet, select the top left cell where you want to paste the formulas and pressCtrl + V.

Nuts:

- You can only paste the formulas into the
**same worksheet**where your original formulas are unless the references include the sheet name, otherwise the formulas will be split. - The worksheet must be included
**formula view mode**. To check this go to*formulas*tab >*Formel-Audit*group and check that the**show formulas**the button is enabled. - After pasting the formulas, pressCtrl + `to turn off formula view mode.

#### Method 3. Copy formulas exactly with Excel Find and Replace

To copy a set of Excel formulas without changing their cell references, you can use the Excel*search and replace*function as follows.

- Select the cells with the formulas you want to copy.
- About him
*Haus*tab, go to*edition*group and click*Search and select*>**Substitute…**or just pressCtrl + H, which is the shortcut to launch theDialog in Excel. - inside
*Search and replace*In the dialog box, type the equal sign (=).*find that*Crate. inside*replace with*In the box, enter a symbol or string that is not used in any of your formulas, such as ', # or \.

The purpose of this step is to convert the formulas to text strings, which prevents Excel from changing the cell references during the copying process.UseDo not use an asterisk (*) or question mark (?) to replace them, as these are wildcard characters in Excel and using them would complicate later steps.

(Video) Copy or Move Formulas Without Changing Cell References In Excel - 3 Methods - Click on that
**replace everything**button and close the*search and replace*Dialog. All formulas in the selected range are converted to text strings: - Now you can select any cell, pressControl + CTo copy them, select the top cell in the
**current worksheet**where you want to paste the formulas and pressCtrl + V. Since Excel does not interpret formulas without an equals sign as formulas, they are copied exactly without changing the references. - Use
*Search and replace*again to undo the change. Select both regions with the original and copied formulas (to select nonadjacent regions, press and holdcontrol). PressCtrl + Hto open the Find and Replace dialog box. This time, type the backslash (\) (or whatever character you used for the first replacement) in the*find that*field, y = in the*replace with*checkbox and click the*replace everything*Button. Completed!

## Shortcuts to copy Excel formulas to other cells

**1. Copy a formula down**

Ctrl+D- Copy a formula from the cell above and adjust the cell references.

For example, if you have a formula in cell A1 and want to copy it to cell A2, select A2 and pressCtrl+D.

**2. Copy a formula to the right**

Strg + R- Copy a formula from the left cell and adjust cell references.

For example, if you have a formula in cell A2 and want to copy it to cell B2, select B2 and pressStrg + R.

Bribery.The two keyboard shortcuts above can also be used to copy formulas across multiple cells. The trick is to select both the source cell and the target cell before pressing the shortcut. For example, if you want to copy the formula from A1 to the next 9 rows, select cells A1:A10 and pressCtrl+D.

**3. Copy a formula exactly**

control + '- Copies a formula from the cell above to the currently selected cell**Exactly**and exits the cell in edit mode.

This is a quick way to make an exact copy of a formula.**without changing the cell references**. For example, to copy a formula from cell A1 to cell A2 so that the references don't change, select A2 and presscontrol + '.

UseDon't confuse the shortcutcontrol + '(Ctrl + inverted comma) copies exactly one formula from the cell aboveCtrl + `(ctrl + engraved accent key) which will be activatedShow formula mode in excel.

Well, that's all I have to say about copying formulas in Excel. If you know other methods to quickly move or copy formulas in Excel spreadsheet, please share. Thanks for reading and I hope to see you on our blog next week!

## You might also be interested in

- How to create formulas in Excel
- Microsoft Excel formulas with examples
- How to view formulas in Excel
- How to hide and lock formulas in Excel
- Edit, evaluate and debug formulas in Excel
- Excel formulas not working, not updating, not calculating: fixes and solutions

## FAQs

### How do I keep the cell reference when copying formulas in Excel? ›

For example, if you copy the formula =A2+B2 from cell C2 to C3, the formula references in C3 adjust downward by one row and become =A3+B3. If you want to maintain the original cell reference when you copy it, **you "lock" it by putting a dollar sign ($) before the cell and column references**.

**How do you change all cell references in a formula? ›**

To change the type of cell reference: **Select the cell that contains the formula.** **, select the reference that you want to change.** **Press F4 to switch between the reference types**.

**When you copy a formula Excel edits cell references in the newly copied formula? ›**

The correct answer is **Relative Reference**. With relative cell referencing, when we copy a formula from one area of the worksheet to another, it records the position of the cell relative to the cell that originally contained the formula. This is the default mode of referencing in a spreadsheet.

**How do I automatically update cell references in Excel? ›**

**To change this to automatic in the various versions of Excel:**

- 2003: Tools > Options > Calculation > Calculation > Automatic.
- 2007: Office button > Excel options > Formulas > Workbook Calculation > Automatic.
- 2010 and newer: File > Options > Formulas > Workbook Calculation > Automatic.

**How do I create a dynamic cell reference in Excel? ›**

To create an Excel dynamic reference to any of the above named ranges, just **enter its name in some cell, say G1, and refer to that cell from an Indirect formula =INDIRECT(G1)** .

**When you copy and paste a formula to a new location the formula's relative references do not change? ›**

**A relative cell reference in a formula will not change when the formula is copied**. By default, when a cell reference is first included in a formula (so that only the column and row reference are included), it is displayed as a relative cell reference.

**Does Excel automatically adjust cell references to reflect new formula locations? ›**

**Excel automatically adjusts cell references to reflect new formula locations**.

**Which cell reference will not change if copied or moved? ›**

**Absolute references**

There may be times when you do not want a cell reference to change when filling cells. Unlike relative references, absolute references do not change when copied or filled.

**Which references are updated when you copy the formula? ›**

There are two types of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant no matter where they are copied.

**How do I use the same formula for an entire column in Excel? ›**

**Fill formulas into adjacent cells**

- Select the cell with the formula and the adjacent cells you want to fill.
- Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.

### Does AutoFill automatically adjust formulas with relative cell references? ›

Relative Cell References

This is the most widely used type of cell reference in formulas. **Relative cell references are basic cell references that adjust and change when copied or when using AutoFill**. Example: =SUM(B5:B8), as shown below, changes to =SUM(C5:C8) when copied across to the next cell.

**Why is my Excel formula not updating cell reference? ›**

When Excel formulas are not updating automatically, **most likely it's because the Calculation setting has been changed to Manual instead of Automatic**. To fix this, just set the Calculation option to Automatic again.

**Why does Excel keep changing to R1C1 reference style? ›**

More likely is that **you opened up a spreadsheet (probably from another person) that was set to R1C1 style**. Once it is opened, Excel assumes that you want that as a default, so from then on, all spreadsheets you open (even if they were A1 format) will convert to R1C1.

**What is a dynamic formula in Excel? ›**

Dynamic Formulas **allow you to insert Excel's formulas into cells even when the formula must reference rows that will be inserted later during the merge process**. They can repeat for each inserted row or use only the cell where the field is placed.

**How do you create a formula using relative cell references? ›**

**To create and copy a formula using relative references:**

- Select the cell that will contain the formula. ...
- Enter the formula to calculate the desired value. ...
- Press Enter on your keyboard. ...
- Locate the fill handle in the bottom-right corner of the desired cell. ...
- Click and drag the fill handle over the cells you want to fill.