1

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?

2 Answers 2

5

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.

1
  • 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. Sep 28, 2012 at 12:42
-1

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

3
  • Please don't mark down without leaving a comment! Jun 22, 2012 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, 2012 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! Jun 22, 2012 at 16:49

You must log in to answer this question.

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