How to Copy a Formula in Excel with Changing Cell References

Do you need to copy formulas or functions in excel with or without changing cell references? There are lots of ideas and techniques to do it without encountering any hassle.

Here you’ll find a list of methods through which you’ll be able to copy any sort of formulas or functions with changing cell references.

If you’re not used to the term ‘Cell Reference’ then you can go here to grab some ideas. It’s not a major context at all but when you’ll have to copy a formula to use elsewhere in the spreadsheet you need to change the input data for another cell, right?

Cell reference will do this trick automatically, and if you need to lock this cell reference so that you don’t want to change the input values by column or rows then you’ll find the process in this article too.

Read More: Copy Formula in Excel by Changing Only One Cell Reference

Download Excel Workbook

Download the Excel Workbook that we used to prepare this article so that you can practice yourself.

14 Easy Methods of Copying a Formula in Excel with Changing Cell References

1. Filling Down the Column Manually

Suppose, a company has decided to increase 10% salary of its five specific employees and here you’re seeing the chart with employees’ names along with their current salaries.

Now, you’ve to find out what will be their new salaries after a 10% increase.

fill down the cell in excel

Due to a 10% increase in the salary of each employee, you have to multiply each one’s current salary by 1.1 to find out the new salary.

To do this, select Cell C2, enter F2 or double-click the left button of the mouse to enable editing. 

Now tap ‘=’, select Cell B2 and multiply with 1.1.

What you’ll be typing can be shown in the Formula Box which has been boxed with red at the top. 

fill down the cell in excel

Now press Enter and you’ll see the new salary for Sam in Cell C2.

To check the new salaries of all other employees, first of all, point your mouse cursor to the bottom right corner of Cell C2.

You’ll notice a ‘+’ sign there.

Click it with your mouse and without releasing the button drag it down to Cell C6 and then release it there.

fill down the cell in excel

Thus you’ll get the amounts of new salaries of all employees.

fill down the cell in excel

This method is called ‘Fill Down’ as you’re filling other cells by dragging with the reference of the 1st cell.


2. Double Clicking the ‘+’ Sign

You can evaluate the increased salaries of all employees by double-clicking the ‘+’ sign too.

Double click plus(+) sign

You have to do the calculation for the 1st cell (C2) only like before.

Then you’ll double-click the ‘+’ option and the results will be displayed at once.

Double click plus(+) sign


3. Creating an Excel Table

This is another very good option to copy formulas with changing cell references.

First of all, select the whole section shown below.

Create table in excel to copy formula

Now choose a table from the Format as Table option under the Home tab.

Create table in excel to copy formula

As this chart got headers so click OK.

Create table in excel to copy formula

Now the table will appear with headers.

Go to Cell C2, put the ‘=’ symbol, select B2 and multiply it with 1.1 like before.

Create table in excel to copy formula

Press Enter and the results will be shown as follows.

Create table in excel to copy formula


4. Copying Formula to Non-Adjacent Cells

Now, for example, there can be gaps sometimes among the rows or even columns in a chart.

If you use the ‘Fill Down’ method here C3 and C6 cells won’t show results or even can display error messages as no calculation will be executed there.

Copy formula to distant or non-adjacent cells

In this case, you have to copy Cell C2 first by right-clicking the mouse on it.

Copy formula to distant or non-adjacent cells

Then you’ll enter CTRL and without releasing it, continue to select the C4, C5, C7 and C8 cells.

Copy formula to distant or non-adjacent cells

Right-click the mouse again and choose the 1st option of Paste functions.

Copy formula to distant or non-adjacent cells

You’ll get your desired results at the right places easily.

Copy formula to distant or non-adjacent cells


5. Using Single Formula for Multiple Columns at Once

Sometimes you have to perform column or row calculations with similar data but with different multipliers.

Here, we want to find out what will be the salaries of the employees with both 10% and 20% increases.

So basically, you need here one common formula but you have to use it for two different columns. 

Using single formula for multiple columns

To do this, you’ll select the array C2:D6 with mouse first and then press F2 to enable editing at C2.

Using single formula for multiple columns

In row 8, two multipliers have been added under the perspective columns to make calculations easier.

So, now only type the formula to multiply cell B2 with C8(1st multiplier) but don’t execute the function right now.

Using single formula for multiple columns

You have to lock column B as well as row 8 by using the ‘$’ sign before them inside the Formula Box to ensure the proper calculations under mixed cell references.

I’ll guide you through this ‘Mixed Cell References’ a few methods later.

Now, you just have to keep in mind that you’re using this ‘$’ symbol to lock the multiplier row(8) for their respective columns, and in the same way, you’re locking Current Salary(Column B) to ensure the perspective calculations of increased salaries in two different cases for each employee. 

Using single formula for multiple columns

After you’re done with these processes, press CTRL+Enter instead of pressing only Enter and you’ll be able to see all the new and increased salaries in both cases of all employees.

Using single formula for multiple columns


6. Copying With Multiple Options

Let’s look at a Series now. 

It’s a series that’ll contain multipliers of 5.

How will you get all the next values in the column with a single click?

Copy with options

Select cells including A2 to A5, keep mouse pointer to the right bottom corner of the A5 cell and then fill down the series.

You’ll get all the required values for the multipliers for 5.

Excel is assigned to Fill Series as default.

Now, let’s check other Copy options.

If you choose Copy Cells, the 1st 4 cells you’ve selected before will be copied downward again and again. 

And if you opt to choose Fill Formatting Only, here only cell pattern or background will be copied, not the values.

Select Fill Without Formatting, you’ll get the whole series but the background of the cell patterns will not be copied here.

Last of all, this is probably the most interesting part.

Suppose, you want to get only first names from a list of full names.

So, in the column of First Name, type it once for the 1st one only.

Then like previous methods, Fill Down the B2 cell and select Flash Fill.

And all the first names will be displayed in the column right away.

 


7. Matching Your Criteria by Choosing from Different Paste Options

Like Copy options, almost similar or more options you’ll find in Paste options too.

If you want to copy column C to E, then select the column range you want to copy and it includes formulas too.

Matching criteria with paste options

Now, right-click the mouse on cell E2 and you’ll find a variety of Paste options.

The 1st one will include the formulas you used for column C.

But you have to lock column B otherwise during pasting at column E, values from column B won’t appear here, rather the empty cells in column E will want to be multiplied with 1.1 and the error messages will be shown.

If you choose the Values (V) option then only values will be copied from column C, not formulas or functions.

And if you go for Paste Link, then the calculated values will be shown in column E, this option will actually copy both values and functions assigned for column C.

There are some other Paste options that you can find through Paste Special tab.

You can select a wide variety of options to paste the values or formulas or both depending upon your required criteria.


8. Copying the Function(s) from Formula Bar

You can even copy the formula from Formula Bar directly and use it in any cell you want.

Here, at first, calculate the total current salaries of 5 employees by using the Sum function.

Copy from formula bar

Focus on the Formula Bar and cut this formula instead of copying.

If you copy this formula you have to lock the cell reference to paste elsewhere.

Copy formula from formula bar

Now paste it on Cell F8 and you’ll get the calculated value with the formula there.

Copy from formula bar

But since you cut the formula so the value from Cell B8 will have vanished and you’ll have to paste the same function again on Cell B8 and the calculated value will be back.

Copy from formula bar


9. If You Don’t Want to Change the Cell References

This is a tricky part now but you’ll find it interesting I hope.

I’ve already given a bit of idea on this topic when discussing the 5th method where you had to copy one single formula to use for two different columns.

Let’s take a look in detail now.

Like before, you’ll have to find out the increased salaries both with 10% and 20% increments. 

Copy formula without changing cell references

Select the Array C2:D6 and turn on editing by pressing F2.

Multiply B2 with C8 and press CTRL+Enter.

Copy formula without changing cell references

You’ll see the calculation has been made only for Sam but others have been denied. 

The reason is you haven’t locked the cell references.

So how and when will you lock them?! 

Before knowing that, let’s have a glance at Cell C3. 

Go to the Formula Bar and you’ll see the calculation has been executed based on the multiplication between Robert’s current salary(B3) and an empty cell(C9)!

This is why the result has been displayed as $0.00.

So, you’ve already got that here cell references are moving from cell to cell and you have to lock these cell references now based on the required criteria.

Copy formula without changing cell references

Let’s select the whole array (C2:D6) again and press F2 to enable editing mode.

Go to the Formula Bar.

Put a Dollar($) sign before B, this will lock column B and also put it again before 8, it’ll lock the 8th row containing the multipliers which you don’t want to circulate over the whole excel sheet.

Press CTRL+Enter & you’ll have your expected calculations.

Copy formula without changing cell references

So what’s happening here? 

As you’ve locked column B and row no. 8, now under column C, all employees’ salaries will be multiplied by 1.1 from C2 and similarly, under column D, salaries with 20% increments will be obtained for the multiplier 1.2 from D8.


10. Exposing the Formulas

To follow this method here, you have to calculate the increased salaries first by filling down the cells like before. 

Copy by showing formulas

Under the Formulas tab, click on Show Formulas. 

Under column C, you’ll see the functions executed in each cell.

Copy by showing formulas

Now copy these formulas.

Make a new chart under column E. 

Paste the formulas at E2 by selecting the Values(V) option.

You’ll get all the desired values at once.

If you opt to choose other Paste Options then the formulas will be shown here under column E but values won’t be displayed here as the Show Formula button has been kept on.

You can paste these formulas anywhere in the excel sheet to copy the calculated values.


11. Using the ‘Find & Replace’ Button

This is really a fruitful method and you can find more fun here.

In column C initially, like the previous method, formulas are exposed. 

But now we’re going to choose the Find & Replace option from the Home tab after selecting the column range C2:C6.

 Next, you have to replace the ‘=’ symbol with ‘/’ or any other symbol which has not been used already in the spreadsheet.

Tap Replace All.

Thus in column C, the formulas will turn into text strings which you can copy anywhere you want.

Copy by using find and replace option

Let’s go to column H and paste column C there first.

Now select the whole column H, open the Find & Replace tab again and reverse those symbols which you’ve used before for column C.

Choose the option Replace All again and you’ll have turned the text strings into number functions with this process.

Copy by using find and replace option

Now turn off the Show Formulas.

Copy by using find and replace option

The calculated values will be obtained in column H.

Copy by using find and replace option


12. Using CTRL+D & CTRL+R for Immediate Next Cell to Fill

Copy using CTRL+D & CTRL+R options

You can use CTRL+D or CTRL+R for the immediate next cell to fill one by one.

After doing the initial calculation in C2, go to C3 and use CTRL+D for going downward calculation and use CTRL+R to move to the right.

Basically, this method can be useful for a very small amount of data calculation.


13. Creating a Notepad and Pasting the Formula Column to Use Later

Expose the formulas again.

Copy the formulas from column C to a notepad.

Copy formulas by using notepad

Now paste the formulas from the notepad in column E.

Copy formulas by using notepad

Turn off the Show Formulas tab and you’ll get the values again.

This method is quite effective when you have to keep track of the calculated data but you cannot expose the formulas while copying those values to another column.

Copy formulas by using notepad


14. Choosing ‘AutoSum’ or ‘SUBTOTAL’ Function

If you don’t want to type any function manually you can choose this AutoSum option under the Formulas tab where you’ll get your desired functions for basic and simple calculations.

Copy using autosum

Here summation has been done for Column B with the help of the Autosum feature.

Copy using autosum

Subtotal is another similar function you can use to find out the results by going through up to 111 parameters from the list.

Copy using subtotal

Conclusion

So, here are all the basic techniques you need to know and use in your daily Excel activities when you’re assigned to analyze tons of data. You’re welcome to let me know any kind of suggestion or opinion in the comment box. I’ll catch you up with your valuable words!


Further Readings

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo