How one can copy formula in Microsoft Excel: down a column, without changing references

It was well known that copying the formulas in Microsoft Excel is considered one of the easiest tasks done with a mouse click. We primarily say "usually" it is because of the reason that very specific cases require the special tricks, such as copying a range of formulas without changing cell references.

Besides all these, the respective Microsoft Excel basically offers many methods to do the same task, which is true for the purpose of copying out the formulas as well.

In this tutorial, we will effectively discussing the different ways to copy formulas in Microsoft Excel in order to choose the best suited for our task.

1. Copying the formula down a column in Excel.
2. Copying out the respective formula to the entire column in Microsoft Excel.
3. Copying down all the formulas to a non-adjacent cell or the respective ranges.
4. How can we enter the formula into the various multiple cells?
5. How can we copy down the formula but not the formatting in the Excel sheet?
6. How can one copy all the formulas without changing out the references in the Excel sheet?

How can one copy a formula down a column in Microsoft Excel?

Microsoft Excel provides a quick way to copy the formula down a column. We need to do the following things as well:

Step 1: First, we will enter a formula in the top cell.

Step 2: Now in these step, we will be then selecting out the particular cell with the given formula and hover the mouse cursor over a small square just at the lower right-hand corner of the respective cell, that is known as the Fill handle. As we do this, the cursor will change to a thick black cross effectively.

Step 3: After that, we will be holding as well as dragging out the respective fill handle down the column just over the cells where we basically want to copy the formula.

And similarly, we can efficiently drag the formula into the adjacent cells to the right, left, or upwards respectively.

And let us assume that, if in case the given formula includes various relative cell references then in that case, it will automatically get change based on the relative position of the particular rows as well as the columns. So, after copying the formula, we will be verifying that the cell references have been adjusted properly after then it will be producing the result which is our basic needs as well. If necessary, the switch between absolute, relative as well as mixed references can be effectively used by making use of the respective Function F4 key from the keyboard:

In the above example, to ensure that the formula was copied correctly, let us select some cells in column C, we say that column C4, and then we will view the cell reference in the formula bar. As it could be clearly depicted in the below screenshot as well, the formula is all right - as relative to particular row 4:

How one can copy a formula down without copying formatting in Excel?

We can easily copy down a formula by just dragging out the fill handle; as it copies the formula and the respective source cells' formatting, like font or the background color, currency symbols, as well as the number of displayed decimal places. But in some of the cases, this exactly works fine, but some of the times it could be mess up with the existing formats in cells in which the formula is being copied to. And one of the most common examples is overwriting of the alternate row shading:

And to prevent the overwriting of the particular existing cell formatting, we will drag the fill handle as demonstrated above and release it. Then after that we will be just clicking on the Auto Fill Options from the drop-down menu and will be selecting out the Fill without Formatting as well.

Copying out the formula to the entire column in Microsoft Excel

As we have just seen, the fill handle makes copying the respective formulas in Microsoft Excel very easy. But what if in case we need to copy a formula down a ten-hundred-line sheet in Excel? Then in that scenario dragging out the formula over hundreds of rows is not a good idea. And unfortunately, Microsoft Excel has provides a several of quick solutions to effectively deal with this case as well.

Double-clicking on the plus sign in order to fill the whole column

Now in this we will be double-clicking on the respective plus sign for the purpose of applying the given formula to the entire column instead of just dragging it. And for this we need to follow the below mentioned steps as well.

Step 1: First of all, we will be providing the input to our formula just at the top of the cell.

Step 2: Now after that, we will be the positioning the cursor to the lower right corner of the cell with the given formula as well, and waiting until it turns into the plus sign, and then we will be double-clicking on the plus sign.

How can one create an Excel table to automatically copy a formula to all cells in a column in Microsoft Excel?

The other great features of Microsoft Excel, such as predefined styles, sorting of the data, filtering as well as the banded rows, and automatically calculating the columns, make the Microsoft Excel table a truly wonderful tool to analyze groups of related data as well.

And now by entering the formula into one cell in a given table column, then we can easily create a calculated column and can instantly copy our formula to all other cells in that particular column as well. Unlike the fill handle, Microsoft Excel tables have no problem with copying the formula across the whole selected column, even if the Table has one or more empty rows.

And for the purpose of converting a range of cells to the respective Microsoft Excel table, we can select all the cells and then we will be pressing the shortcut that is: Ctrl + T from our keyboard, and if we prefer a visual way, then in that case we will select the range, go to the Insert tab > Tables group on the Excel ribbon, and then click the Table button effectively.

Note: It should be noted that, if in case we do not want a particular Microsoft Excel table in our worksheet, then we can easily create it temporarily to make the work with formulas much easier, and then we can convert the Table back to a usual range in a second.

How one can Copy a formula to non-adjacent cells/ranges in Microsoft Excel?

The respective fill handle in the Excel is termed to be the fastest way for the purpose of copying out formulas in Microsoft Excel. But what if in case we want to copy our Excel formula in non-contiguous cells? Just make use of the old good Copy as well as the paste way:

Step 1: First, we will click out the cell with the formula to select it.

Step 2: We will press the Ctrl + C to copy the formula.

Step 3: Now in these step, we will be selecting a cell or a range of cells in which we want to paste the formula.

Step 4: We will press the Ctrl + V to pass out the formula.

Step 5: Now, after that, we will be pressing out the Enter button to complete the pasted formulas respectively:

Entering out a formula into multiple cells with a single keystroke (Ctrl + Enter) in Microsoft Excel

It was well known that, in most of the cases, when we are required to input the same formula in just more than one cell on a given worksheet, either adjacent or non-adjacent ones, than this method can also be a time-saver as well.

Step 1: Firstly we will be selecting all the respective cells in which we want to enter the formula. And to choose non-contiguous cells, we will be efficiently pressing as well as holding the Ctrl key respectively.

Step 2: We will press F2 to enter the edit mode respectively.

Step 3: We will input our formula in one cell and then press the Ctrl + Enter from the keyboard instead of Enter. And after that, the respective used formula will get copied to all of the selected cells, and Microsoft Excel will adjust relative cell references in accordance to use as well:

How we can copy an Excel formula but not formatting in Excel?

It was well known that, when we are copying a formula down a column in Microsoft Excel, then in that scenario, we can basically make use of the " Fill without Formatting option" available in the Microsoft Excel that will enable us to copy the formula but it will keep the existing formatting of the destination cells as well.

And moreover, the Microsoft Excel's Copy & Pastefeature offers even more flexibility with regard to paste options in Excel.

Step 1: First, we will select the particular cell containing the formula.

Step 2: Now, we will copy that cell by pressing the keyboard's Ctrl + C shortcut button. Alternatively, we will be right-clicking on the cell, and then after that, we will select the Copy from the context menu or click on the Copy button present on the Home tab > Clipboard, respectively.

Step 3: After that, we will select all the cells we want to copy the formula.

Step 4: Then after we will Right-clicking on the particular selected cells and choose out the Formulas under Paste Options as well:

And for more paste options, we will click on the arrow just below the Paste button on the ribbon.

• For example: We can select out the respective Formulas as well as Number Formatting for the purpose of pasting only the formula as well as the number formatting like percent format, currency format.

Copying out the formula in Excel without changing references

We all know that, the Microsoft Excel formulas primarily occur in a given particular spreadsheet in solitude manner. But in most of the cases, we can easily enter out the formula in one cell and then copy it to other cells in the same column or the row in order perform the same calculation on a data group as well.

And if our formula contains the relative cell references (without $), Excel will automatically adjust them so that each formula operates on the given data in its row or column.

• For example: If we have the formula as =A1*2 in the particular cell B1, and we copy this formula to cell B3, then the formula will change to =A3*2 respectively.

But what if in case we want our Microsoft Excel to copy the formula exactly without making and kind of changes in the particular cell references along the way? As it was depending upon our specific task, so in accordance to that we can choose one of the following solutions wisely.

How one can copy or move a single formula without changing cell references in Microsoft Excel?

If we need to copy or move out a single formula, by making an exact copy of it is much easy in comparison to the other.

Step 1: First, we will select the individual cell with the formula we want to copy.

Step 2: In these step, we will be selecting out the formula in the formula bar by making use of the mouse, and then will press the Ctrl + C shortcut to copy it. And if in case we want to move the formula, then in that particular case, we will press the Ctrl + X shortcut from the keyboard to cut it respectively:

Step 3: After that, we will press the Esc key to exit the formula bar.

Step 4: We will then select the destination cell, and after that, we will press out the Ctrl + V shortcut from the keyboard to paste the formula there as well.

And alternatively, we can enter the editing mode and then copy out the formula in the cell as text respectively:

Step 1: First, we will be selecting a cell with the formula.

Step 2: Now, we will be pressing out the Function F2 (or double-clicking the cell) to get enter into the editing mode respectively.

Step 3: After that, we will select the formula in the particular cell using the mouse, pressing out the Ctrl + C shortcut from the keyboard to copy it effectively.

Step 5: At last we will be then selecting out the destination cell and will be pressing the Ctrl+V shortcut from the keyboard, as this will be pasting all the formula exactly without changing the cell references in the respective Excel sheet. It is because the formula was copied in the form of the text.

Note: To quickly copy a formula from the above particular cell with no reference changed, in that case, one easily selects the specific cell where we want to paste the formula and then press Ctrl + '.

How one can Copy a range of formulas without changing cell references in Microsoft Excel?

Now, for the purpose of moving or copying a range of Microsoft Excel formulas so that no cell references are getting changed, so in that case we can make use of the following methods respectively.

# Method 1: Effective use of absolute or mixed cell references in Excel

If in case we need to make an exact copy of the particular formulas with the help of the relative cell references (such as A1), and the best way, would be just changing them to absolute references in order to fix the reference to a given particular cell, so that it would remain static no matter where the formula moves out.

Moreover, in some of the cases, we may require to make use of the mixed cell references for the purpose of locking either a column or row respectively. Does it make little sense so far? Okay, let us consider the following example for its better understanding.

Let us suppose that we have a table that will be calculating out the fruit prices in EUR that are primarily based on the USD price as mentioned in the given column B as well as the exchange rate in cell C2.

As per the above screenshot, the respective formula will includes an absolute cell reference for the purpose of fixing the exchange rate to particular cell that is C2 and a relative cell reference to cell B5 as well: and we want these references for just making an adjustment for each row.

But now let us see what happens if we need, say, to move the respective EUR prices from column C to column F. If we copy the formulas in a usual way by just copying or pasting the cells, the formula from cell C5 (=B5*$C$2) will get the change to =D5*$C$2 when it was pasted in cell F5, making our calculations all wrong as well.

Besides all these, for the purpose of fixing this, we can change a relative reference (B5) to a cross reference $B5 by putting out the dollar sign ($) just in front of the column letter as well, which we anchor as the reference to that particular column B.

And now, if in case we are copying or moving the formulas from the respective column D to column F, then in that case, the column reference would not be get changed this is because of the reason that we have locked it with the help of the dollar sign ($B5) respectively.

Besides all these, the concept of the Microsoft Excel references may be quite difficult in order to grasp from the outset, but it is much worth our time as well as effort.

For example: Now, in this example, let us see how we can effectively calculate the entire Table with a single formula using the mixed cell references.

However, if we have already a ton of formulas with the relative cell references in our Microsoft Excel sheets, then we just need to make an exact copy of those formulas quickly.

# Method 2: How can one Copy Excel formulas without changing references via Notepad in Microsoft Excel?

Step 1: First, we will enter the formula view mode by just pressing the Ctrl + ` shortcut from our keyboard.

Step 2: Now, in this step, we will select all the respective cells with the formulas which we want to copy or move in the sheet as well.

Step 3: Now, we will press the Ctrl + C to copy the formulas or use the Ctrl + X shortcut to cut them effectively.

Step 4: Now in these step, we will be then opening a Notepad or any other text editor after that we will be pressing out the Ctrl + V shortcut from our keyboard in order to paste the formulas as well, Then we will be pressing the Ctrl + A shortcut for the purpose of selecting all the formulas and Ctrl + C shortcut to copy them in the form of the text respectively.

Step 5: And in our particular Excel worksheet, we will be selecting the upper-left cell in which we we want to paste the formulas and press the shortcut Ctrl+V respectively.

Important Notes:

1. We can easily paste out the formulas only in the same worksheet in which our original formulas are located unless the references include the sheet name respectively. If not, then the respective otherwise formulas will be broken.
2. And the next important point is that the particular worksheet should be in formula view mode as well; to verify this, we will go to the Formulas tab > Formula Auditing group, and then we will check if the Show Formulas button is toggled on or not.
3. And after pasting out the formulas, we will press the Ctrl + ` shortcut from our keyboard to toggle off the formula view mode.

# Method 3: Copying out the formulas exactly by just making use of Excel's Find and Replace

We can effectively make use of the Excel Find and Replace feature for the purpose of copying out a range of Microsoft Excel formulas without changing their cell references as well.

Step 1: First of all, we will be selecting out the cells with the formulas which we want to copy respectively.

Step 2: Now, after that, On the Home tab, we will go to the Editing group and then will click on Find & Select > Replace... Or, press out the shortcut from the keyboard, Ctrl + H, which is the shortcut to launch the Find and Replace dialog in Microsoft Excel.

Step 3: And in the Find & Replace dialog window which is available in the Excel sheet, we will be typing the equal sign (=) in the Find what box. In the Replace with box, input some symbol or a string of characters which is not used in our formulas, like # or \.

Note. It was noted that we should not make use of an asterisk (*) or the question mark (?) for replacement since these are the wildcard characters supported in Microsoft Excel.

Step 4: In this step, we will click on the Replace All button and close the Find and Replace dialog. All the formulas in the selected range will turn into text strings as well:

Step 5: Now, we can effectively select any particular cells, and then we will press Ctrl + C shortcut to copy them. And then we will be selecting the top cell in the current worksheet in which we we want to paste the formulas and will be pressing the shortcut button that is: Ctrl + V. It is because of the reason that the Microsoft Excel does not interpret the formulas without the equal sign, and this would be get copied exactly, without changing references respectively.

Step 6: After that, we will use the Find & Replace again to reverse the change, making selection of both the regions with the help of the original formulas and copied ones (for the purpose of selecting the non-adjacent areas, pressing and holding Ctrl). Press Ctrl + H to open the Find & Replace dialog. This time, enter the backslash (\) (or any other character we used for the first replacement as well) in the Find what box, and = in the Replace with box, and click on the Replace All button, respectively.

Shortcuts to copy Microsoft Excel formulas to other cells

1. Copy a formula down

Ctrl + D - This shortcut from the keyboard will help us to efficiently copy a formula from the given cell above and then adjust the cell references respectively.

* For example: If in case we are having a formula in the respective cell A1 and we want to copy it to cell A2, then in that case we must need to select A2 and press Ctrl + D.

2. Copy a formula to the right

Ctrl + R - This shortcut will help us effectively copy a formula from the given cell to the left and then adjust the cell references as well.

• For example: If in case we are having a formula in cell A2 and we want to copy it to the other cell that is cell B2, then in that case, we will be selecting the cell B2 and pressing the shortcut Ctrl + R respectively.

Tip. Remember that both shortcuts can efficiently copy formulas to multiple cells.