如何在Excel中像拖动公式一样批量应用条件格式？实现日期高亮

Got it, let's sort this out so you don't waste time creating a conditional formatting rule for every row. The key here is using relative cell references in your formula, which lets the rule automatically adapt to each row—just like how formulas adjust when you drag them.

Step 1: Set Up the Rule for the Entire Range (No Dragging Needed) #

Let's assume your Start Dates are in column A and End Dates are in column B (adjust the column letters to match your sheet):

1. Select all your End Date cells (e.g., B2 through B100, or drag to select all rows with data).
2. Go to the Conditional Formatting menu → Manage Rules → New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter this formula (make sure there are no dollar signs $—that's the relative reference magic):

   =B2<A2
   

   This formula checks if the End Date in the current row (B2) is earlier than the Start Date in the same row (A2). When applied to B3, it'll automatically switch to =B3<A3, and so on for every row in your selected range.
5. Click "Format..." and set the fill color to red, then confirm all windows.

Step 2: Drag to Copy the Rule (Like You Do With Formulas) #

If you already set up the rule for one row and want to extend it quickly:

1. Select the cell that has the working conditional formatting (e.g., B2).
2. Hover your mouse over the fill handle (the tiny square at the bottom-right corner of the cell) until it turns into a black cross.
3. Click and drag down to the last row you need to apply the rule to. The conditional formatting will copy over, and the formula will adjust automatically for each row—exactly like dragging a regular formula.

Quick Tips to Avoid Issues #

• Always double-check the "Applies to" field in the Rule Manager to make sure it includes all your target cells. If you need to add more rows later, just edit this range.
• If your Start/End Date columns are in different positions (e.g., Start in D, End in F), adjust the formula to =F2<D2—still no dollar signs!
• Make sure your dates are formatted as actual date values (not text), otherwise the comparison won't work correctly.

内容的提问来源于stack exchange，提问作者Pherdindy