Autofill conditional formatting cell references

Question

Excel conditional formatting fill down is working but not recording the correct formula once filled down.

eg. Cells in Column B (eg B6) have a value (active, exit, suspend). The adjacent Column C cell needs to be colored green if the adjacent value in column B shows the entry active. This works fine when using in C6 Use a formula to determine which cells to format, and Format values where this formula is true shows =B6="active" and cell C6 comes up filled with the green color.

Upon fill down from cell C6 the formatting is all displayed correctly for each additional instance of the word active in a column B cell. However when the conditional formatting rule is viewed for a cell such as C7, C8 onwards instead of showing =B7"active" or =B8="active" etc they still all say =B6="active".

Does anyone know why they do not refer to their relative adjacent cell (B7, B8, B9 etc) instead of the original B6? The formula originally was =$B$6="active" and has been edited to remove the absolute reference and replaced with =B6="active" to allow a relative fill down.

Answer 1

Does anyone know why they do not refer to their relative adjacent cell ( B7, B8, B9 etc ) instead of the original B6 ?

Presumably someone in Microsoft does and that is not the answer you seek.

However you acknowledge the formatting is working so it hardly seems to matter.

I can suggest that it is efficient. Conditional Formatting with a formula has three key parts:

• Formula
• Formatting
• Range

Formula May be complex and lengthy.

Formatting It is what it has to be. Usually a very simple fill colour but sometimes bold, font, font colour etc.

Range The 'neglected' one of the three but equally important though simple - just co-ordinates defining one or more rectangles (or a singe cell reference otherwise the top left and bottom right references). An efficient way to define an area that works whether one cell or one million, still just two corners at most.

You have probably noticed that whatever you enter there that is valid, anchors ($ signs) will be added where not already provided. You may also have noticed that Applies to will accept a named range - but Excel then automatically converts this to the cell reference/s.

It might help to consider what you would do if you were in charge of having doors painted yellow at house numbers 1 to 10 on a housing estate (yellow highlighting probably the most common format chosen for Conditional Formatting and a cell being roughly the shape of the surface of one side of a door). Say during the process you were required to extend that to house numbers 11 to 15 also. Would you then produce five additional copies of the painting specification (the formula), it might be several pages long, or would you merely change your instructions to the team doing the work from "do this for houses 1 to 10" to "do this for houses 1 to 15"?

As you copy down the Applies to range adjusts accordingly, the system works. But for each rule there is only one range and that is the case in reverse, for each range thee is only one rule, So no need to express that rule cell by cell, hence perhaps a million times over.