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?