Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In any version of Excel (since 2000, at least), you can enter = into a cell to indicate that a formula is about to be entered and then click cell A1 so that the formula says =A1. This is a relative address and is the behavior I would expect.

However, there are those special "range selection" windows that behave differently. For example, go to create a new conditional format that uses a formula. While the formula window is open, clicking on cell A1 results in =$A$1 being entered into the formula box. This is very annoying because it prevents you from doign a select/drag or copy/paste if you want to repeat that conditional format elsewhere.

It's not just the conditional format feature that does this, but other Excel features that implement this behavior.

I would like Excel to treat point-and-click formula designers to use relative addresses rather than absolute addresses. Does Excel have such a feature?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

I have not been able to find a way to turn this feature on/off. A way to deal with it is to do the following;

After you select your range, press the F4 to change the reference. Press it again to change to the next type of reference and so forth. In the example you gave for conditional formatting, pressing it 3 times gets you to relative reference.

share|improve this answer
    
I just confirmed that this works, although you do need to hit F4 every time you click a cell rather than choosing your reference format once so that all consecutive clicks will use that same format. Thanks. –  oscilatingcretin Sep 28 '12 at 12:42

I've just checked this out. I'd never actually realised it. This behaviour also occurs when set to R1C1 mode as well. It doesn't happen when editing a formula from the formula bar (e.g. when entering a formula in a cell).

I do not believe that there is any way of controlling this.

I suggest though, asking in one of the Microsoft recommended support forums:

These 2 require logins, I think you have to be a business partner or Technet/MSDN subscriber

Alternatively, if you get paid support from Microsoft you can try asking them directly: http://support.microsoft.com/ph/918

share|improve this answer
    
Please don't mark down without leaving a comment! –  Julian Knight Jun 22 '12 at 8:41
    
I didn't mark you down, but my guess is it was because you didn't really answer the question. Although your information may be helpful, it probably should have been posted as a comment rather than an answer. –  CharlieRB Jun 22 '12 at 13:25
    
Cheers for the info @CharlieRB, though I think that it would have been pretty impossible to add that info as a comment! –  Julian Knight Jun 22 '12 at 16:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.