- HD
- 720p
- 540p
- 360p
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
What if the conditional formatting menu options do not cater to what I want? Let's look at how to customize conditional formatting by way of creating a dynamic To-Do list.
Exercise files
Download this lesson’s exercise file.
Conditional formatting: manage rules and use formulae in rules16.1 KB
Quick reference
Conditional formatting
Conditional formatting in this example is used to format dates using the dynamic TODAY function.
The date formatting indicates overdue and pending items.
When to use
Conditional formatting is very useful to visually accentuate data and will change dynamically with your data.
Create rule 1
- Selection of cells
- Select the entire row even if you only test one of the column values to highlight the whole record
- Create conditional formatting rules using the create rule menu option
- Add formulas to test dates
- Lock the formulas to test the whole column
- Format the desired result by changing the fill and text color of the cell
Create rule 2
- Follow the same procedure
- After creating the rule, the cells don’t behave as expected – note the reason
- Manage the rules, click on stop after rule 1
Create rule 3
- Follow the same procedure
- Note the cells don’t behave like expected
- Manage the rule, change the order of the rules
- 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.