Getting Fancy with Conditional Formatting

Let's start by just looking at June sales again, so 0:00 let's highlight all of June sales. 0:03 And then go to Conditional Formatting and Color Scales. 0:05 And then let's choose the top left one which is Green, Yellow, Red. 0:09 This is the type of heat map that shows us where values fall in a range. 0:15 Greens are highest values in the range and red are lowest. 0:19 And the yellows and oranges are more mid-range values. 0:24 You could also reverse it to show red is highest and 0:28 green is lowest by choosing the second options. 0:30 I'll hover over the rest of the options here, so 0:34 you can see the different color options you have for Color Scales. 0:36 This is a good way to quickly see how each state sales fall in the range for June. 0:41 We can also choose the entire dataset and look at it for all months. 0:46 To do that we would just need to clear the rule. 0:50 And then select all the values. 0:58 And then go back to Conditional Formatting, Color Scales, 1:05 and let's choose top left option again. 1:09 Now this gives us a good visual of the monthly sales by state, and 1:12 how each value falls in the overall range. 1:17 You may notice quite a bit of red here, so it looks like total sales for 1:19 June are probably down. 1:23 Let's add a total down here and see if that's true. 1:25 Key in Total here, and then click on the AutoSum function. 1:29 And make sure it's including the correct data. 1:35 And it looks like it is, so now lets hit Enter, and copy this over through June. 1:44 There, you can see our assumptions were correct. 1:50 June sales of 716,068 are the lowest of the year so far. 1:53 Now let's clear that rule and say we just want to look at how Georgia 2:00 sales are trending for the first half of the year. 2:03 Highlight the row with Georgia sales and then go back to Conditional Formatting, 2:07 Color Scales, and then the top left option again. 2:12 It's my favorite if you can't tell, just makes sense to me when looking at 2:15 sales data to show highest numbers as green and lowest as red. 2:19 Now we can see that February was Georgia's best month and June is the worst. 2:23 All right, let's clear that rule, now let's look at icon sets. 2:28 I use icon sets a lot when looking at variances or comparing data. 2:33 For instance, changes in month over month sales. 2:37 Let's add a formula here at the bottom of our data to show monthly change in total 2:40 sales. 2:44 Let's type Monthly Change here, and then since we don't have the data for 2:45 December in here. 2:48 The first monthly change we can calculate is January to February. 2:50 Let's add a formula here starting in February by typing = and 2:55 then selecting February total minus January total. 3:00 And then let's copy this across. 3:04 Now you can see sales were up from January to February, but 3:10 down each month after that. 3:14 Let's try adding an icon to help us visualize by selecting the row that shows 3:16 month over month change in sales. 3:20 And then go up to Conditional Formatting, Icon Set, and 3:22 then you can see all of the different display options here. 3:26 Let's choose the arrows with the colors, 3:30 now you can easily see sales are up from January to February. 3:32 And down for the remaining months, but 3:36 you may notice February to March is showing as yellow instead of red. 3:38 You would think this would be red since the negative, so let's figure out why 3:42 it's showing yellow by going up to Conditional Formatting in Manage Rules, 3:47 and then go to Edit Rules. 3:52 And you'll see the rules here, 3:55 which we are saying if the value falls in the top 33% of the range then it's green. 3:57 If it's bottom 33% then it's red, and if it's in between those it's yellow. 4:03 You can change these to whatever percentages you can prefer, or 4:07 even change it to numbers instead of percentages. 4:11 And put zeros for both if you never want to see yellow. 4:15 There's several other variations for rules that you can get by selecting New Rule or 4:18 Manage Rule to adjust any other rules you have selected. 4:24 But there's not enough time to go through each variation, so 4:27 I'll leave that up to you to explore on your own. 4:30