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