Conditional Formatting: Manage Rules and Use Formulas in Rules

Quick reference

• 00:04 In this lesson, I'd like to show you how Conditional Formatting could be used
• 00:09 to create a dynamic to-do list.
• 00:11 I'd love it if this list showed me deadlines I've already completed,
• 00:16 deadlines I've missed, and warnings for upcoming work that I need to do.
• 00:22 Now the best way to do this is to have your deadlines listed with today's date.
• 00:26 Now if this was a real life example,
• 00:28 we'd have today's actual date in that cell where it says today's date.
• 00:32 For the purpose of this exercise, I've hard coded a date in there.
• 00:36 But when you use the list, replace that date with the today function, and
• 00:40 it will update every time you open the spreadsheet.
• 00:43 To create our to-do list, I can use Conditional Formatting.
• 00:47 Now let's say, for example,
• 00:49 I want this list to show me all the deadlines I've missed in red.
• 00:53 We'll start by highlighting the entire range.
• 00:55 Meaning, all the columns for each data line or
• 00:58 record will be highlighted if the cell meets the conditional formatting criteria.
• 01:04 Highlight the range, go up to Home, Conditional Formatting,
• 01:09 and click on New Rule.
• 01:13 And I want to use a formula.
• 01:14 The formula is going to say if the current date is bigger than the deadline date.
• 01:19 In other words, the deadline comes before today's date, so it has come and gone.
• 01:25 Okay, if I leave the formula like this, it's only going to look at D8 and
• 01:31 D4, and will not test the rest of the data.
• 01:35 D4 is okay, but that D8 reference needs to change.
• 01:39 I need to release the row so
• 01:41 it can refer to all the rows in the column as well as D8 itself.
• 01:46 So let's remove that dollar sign before the 8.
• 01:50 Now let's select our formatting.
• 01:51 How should they appear?
• 01:53 I'd like a nice red font.
• 01:57 And then if we go up to Fill, we can have a nice light red fill.
• 02:03 That's great.
• 02:03 We hit OK, and OK to complete the new rule.
• 02:07 And now the first task on this list is not a problem because I've already done it.
• 02:13 That's indicated by the x over on the right.
• 02:15 The second job is correctly highlighted as officially late.
• 02:19 So we need to get rid of the jobs that were completed, we need to add a new rule.
• 02:25 So select the same range, go to Conditional Formatting, New Rule,
• 02:29 and let's use another formula.
• 02:31 This formula is going to say, if I marked a task as done in column E,
• 02:36 highlight the record in a different way.
• 02:39 I'm going to click on this value E8, and
• 02:42 then I'll click F4 to release the row again.
• 02:46 And I can toggle the lock reference options with F4, or
• 02:51 if you're on a Mac Cmd+T.
• 02:53 Toggle through the lock reference values until only the column is fixed with
• 02:57 the dollar sign.
• 03:00 So if E8 does not equal blank, I can indicate that
• 03:04 I've done the job with x or really any character.
• 03:09 So not equal to blank, and in Excel you type the less than and
• 03:14 greater than sign together, unequal to blank.
• 03:18 So that's going to be two quotation marks with nothing in between.
• 03:22 If it's not blank, then what?
• 03:25 Okay, go to font.
• 03:26 Let's put a strikethrough, through the text.
• 03:30 And let's make it a lighter gray, so it's a little non-threatening.
• 03:35 In fact, let's make it even lighter than that.
• 03:39 So hit OK there, and okay to add the new rule.
• 03:43 And that looks good except now for that first record the background is red.
• 03:47 And that's not what I wanted.
• 03:49 I didn't set a background in the new rule I created.
• 03:52 So let's review this.
• 03:54 Let's go back to Conditional Formatting and Manage Rules.
• 03:59 Okay, I see the strikethrough rule doesn't show a light red background fill but
• 04:04 the cell on the sheet does.
• 04:06 What happened is Excel applied the first rule and
• 04:09 the first rule didn't dictate any fill, so it applied the second rule.
• 04:14 And where the first rule would overrule the second rule in terms of font color,
• 04:19 the second rule replaces the fill color,
• 04:21 because the first rule didn't state anything for a fill.
• 04:25 So we need to get that to stop from happening.
• 04:28 And to do that, I can just click Stop if true.
• 04:33 So if the first rule applies to a line, then Excel won't continue applying
• 04:37 the second rule or anything after that, and we won't get that fill background.
• 04:42 So let's click Apply and it solves our problem.
• 04:47 Okay, great, two rules are done.
• 04:51 Now I want the list to highlight the deadlines that are fast approaching.
• 04:55 So I'm going to highlight my data again.
• 04:59 Go up to Conditional Formatting, New Rule.
• 05:02 Now I want to use a formula again, I want to show a warning.
• 05:05 If there's a pending deadline, we'll reference the start date.
• 05:09 But again, I have to release the row.
• 05:11 So click on F4 or Cmd+T on the Mac until your column is fixed.
• 05:17 Okay, the start date must be smaller than today's date, plus a few days.
• 05:24 Now I want a warning to say that it's in the next few days.
• 05:28 And I want to be able to dynamically change that.
• 05:31 So if the start date is in that range, if the start date is smaller than today,
• 05:37 plus 2, then it's going to happen in the next few days.
• 05:42 Well, then what?
• 05:43 Let's format this Let's make it a happy green to warn me,
• 05:49 and a light fill in the background, that'll be perfect.
• 05:54 We hit OK, and OK again to add the rule.
• 05:57 Uh-oh, now it's overwritten everything, that doesn't work.
• 06:01 Let's figure out what's going on.
• 06:02 Go back to Conditional Formatting, Manage Rules.
• 06:06 Let's look at the order of the rules.
• 06:08 That is the problem here.
• 06:10 You see the order in which these rules are evaluated makes a difference, so
• 06:13 we need to list them in the correct order.
• 06:16 Select the first rule and move it down with that little arrow button, so
• 06:20 that it's last.
• 06:21 Click on Apply, and now you see everything is working as expected.
• 06:27 If I click on my days, the due days and change it to 3 days,
• 06:31 it'll highlight all the jobs due within 3 days.
• 06:35 So there you go.
• 06:36 That's how you can use Conditional Formatting to create a dynamic to-do list.

Lesson notes are only available for subscribers.