Make all cells references in a long formula absolute

[SamFares]

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)

Thanks,

Sam 

[#2]

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!

[#3]

@SamFares 

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.

[#4 · Accepted solution]

@SamFares 

Sam, you shall select entire formula to do that.

In cell:

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

In formula bar

Ctrl+A->F4

[#5]

Thank you PReagan!

[#6]

Thank you Peter!

[#7]

I appreciate it and thank you Sergei!

[#8]

@SamFares 

Sam, you are welcome

[#9]

@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?

Thanks,

Sam

[#10]

@SamFares 

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.

[#11]

@SamFares 

Sam, perhaps you are not on latest Excel, couple of years ago Ctrl+A didn't work, see for example our discussion here https://techcommunity.microsoft.com/t5/Excel/Editing-text-in-a-cell-Ctrl-A-doesn-t-select-all-ctrl-b.... 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.

[#12]

@Sergei Baklan 

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

Thanks!

Sam

[#13]

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