Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 31 down vote accepted

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.
share|improve this answer
    
+1 - beat me to it :) – warren Sep 1 '09 at 6:52
    
Spot on, cheers. – Lunatik Sep 1 '09 at 7:34
    
Also answered in: superuser.com/questions/18945/useful-excel-keyboard-shortcuts – pelms Sep 1 '09 at 9:51
    
@pelms - the link you provided does not exist anymore. Any idea where was the question moved to? – ysap Apr 15 '12 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 Apr 16 '12 at 0:31

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
share|improve this answer
    
This macro just made my day! – Jamiho Jan 21 at 22:23

You must log in to answer this question.

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