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.

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.

Introduction to the Dataset

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

Quickest Way to Copy a Formula in Excel with Changing Cell References

The quickest method literally takes 2 seconds to copy a formula with changing cell references.

Now Follow the Guide ↓

Step_1: Select an empty cell.

I selected D2.

Step_2: Write your formula.

I used the following formula in my data table:

=C2*1.5

Applying a Formula to Copy in Excel with Changing Cell References

Step_3: Press ENTER to apply the formula.

Step_4: Put the mouse cursor on the right-bottom corner of cell D2.

The Fill Handle β€˜+’ sign will show up instead of the cursor arrow.

Step_5: Double-click on the Fill Handle icon.

Double-click on Fill Handle icon to Copy a Formula in Excel with Changing Cell References

Final Result ↓

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

Result of Dragging down Fill Handle to Copy a Formula in Excel with Changing Cell References

πŸ”—Β 9 ways to Copy Formula Down in Excel without Dragging

Alternative Way #1: Drag Down Fill Handle to Copy a Formula in Excel with Changing Cell References

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.

Now Follow the Guide ↓

Step_1: Type the formula in cell D2.

Step_2: Press ENTER.

Step_3: Take the mouse cursor on the right-bottom corner of cell D2.

The mouse cursor will turn into Fill Handle.

Step_4: Press on the Fill Handle with your left mouse button and drag it from cell D2 to D8.

Drag Down the Fill Handle to Copy a Formula in Excel with Changing Cell References

Final Result ↓

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

Alternative Way #2: Insert an Excel Table to Copy a Formula in Excel with Changing Cell References

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.

Now Follow The Guide ↓

Step_1: Select the entire data table.

Step_2: Select Insert from the ribbon.

Step_3: In the Tables group, click on the Table option (Or, just press CTRL+T).

Select the Table option to Copy a Formula in Excel with Changing Cell References

A dialog box named Create Table will come along on the sheet.

Step_4: Check the My Table Has Headers checkbox has a checkmark.

Step_5: Click on OK.

Insert an Excel Table to Copy a Formula in Excel with Changing Cell References

Now you will notice that your data table is arranged in an Excel Table.

Step_6: Type a formula in cell D2.
Step_7:
Press ENTER.

Applying a formula in Excel Table to Copy it in Excel with Changing Cell References

Final Result ↓

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

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

πŸ”—Β 5+ Proven Methods to Copy a Formula to Entire Column in Excel

πŸ”—Β 4+ Ways to Repeat Formula Pattern in Excel

Alternative Way #3: Copy Multiple Formulas to Multiple Cells in Excel with Changing Cell References

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.

Now Follow the Guide ↓

Step_1: Type this formula in cell D6: Β 

=$C6*D$3

Use Cell Reference to Copy a Formula in Excel with Changing Cell References

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.

Step_2: Press ENTER.

Step_3: Double-click on the Fill Handle.

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

Drag the Fill Handle to the right to Copy a Formula in Excel with Changing Cell References

Final Result ↓

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

πŸ”—Β Apply Same Formula to Multiple Cells in Excel (5+ Solutions)

Alternative Way #4: Use COPY-PASTE to Copy a Formula in Excel with Changing Cell References

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 ↓

Step_1: Write a formula in cell D6.

Step_2: Press ENTER.

Step_3: Select the cell and press CTRL+C.

Small dash marks will appear around the cell.

Press CTRL+C Keyboard Shortcut to Copy a Formula in Excel with Changing Cell References

Step_4: Select the cell range D6:E12 with the white β€˜+’ sign cursor.

Step_5: Press CTRL+V.

Final Result ↓

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

Press CTRL+V to Copy a Formula in Excel with Changing Cell References

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.

Alternative Way #5: 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.

Now Follow the Guide ↓

Step_1: Apply a formula in the first cell of a column of unadjusted cells.

I applied the previously mentioned formula in cell D2.

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

Step_2: Put your Fill Handle in the right-bottom corner of D2.

Step_3: Select the column (column D).

Step_4: Press CTRL+D.

Select the column and press CTRL+D to Copy a Formula in Excel with Changing Cell References but Cells Are Unadjusted

Final Result ↓

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

Result of Copying a Formula in Excel with Changing Cell References but Cells Are Unadjusted

πŸ”—Β Copy Excel Sheet to Another Sheet with Same Format and Formulas

Alternative Way #6: Use CTRL+D Keyboard Shortcut to Copy a Formula in Excel with Changing Cell References

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 Follow the Guide ↓

Step_1: Apply a formula in cell D6.

Step_2: Select the column (column D).

Step_3: Press CTRL+D.

Final Result ↓

CTRL+D only works for cells on the downside.

Use CTRL+D Keyboard Shortcut to Copy a Formula in Excel with Changing Cell References

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

Use CTRL+D and CTRL+R Keyboard ShortcutS to Copy a Formula in Excel with Changing Cell References

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.

Now Follow the Guide ↓

Step_1: 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.

Step_2: Select the number series B2 to B5.

Step_3: Put the mouse cursor on cell B5, and the Fill Handle will come up.

Step_4: Drag the Fill Handle to the rest of the cells of the column.

Final Result ↓

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

Fill a Series of Numbers to Autofill Serial Numbers in Excel

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 apply 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.

(Visited 158 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *