Excel - Point to a New Range - Episode 1741

877 views · Published 1 July 2013 · 3:05 · Indexed 4 November 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial - Change References in a Formula to Point to New Ranges.

Welcome to another episode of the MrExcel podcast! In today's episode, we will be discussing how to point all 5 references in a formula to a new range. This question was posted on YouTube in reference to episode 1455, and we will be exploring different methods to achieve this task.

In episode 1455, we talked about using the Edit mode and dragging the references to a new spot. However, in this particular formula, there are duplicate references which makes it a tedious task to drag and drop multiple times. So, is there a better way to do this? Let's find out.

I have another copy of the worksheet here and I will show you a different approach. First, we need to put the formula in Edit mode by pressing F2. Then, in the formula bar, we can select the entire range of references by starting after the quotation mark and going all the way back to the beginning of the word INDEX. Now, by pressing F4, we can remove all the $ signs from the references. We need to press F4 three times to achieve this. This will make all the references relative except for the one we want to remain constant, in this case, BT7.

Now, we can copy the formula and paste it in the new location, and all the references will automatically change to the new range. To make the same change in the original location, we can cut the formula and paste it back, and it will now point to the new range. This method saves us from manually changing each reference and makes the process much faster.

One important thing to note is that this method will only work if the selection includes more than just the self-references at the beginning and end. So, make sure to select a larger range to make this trick work. This is a great tip to keep in mind when you need to change multiple $ signs in a formula.

I hope you found this episode helpful. Don't forget to tune in next time for another netcast from MrExcel. Thank you for watching and see you soon!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction and Sponsorship  
(00:15) Formula with Constant and Moving References 
(00:30) Alternative Method 
(01:01) Removing $ from Multiple References 
(01:37) Copying and Pasting with Changed References 
(02:12) Changing Multiple References at Once 
(02:44) Trick for Changing $ 
(02:55) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Ctrl+C
Ctrl+V
Ctrl+X
Edit mode
Episode 1741
F4 key
Formula bar
Learn Excel
Point all 5 References to a New Range
Relative references

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153563/


You have a formula with 6 absolute references. You want to keep one of those constant, but change the other 5 references to point to a new range. Today's episode shows two methods - one involves dragging a range five times, the other is even more convoluted. However - the big discovery in this episode is that you can use F4 to change all nine references from Absolute to Relative, provided your selection does not begin or end on a cell reference.

More from this channel