29

If you build a formula by clicking on a cell to insert its address into the formula, is there a way of making this reference absolute rather than relative?

I've been using Excel for many years but never come across a way of doing this. I've tried the obvious SHIFT+ click, CTRL+click and ALT+click.

2 Answers 2

46

Click the cell and then hit F4. As you keep hitting F4, it will cycle through various level of absolute.

Example:

  • Click the cell A1, type '=', then click the cell B1.
  • Hit F4. The reference to B1 becomes $B$1.
  • Hit F4 again. The reference becomes B$1.
  • Hit F4 again. The reference becomes $B1.
  • Hit F4 again. The reference returns to B1.
3
  • Also answered in: superuser.com/questions/18945/useful-excel-keyboard-shortcuts
    – pelms
    Commented Sep 1, 2009 at 9:51
  • @pelms - the link you provided does not exist anymore. Any idea where was the question moved to?
    – ysap
    Commented Apr 15, 2012 at 23:08
  • @ysap It probably wasn't kept, IIRC the link was a bit of a laundry list which I think the network now tries to discourage.
    – Margaret
    Commented Apr 16, 2012 at 0:31
5

I've just been through a similar issue where I needed to do this for a large number of cells ad-hoc

And this code saved my sanity :)

Sub selectedToAbsolute()
 Dim c As Variant
    For Each c In Selection
        c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
    Next c
End Sub

Sub selectedToRelative()
 Dim c As Variant
    For Each c In Selection
        c.Value = Application.ConvertFormula(c.Formula, xlA1, , xlRelative, c)
    Next c
End Sub
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .