1 00:00:00,750 --> 00:00:04,971 Welcome back, in this video, we'll introduce you to conditional formatting, 2 00:00:04,971 --> 00:00:08,500 a simple tool used to highlight data that meets certain criteria. 3 00:00:10,390 --> 00:00:13,560 Let's stay on the Texas rental data worksheet. 4 00:00:13,560 --> 00:00:17,494 In the last video, we created this nice table using VLOOKUP for 5 00:00:17,494 --> 00:00:22,200 ten counties with specific data we wanted for those counties, 6 00:00:22,200 --> 00:00:23,950 but let's dig a little deeper. 7 00:00:24,970 --> 00:00:30,296 I want to find all three bedroom rentals less than \$900. 8 00:00:30,296 --> 00:00:32,666 Again, we have over 250 counties, so 9 00:00:32,666 --> 00:00:36,330 trying to cherry pick through them will be a chore. 10 00:00:36,330 --> 00:00:38,570 Instead, let's apply conditional formatting. 11 00:00:39,760 --> 00:00:41,390 How does conditional formatting work? 12 00:00:42,810 --> 00:00:46,879 Conditional formatting changes the appearance of cells based on specific 13 00:00:46,879 --> 00:00:47,530 criteria. 14 00:00:48,740 --> 00:00:50,224 If the condition is true, 15 00:00:50,224 --> 00:00:54,460 the cells are modified with the format that you specified. 16 00:00:54,460 --> 00:00:56,080 If not, they remain unchanged. 17 00:00:57,940 --> 00:01:00,506 Select column G by clicking on the column header. 18 00:01:03,580 --> 00:01:06,866 In the menu, choose format then conditional formatting. 19 00:01:12,819 --> 00:01:16,642 Apply to range will show the cells that will be changed when the condition 20 00:01:16,642 --> 00:01:18,140 is true. 21 00:01:18,140 --> 00:01:22,780 We want to highlight cells that are less than \$900. 22 00:01:22,780 --> 00:01:29,027 Under format rules, Choose less than, 23 00:01:29,027 --> 00:01:33,754 then enter \$900 in the entry box. 24 00:01:38,594 --> 00:01:45,183 By default, you'll notice that a few cells are shaded green. 25 00:01:45,183 --> 00:01:52,650 Bailey 801, Baylor 801, Borden, 878 and so on. 26 00:01:52,650 --> 00:01:55,950 The condition for these cell values is true. 27 00:01:55,950 --> 00:01:58,125 Notice the unshaded values, 28 00:01:58,125 --> 00:02:03,440 the condition is false because those values are not less than \$900. 29 00:02:07,830 --> 00:02:10,140 Click done in the conditional formatting panel. 30 00:02:12,990 --> 00:02:16,010 Let's add another rule to this same column. 31 00:02:16,010 --> 00:02:17,480 Click add another rule. 32 00:02:20,580 --> 00:02:25,509 This time format cells if greater than or equal to and enter 1200. 33 00:02:30,440 --> 00:02:33,656 Under formatting style, click on default and 34 00:02:33,656 --> 00:02:36,460 choose the third option in the top row. 35 00:02:39,014 --> 00:02:39,980 Then click done. 36 00:02:41,680 --> 00:02:45,610 Column G now has two conditional format rules. 37 00:02:45,610 --> 00:02:50,400 The lower rental rates are in green, and the more expensive prices are in red. 38 00:02:52,020 --> 00:02:53,920 But I foresee a problem. 39 00:02:54,940 --> 00:02:59,470 Someone with red green color blindness may not differentiate between the two shades. 40 00:03:00,550 --> 00:03:04,610 Or if I print this worksheet in black and white, the high prices and 41 00:03:04,610 --> 00:03:08,680 the low prices will appear with gray backgrounds. 42 00:03:08,680 --> 00:03:09,490 So let's fix that. 43 00:03:10,910 --> 00:03:13,190 Click the value is less than 900 rule. 44 00:03:15,210 --> 00:03:17,380 These are the rates that are more attractive to us, so 45 00:03:17,380 --> 00:03:19,030 we want to bring more attention to them. 46 00:03:21,080 --> 00:03:27,230 Under formatting style, click the B for bold, and I for italics. 47 00:03:29,110 --> 00:03:33,261 You'll notice the font changes for all values less than 900. 48 00:03:33,261 --> 00:03:33,840 Click Done. 49 00:03:37,510 --> 00:03:40,420 Let's make changes to the higher values too. 50 00:03:40,420 --> 00:03:43,984 Click value is greater than or equal to 1200 rule 51 00:03:46,276 --> 00:03:50,620 These are the values I want to avoid, so they don't need to be as in my face. 52 00:03:51,660 --> 00:03:54,550 Under formatting style, click Default. 53 00:03:55,800 --> 00:03:58,150 And let's choose the option in the bottom right. 54 00:03:59,170 --> 00:04:03,540 This removes the cell fill color, but makes the font color red. 55 00:04:03,540 --> 00:04:06,660 For good measure, let's click the strikethrough button. 56 00:04:06,660 --> 00:04:08,300 It's the S with the line through it. 57 00:04:10,090 --> 00:04:15,550 Now all the values \$1200 or more are red and crossed out. 58 00:04:16,590 --> 00:04:17,090 Click Done. 59 00:04:20,010 --> 00:04:24,280 Try some other combinations on your own, but be sure your criteria makes sense. 60 00:04:25,310 --> 00:04:29,976 For example, if you set a condition on studio apartments under \$850, 61 00:04:29,976 --> 00:04:31,910 they will all be highlighted. 62 00:04:33,110 --> 00:04:36,847 If you create a condition for 4 bedroom over \$2,000, 63 00:04:36,847 --> 00:04:42,260 that condition is false for the entire column, so no values are formatted. 64 00:04:42,260 --> 00:04:45,634 With that in mind, have fun experimenting with different rules, colors, 65 00:04:45,634 --> 00:04:46,590 and format options. 66 00:04:48,620 --> 00:04:53,060 In the next video, we will shift gears and create a pivot table using sales data. 67 00:04:53,060 --> 00:04:53,560 Can't wait.