WEBVTT Kind: captions Language: en 00:00:08.410 --> 00:00:13.640 You may already have some experience using charts to visualize data in Excel. 00:00:13.640 --> 00:00:18.220 Conditional formatting offers another way to visualize your data using options 00:00:18.220 --> 00:00:21.730 like color coding based on the cell's value. 00:00:21.730 --> 00:00:26.539 In this example, I have the sales figures for a group of different salespeople and 00:00:26.539 --> 00:00:31.079 I'd like to find out who's meeting or exceeding their monthly sales goal. 00:00:31.079 --> 00:00:34.820 Of course we could just look at the numbers, but it might be easier they were 00:00:34.820 --> 00:00:39.629 highlighted somehow and the good news is that we can actually do just that with a 00:00:39.629 --> 00:00:41.530 conditional formatting rule. 00:00:41.530 --> 00:00:44.240 First, select your data 00:00:44.240 --> 00:00:47.690 then go to Conditional Formatting on the Home tab. 00:00:47.690 --> 00:00:54.650 Next, mouse over Highlight Cells Rules and, in this case, we're going to use Greater Than. 00:00:54.740 --> 00:00:59.740 Now the sales goal for each person on this list is $4,000 per month. 00:00:59.740 --> 00:01:04.180 If we type that number here, you can see in the background that any cell with a 00:01:04.180 --> 00:01:08.400 value greater than 4,000 is being formatted automatically. 00:01:08.400 --> 00:01:13.220 If you want you can even choose a different formatting option 00:01:13.220 --> 00:01:16.500 just click OK when you're done. 00:01:16.500 --> 00:01:23.980 As you can see, the formatted cells really stand out now so we can easily tell who's met or exceeded their goal. 00:01:23.990 --> 00:01:28.570 It looks like August wasn't a very good month, for example, but September and 00:01:28.570 --> 00:01:32.100 October where much, much better. 00:01:32.100 --> 00:01:36.610 Next, let's take a look at some of the conditional formatting presets. 00:01:36.610 --> 00:01:42.340 We're just going to re-select our data and then go back to the Conditional Formatting command. 00:01:42.340 --> 00:01:45.690 You have three different types of presets to choose from: 00:01:45.690 --> 00:01:51.810 data bars, color scales, and icon sets. To learn more about each one mouse over 00:01:51.810 --> 00:01:53.810 these options on the menu. 00:01:53.810 --> 00:02:00.770 Data bars are kind of like bar graphs; the higher the value the longer the bar in the cell. 00:02:00.770 --> 00:02:06.690 Color scales, on the other change, will change the background color based on the cell's value. 00:02:06.760 --> 00:02:10.920 Icon sets can be used to add different symbols, ratings, and indicators, 00:02:10.930 --> 00:02:16.250 and you have quite a few styles to choose from depending on what you need 00:02:16.250 --> 00:02:20.940 I think this one's a special easy to read. 00:02:20.940 --> 00:02:27.540 These icons help us see which items are the highest and also the lowest when compared to one another. 00:02:27.550 --> 00:02:30.230 We can also see which ones are about average. 00:02:30.230 --> 00:02:33.860 Now you might have noticed that the first rule we applied is still visible. 00:02:33.860 --> 00:02:38.440 That's the one that's changing the fill color for cells above 4,000. 00:02:38.440 --> 00:02:44.180 That's because Excel lets you have multiple conditional formatting rules at the same time, 00:02:44.190 --> 00:02:46.310 so you can highlight different things. 00:02:46.310 --> 00:02:50.680 To clear the current formatting rules, just select your cells, 00:02:50.680 --> 00:02:55.810 go to Conditional Formatting and mouse over the option that says 00:02:55.810 --> 00:03:02.390 Clear Rules. You can clear the rules for just the selected cells or for the entire spreadsheet. 00:03:02.510 --> 00:03:07.250 In this case, though, I'd like to remove the greater than 4,000 rule while 00:03:07.250 --> 00:03:09.640 keeping the rest of the icons in place. 00:03:09.640 --> 00:03:14.150 We can do that by going to Manage Rules instead. 00:03:14.150 --> 00:03:19.120 Here, you'll find a list of all the rules that are currently applied here cells. 00:03:19.120 --> 00:03:22.310 To remove one, just select the rule you want 00:03:22.310 --> 00:03:27.840 then click Delete Rule, and click OK! 00:03:27.840 --> 00:03:33.520 Conditional Formatting is a great tool for visualizing your worksheet, so practice using some of these 00:03:33.530 --> 00:03:38.990 rules on your own and try combining them to see what they can tell you about your data.