6+ Ways to Copy a Formula in Excel with Changing Cell References

Do you want to copy a formula in Excel with changing cell references? – Well there is a number of ways to do that. In this article, I will be showing you 6+ easy methods to solve your problem of copying formulas with changing cell references.

Copy a Formula in Excel.xlsx

But before moving into the tutorial, think you all know what is a cell reference and you also know the cell reference is like a variable used in a formula.

I am going to use a dataset of the sales records of an electronics shop. The shop imports its products at a fixed price and sells them with a 1.5 times profit.

Table of Contents

Copy a Formula in Excel with Changing Cell References by Double-clicking on Fill Handle

The quickest method literally takes 2 seconds to copy a formula with changing cell references. So, follow these steps below to do that:

Select an empty cell.
I selected D2.

Write your formula.
I used the following formula in my data table: =C2*1.5
Press ENTER to apply the formula.
Put the mouse cursor on the right-bottom corner of cell D2.
The Fill Handle ‘+’ sign will show up instead of the cursor arrow.

Double-click on the Fill Handle icon.

The Fill Handle has done its job of copying the formula all through column D with changing cell references.

9 ways to Copy Formula Down in Excel without Dragging

Copy a Formula in Excel with Changing Cell References Draggin Down Fill Handle

This method is also about Fill Handle. But this time you can control the Fill Handle manually by dragging it down through as many as cells you like. Here are the steps to do that for copying formula with changing cell references:

Select a cell.
Type the formula.
Press ENTER.

Take the mouse cursor on the right-bottom corner of cell D2.
The mouse cursor will turn into Fill Handle.
Press on the Fill Handle with your left mouse button and drag it from cell D2 to D8.

This way you can easily copy a formula throughout any column.

3 Ways to Copy a Formula in Excel without Changing Cell References

4 Ways to Fill Formula Down to Specific Row in Excel

Copy a Formula in Excel with Changing Cell References Inserting an Excel Table

The Excel Table is a pretty nifty feature. If you apply the Excel Table to your data table and then apply a formula in any cell of a column, it will automatically copy the formula through the entire column. So go through these steps:

Select the entire data table.

Select Insert from the ribbon.
In the Tables group, click on the Table option (Or, just press CTRL+T).
A dialog box named Create Table will come along on the sheet.
Check the My Table Has Headers checkbox has a checkmark.

Click on OK.
Now, you will notice that your data table is arranged in an Excel Table.
Type a formula in cell D2.
Press ENTER.

The formula will instantly copy down through the entire column D.

Copy Multiple Formulas to Multiple Cells in Excel with Changing Cell References by Fill Handle

Now let’s say, you want to copy multiple formulas in multiple cells with changing cell references. In that case, you will need to modify the cell references in the formula and then you are good to go.

Consider a situation where we have to find out the selling price of some imported electronic products in a shop. The shop increased the price of electronic products twice. At first, the price increase was 1.5 times and then the shop increased the price 2.5 times.

We will find out the Selling price after a 1.5 times increase (column D: Selling price 1) and 2.5 times increase (column E: Selling price 2) with one formula.

Formula

=$C6*D$3

Formula Explanation:

Here in $C6, the dollar sign ‘$’ is used before C, that’s because we want to fix the column number and want to move the row value. So, the $C6 will change into $C7, $C8, $C9, $C10

For about D$3, you can see the dollar sign ‘$’ is before 3. That means, the row number is not going to move for the formula but the column number will change. So If we drag the Fill Handle to the right, column D will be column E.

Steps to copy multiple formulas in multiple cells using the Fill Handle icon:

Select a cell.

Type this formula in cell D6: =$C6*D$3
Press ENTER.
Double-click on the Fill Handle.

Put the mouse cursor on the right-bottom corner of cell D12 and just drag the Fill Handle to the right side.

This way you can multiply cell values in multiple cells using a single formula.

Apply Same Formula to Multiple Cells in Excel (5+ Solutions)

Copy a Formula in Excel with Changing Cell References with Using COPY-PASTE

Applying the same formula in multiple cells can be done in many ways. I am going to show you another easy way to copy a formula with changing cell references using the traditional COPY-PASTE method. Now, follow the guide below:

Select a cell.
Write a formula.

Press ENTER.
Select the cell and press CTRL+C.
Small dash marks will appear around the cell.
Select the cell range D6:E12 with the white ‘+’ sign cursor.

Press CTRL+V.

You will see the formula is now copied in the targeted cell range.

But the small dash marks are still visible around cell D6. To remove the small dashes, click on cell D6 to select it and press ENTER. You can also copy a formula in Excel to another sheet using the COPY-PASTE technique.

Copy a Formula in Excel with Changing Cell References but Cells Are Unadjusted

Sometimes you may face a situation where the cells are not adjusted but you want to copy a formula in the unadjusted cells to save your time. Good news! It’s possible. To do that, follow these steps below:

Apply a formula in the first cell of a column of unadjusted cells.
I applied the previously mentioned formula in cell D2.

Put your Fill Handle in the right-bottom corner of D2.
Select the column (column D).
Press CTRL+D.

Now, you have the formula copied with changing cell reference non-adjusted cells.

Copy Excel Sheet to Another Sheet with Same Format and Formulas

Copy a Formula in Excel with Changing Cell References Using Keyboard Shortcut

Instead of using CTRL+C and CTRL+V, try to use CTRL+D from now. It’s even handier in terms of applying formulas in an entire column. Now, go through the procedure below:

Select cell D6.

Apply a formula.
Select the column (column D).
Press CTRL+D.

CTRL+D only works for cells on the downside.

If you want to apply the formula in the right column of your data table, press CTRL+R.

Also Learn: Fill a Series of Numbers to Autofill Serial Numbers in Excel

Microsoft Excel can automatically autofill a series of numbers with a fixed interval. You don’t have to write a number series manually anymore. To autofill serial numbers in Excel, follow these steps below:

Insert the first two (or more than two) values of your desired number series.
For example, I wrote a series of 5, 10, 15, and 20.
Instead of that, I could write the first two values of the series, 5 and 10.

Select the number series B2 to B5.
Put the mouse cursor on cell B5, and the Fill Handle will come up.
Drag the Fill Handle to the rest of the cells of the column.

See how smoothly you can autofill a series of numbers of any interval.

If you want to input just one value in a cell and want to fill the number series, it won’t work. The selected cells will show the same value as that particular cell.

For example, I inserted value 5 in cell B2 and applied the method. The rest of the cells return the value 5 only.

Conclusion

I hope the methods I have shown you in this article, will help you to solve your problems with copying any formula in Excel. If you have any confusion regarding this topic, please do share it with us in the comment section below.

Frequently Asked Questions

How do you update a cell reference in a formula?

To update a cell reference in a formula in Excel:

Select the cell with the formula you want to update.

Click on the formula bar at the top of the Excel window.
Use the arrow keys to move to the part of the formula that contains the reference you want to update.
Change the cell reference as needed. You can type a new cell reference or click on a different cell to update the reference.

Once you’ve updated the reference, press ENTER to confirm the modification.

By following these steps, you can easily update a cell reference within a formula in Excel.

What are the 3 types of cell references in Excel?

The three types of cell references in Excel are:

Relative Reference: Adjusts the reference based on the relative position of the formula and the cell being referred to. When you copy the formula to another cell, the reference updates accordingly.
Absolute Reference: Remains fixed and does not change when the formula is copied to other cells. It is denoted by adding a dollar sign ($) before the column letter, row number, or both.
Mixed Reference: Combines aspects of both relative and absolute references. You can have an absolute column with a relative row, or vice versa. One part is anchored, and the other adjusts when the formula is copied.

Understanding these cell reference types is crucial for creating dynamic and flexible formulas in Excel.

What is AutoFill in Excel?

AutoFill in Excel is a feature that automatically extends a series or copies the content of a cell to adjacent cells, making data entry and formatting more efficient. It is initiated by using the Fill Handle, a small square at the lower-right corner of the selected cell. By dragging or double-clicking the Fill Handle, Excel intelligently detects patterns and automatically fills cells with relevant data, such as numbers, dates, or formulas. AutoFill is a powerful tool for quickly populating cells consistently and logically.

Rate this post