Wrong cell when conditional formatting with values

问题:

I regularly encounter an issue on Excel 2010 when I create a new rule using a formula. The issue is reproducible on several of my computers. I select several cells (for instance A4:B143) and then create a new rule using one of the following formulas:

=$A4="issue"
=($A4="issue")

then press Enter. The goal is, of course, to see all the rows of which the first cell's value is "issue". But this does not work and when I go to "manage rules", I see that Excel has the following formula:

=$A1048441="issue"

This is the formula that I want but not with the number that I entered. If I manually replace this new formula that comes out of nowhere (in my eyes) with my old formula, everything works correctly and the formula stays =$A4="issue".

Does anyone know what might cause this issue?

回答1:

Please try selecting from top left to bottom right rather than vice versa.

Without the anchor ($) for rows (has to be left off for the one formula rule to apply throughout the range) the row references are relative - but relative to the active cell rather than to the top row.

The active cell when selecting a range is the one that starts the range selection. So a range that is selected from B143 to A4 has B143 as the active cell (not A4). Relative to that Row4 is 139 rows earlier. For CF, 139 rows earlier than A4 is Row1048441 in Excel 2010 (the rows 'wrap' - the last row, Row1048576, is in effect immediately before Row1, so Row1048441 + 136 gets back to Row1 and plus another 3 from there to get to Row4. 136+3 is the 139 difference).

回答2:

I was able to do apply it in the following manner:

When I entered the formula, I did not put an = sign before. I just put $a6=issue

Rules:

Data: