Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Intermediate Spreadsheets!
You have completed Intermediate Spreadsheets!
Preview
In this video, we'll introduce you to conditional formatting, a simple tool used to highlight data that meets certain criteria.
Conditional formatting - a spreadsheet feature that changes the appearance of cells based on specific criteria.
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
Welcome back, in this video, we'll
introduce you to conditional formatting,
0:00
a simple tool used to highlight
data that meets certain criteria.
0:04
Let's stay on the Texas
rental data worksheet.
0:10
In the last video, we created
this nice table using VLOOKUP for
0:13
ten counties with specific data
we wanted for those counties,
0:17
but let's dig a little deeper.
0:22
I want to find all three bedroom rentals
less than $900.
0:24
Again, we have over 250 counties, so
0:30
trying to cherry pick
through them will be a chore.
0:32
Instead, let's apply
conditional formatting.
0:36
How does conditional formatting work?
0:39
Conditional formatting changes
the appearance of cells based on specific
0:42
criteria.
0:46
If the condition is true,
0:48
the cells are modified with
the format that you specified.
0:50
If not, they remain unchanged.
0:54
Select column G by clicking
on the column header.
0:57
In the menu,
choose format then conditional formatting.
1:03
Apply to range will show the cells that
will be changed when the condition
1:12
is true.
1:16
We want to highlight cells
that are less than $900.
1:18
Under format rules, Choose less than,
1:22
then enter $900 in the entry box.
1:29
By default, you'll notice that
a few cells are shaded green.
1:38
Bailey 801, Baylor 801,
Borden, 878 and so on.
1:45
The condition for
these cell values is true.
1:52
Notice the unshaded values,
1:55
the condition is false because those
values are not less than $900.
1:58
Click done in the conditional
formatting panel.
2:07
Let's add another rule
to this same column.
2:12
Click add another rule.
2:16
This time format cells if greater than or
equal to and enter 1200.
2:20
Under formatting style,
click on default and
2:30
choose the third option in the top row.
2:33
Then click done.
2:39
Column G now has two
conditional format rules.
2:41
The lower rental rates are in green, and
the more expensive prices are in red.
2:45
But I foresee a problem.
2:52
Someone with red green color blindness may
not differentiate between the two shades.
2:54
Or if I print this worksheet in black and
white, the high prices and
3:00
the low prices will appear
with gray backgrounds.
3:04
So let's fix that.
3:08
Click the value is less than 900 rule.
3:10
These are the rates that
are more attractive to us, so
3:15
we want to bring more attention to them.
3:17
Under formatting style, click the B for
bold, and I for italics.
3:21
You'll notice the font changes for
all values less than 900.
3:29
Click Done.
3:33
Let's make changes to
the higher values too.
3:37
Click value is greater than or
equal to 1200 rule
3:40
These are the values I want to avoid,
so they don't need to be as in my face.
3:46
Under formatting style, click Default.
3:51
And let's choose the option
in the bottom right.
3:55
This removes the cell fill color,
but makes the font color red.
3:59
For good measure,
let's click the strikethrough button.
4:03
It's the S with the line through it.
4:06
Now all the values $1200 or
more are red and crossed out.
4:10
Click Done.
4:16
Try some other combinations on your own,
but be sure your criteria makes sense.
4:20
For example, if you set a condition
on studio apartments under $850,
4:25
they will all be highlighted.
4:29
If you create a condition for
4 bedroom over $2,000,
4:33
that condition is false for the entire
column, so no values are formatted.
4:36
With that in mind, have fun experimenting
with different rules, colors,
4:42
and format options.
4:45
In the next video, we will shift gears and
create a pivot table using sales data.
4:48
Can't wait.
4:53
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