Tuesday, 17 October 2017

Conditionally format rows in Excel depending on date range

Sometimes I get asked Excel questions. This is one of those times!

Given a spreadsheet with rows that contain a start and finish date, format the rows in the past, present and future in different colours, so that it looks like this:

Spreadsheet showing rows with different colours

I initially tried using a formula with Excel’s Conditional Formatting feature. Despite this being the recommended solution in some search results, I just found it set the one format for all the rows – not what I wanted. This post by Joseph D’Emanuele put me on the right track.

Here’s what I ended up doing:

  1. Add a new column next to your existing data
    Spreadsheet with extra Status column added
  2. In the first cell of this new column, insert the formula
    =IF(B2 < TODAY(), "Past", IF(A2 > TODAY(), "Future", "Current"))
  3. Copy this formula down to the remaining rows. eg.
    Spreadsheet with Status column populated
  4. Now select all the rows you want to format (for me that’s A2:D5)
  5. On the Home menu tab, select Conditional Formatting, then Manage Rules
  6. You will add 3 rules – one for each status.
  7. Click New Rule
  8. Select Use a formula to determine which cells to format
  9. In Format values where this formula is true, enter =INDIRECT("D" &ROW()) = "Future"
  10. Click Format and choose the desired formatting to apply for Future dates
  11. Click OK and repeat adding new rules for “Current” and “Past”
  12. You should end up with something like this:
    Excel's Conditional Formatting Rules Manager dialog box
  13. Click OK and you should have rows formatted different colours depending on whether the start/finish date is in the past, current or in the future!
  14. You can optionally choose to hide the Status row if you’d rather not see it all the time.

Note that the formula in the conditional formatting (=INDIRECT("D" &ROW()) = "Future") is hard-coded to the column – “D” in my case. If you move data around, you’ll need to update this to refer to the new column letter.

No comments: