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
Introducing Conditional Formatting
4:30 with Tyler TallonConditional formatting is a great tool that can help you visualize data and make trends and variances more obvious. Once you become familiar with it, it’s an easy tool to use and can help you analyze data efficiently.
Downloads
Please note that this is a new spreadsheet, different from the one you used in the previous stage:
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
[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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up