Absolute reference all cell references in a Microsoft Excel formula?

3

1

For a Microsoft Formula like this:

=IF(Data!H5=3,0,IF(Data!H5<=Data!H19,3,IF(Data!H5<=Data!H20,2,IF(Data!H5<=Data!H21,1,0))))

Is it possible to mass convert all the relative cell references to absolute references?

The desired formula would be:

=IF(Data!$H$5=3,0,IF(Data!$H$5<=Data!$H$19,3,IF(Data!$H$5<=Data!$H$20,2,IF(Data!$H$5<=Data!$H$21,1,0))))

John M

Posted 2011-02-23T16:39:25.593

Reputation: 217

Answers

4

Press F4 repeatedly while editing the formula to cycle among reference styles. You may need to select the whole formula to affect all the references in it.

In Excel:Mac 2011 (and possibly other versions for Mac), Press CommandT instead.

Paused until further notice.

Posted 2011-02-23T16:39:25.593

Reputation: 86 075

Doesn't work on Mac – StatsSorceress – 2017-08-26T22:29:53.207

@StatsSorceress: Added Mac keystroke to my answer – Paused until further notice. – 2017-08-29T16:05:29.270

Thanks. Selecting the whole formula is required before cycling through the F4 options. – John M – 2011-02-23T17:26:20.603

Asked: 2011-02-23T16:39:25.593

Viewed: 1 272 times

Active: 2017-08-29T16:05:04.387