Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses 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:
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
You need to sign up for Treehouse in order to download course files.
Sign up