[MUSIC] 0:00 Conditional formatting is a tool I use all the time. 0:09 It helps me visualize data and makes trends, and variances more obvious. 0:12 Once you become familiar with it, you'll see it's an easy tool to use, and 0:17 can help you analyze data efficiently. 0:21 Let's run through a few examples on how to apply conditional 0:24 formatting to our spreadsheet that shows store sales by state and 0:26 month for the first half of the year. 0:31 As a reminder, you can find a link to the spreadsheet in the project files. 0:33 You can find Conditional Formatting under the Home tab. 0:37 Once you click on it, you will see several options. 0:40 Highlight Cell Rules, Top/Bottom Rules, Data Bars, Color Scales and Icons. 0:43 Let's say we wanted to quickly highlight all the states that had 0:52 sales greater than $20,000 in June. 0:55 To do that you would fast highlight all the June sales in column G. 0:58 And you just wanna select values here, not the header. 1:02 Then, go to Conditional Formatting. 1:05 Highlight Cell Rules. 1:08 Choose Greater Than, and type in 20,000. 1:09 You can also choose how you want the values greater than 20,000 to display. 1:14 Let's choose Green Fill with Dark Green Text. 1:19 Now you can easily see which states had sales higher than $20,000 in June. 1:27 Okay, let's clear that rule by going back to conditional formatting and 1:32 then clear rules. 1:36 And you can either clear from selected cells or from entire sheet. 1:38 Let's choose entire sheet. 1:42 Now let's go back to Highlight Cell Rules and look at all the other options here 1:45 like Less Than, Between, Text that Contains, A Date Occurring, and 1:49 Duplicate Values, which all work similar to the Greater Than option we just chose. 1:55 Let's try Duplicate Values to make sure we haven't accidentally listed any of our 2:00 states twice. 2:04 First, we need to highlight the column that lists the states. 2:06 And then go back up to Conditional Formatting, Highlight Cell Rules, 2:12 select Duplicate Values, and let's choose Light Red Fill with Dark Red Text for 2:17 the display, and then hit Okay. 2:22 [BLANK AUDIO] >> Whoops, 2:25 looks like we do have a duplicate. 2:27 Michigan somehow got listed twice, this is a problem and will throw off our totals. 2:29 So let's go ahead and delete the duplicate here. 2:34 >> All right, let's clear the rule again. 2:37 Now let's say we wanted to see all the states that had below average 2:46 sales in June. 2:49 To do this, highlight the column you want to work with. 2:51 And then go up to Conditional Formatting. 2:57 Top/Bottom Rules, and then select Below Average. 3:03 Let's leave the display as Light Red Fill with Dark Red Text. 3:07 What it's doing here is calculating the average of the range you selected and 3:12 then highlighting any that had sales lower than the average for June. 3:16 Next, let's clear that rule and take a look at some of the other options you have 3:21 for top/bottom, Which are Top 10 Items, 3:25 Top 10%, Bottom 10 Items, Bottom 10%, and Above Average. 3:32 All the others here were just like the below average so 3:39 no need to look through them all. 3:42 Now lets take a look at the next option which is data bars. 3:44 For this one, lets select all the sales figures. 3:48 And then go up to Conditional Formatting and Data Bars. 3:54 You have the option to show different colors in Gradient Fill or Solid Fill. 3:58 Let's choose blue gradient fill. 4:02 Data bars are similar to bar graph. 4:05 The higher the value, the longer the bar. 4:08 They give you a quick way to see trends and especially, scale. 4:11 You can see California and Texas make up a good chunk of our total sales. 4:15 All right, now let's clear that rule and move to my favorite, color scales, 4:20 which we'll get into in the next video. 4:24