Excel条件格式：用单一公式为L、O列按左侧单元格值设三色填充

Great question! Let's walk through how to set up this conditional formatting with shared formulas that work for both columns L and O—no need to duplicate rules for each column. Here's the step-by-step breakdown:

Step 1: Select the target columns #

Hold down the Ctrl key and click on the column headers for L and O to select both entire columns at once.

Step 2: Create conditional formatting rules (3 total, one for each color) #

Go to the Home tab → Conditional Formatting → New Rule, then choose "Use a formula to determine which cells to format" for each rule below.

Rule 1: Red fill (left cell is less than your target value)

Paste this formula into the formula box (replace [YOUR_TARGET_VALUE] with your actual threshold—could be a fixed number like 100 or a cell reference like $Z$1):

=OR(AND(COLUMN()=12,K1<[YOUR_TARGET_VALUE]),AND(COLUMN()=15,N1<[YOUR_TARGET_VALUE]))

Then set the fill color to red, and click OK.

Rule 2: Yellow fill (left cell equals your target value)

Use this formula, again updating the target value:

=OR(AND(COLUMN()=12,K1=[YOUR_TARGET_VALUE]),AND(COLUMN()=15,N1=[YOUR_TARGET_VALUE]))

Set the fill color to yellow, click OK.

Rule 3: Green fill (left cell is greater than your target value)

Final formula:

=OR(AND(COLUMN()=12,K1>[YOUR_TARGET_VALUE]),AND(COLUMN()=15,N1>[YOUR_TARGET_VALUE]))

Set fill color to green, click OK.

How this works #

• COLUMN() checks which column the current cell is in (12 = column L, 15 = column O)
• The AND pairs each column with its left neighbor (K for L, N for O) and checks the condition
• OR combines the two column-specific checks so the same rule applies to both L and O

Quick notes #

• If you're using a cell reference for the target value, use absolute references (like $Z$1) so the formula doesn't shift the reference when applied to different rows.
• Make sure the relative references (K1, N1) stay relative—Excel will automatically adjust them to K2, N2, etc., as the rule applies to rows below.

内容的提问来源于stack exchange，提问作者Piotr Zieliński