Make all cells references in a long formula absolute


Hello All:


I have a long formula with many different cell references. Is there a way to make all the different cells references absolute in one shot without F4 every cell reference in the formula?

For example i want all G77 in the formula below to be $G$77

=MIN('LC0-1.4D'!G77,'LC1-1.2D +1.6L'!G77,'LC2-1.2D+1.6L,M2M4M6,LL=0 '!G77,'LC3-1.2D+1.6LM1M3M5M7,LL=0 '!G77,'LC4-1.2D+1.6L M3M6,LL=0 '!G77,'LC5-1.2D+1.6LM1M4M7,LL=0 '!G77,'LC6-1.2D+1.6L M2M5,LL=0 '!G77)




12 Replies

Hello @SamFares,


To my knowledge, there is no quicker way than simply using F4 on each cell reference. My suggestion would have been to use find and replace (i.e. find G and replace with $G$) but seeing as though your formula uses cell references beyond the first 26 columns (i.e. LC1, LC2, LL, etc...) this would complicate things. Without creating a macro, I'm afraid my best suggestion is to simply use F4.


Sorry if this isn't the answer you were looking for!


Define a Name to reference the cell (by default it will be an absolute reference) then use 

Apply Names...

on the Name Manager ribbon tab to replace the direct reference by the Name.


Not that I have much experience of doing this, since I ban direct references from my workbooks in the first place.


best response confirmed by SamFares (Contributor)


Sam, you shall select entire formula to do that.

In cell:

F2 (or double click)->Ctrl+End->Ctrl+Shift+Home->F4


In formula bar



Thank you PReagan!
Thank you Peter!
I appreciate it and thank you Sergei!


Sam, you are welcome

@Sergei Baklan 


Hi Sergie:


When I do Ctrl+A in the formula bar it doesn't select the entire formula. It doesn't do anything. Am i doing something wrong?





For me Ctrl+A works both in the formula bar and the cell.


I realise that my proposed solution was overkill in that I set out to change multiple relative references to a given cell without changing other relative references that may exist within the same formula.


Sam, perhaps you are not on latest Excel, couple of years ago Ctrl+A didn't work, see for example our discussion here Not sure when it was introduced.

Anyway, select in formula bar as well by Ctrl+End->Ctrl+Shift+Home or like.


@Peter Bartholomew , you are right, if Ctrl+A works it works in both.

@Sergei Baklan 


Yes you are correct. I have Excel 2016 which doesn't do it. 




I think these answers are over complicating things.  I would do a search G77 and replace $G$77 @SamFares