[MUSIC]

Conditional formatting is a tool I use all the time.

It helps me visualize data and makes trends, and variances more obvious.

Once you become familiar with it, you'll see it's an easy tool to use, and

can help you analyze data efficiently.

Let's run through a few examples on how to apply conditional

formatting to our spreadsheet that shows store sales by state and

month for the first half of the year.

As a reminder, you can find a link to the spreadsheet in the project files.

You can find Conditional Formatting under the Home tab.

Once you click on it, you will see several options.

Highlight Cell Rules, Top/Bottom Rules, Data Bars, Color Scales and Icons.

Let's say we wanted to quickly highlight all the states that had

sales greater than $20,000 in June.

To do that you would fast highlight all the June sales in column G.

And you just wanna select values here, not the header.

Then, go to Conditional Formatting.

Highlight Cell Rules.

Choose Greater Than, and type in 20,000.

You can also choose how you want the values greater than 20,000 to display.

Let's choose Green Fill with Dark Green Text.

Now you can easily see which states had sales higher than $20,000 in June.

Okay, let's clear that rule by going back to conditional formatting and

then clear rules.

And you can either clear from selected cells or from entire sheet.

Let's choose entire sheet.

Now let's go back to Highlight Cell Rules and look at all the other options here

like Less Than, Between, Text that Contains, A Date Occurring, and

Duplicate Values, which all work similar to the Greater Than option we just chose.

Let's try Duplicate Values to make sure we haven't accidentally listed any of our

states twice.

First, we need to highlight the column that lists the states.

And then go back up to Conditional Formatting, Highlight Cell Rules,

select Duplicate Values, and let's choose Light Red Fill with Dark Red Text for

the display, and then hit Okay.

[BLANK AUDIO] >> Whoops,

looks like we do have a duplicate.

Michigan somehow got listed twice, this is a problem and will throw off our totals.

So let's go ahead and delete the duplicate here.

>> All right, let's clear the rule again.

Now let's say we wanted to see all the states that had below average

sales in June.

To do this, highlight the column you want to work with.

And then go up to Conditional Formatting.

Top/Bottom Rules, and then select Below Average.

Let's leave the display as Light Red Fill with Dark Red Text.

What it's doing here is calculating the average of the range you selected and

then highlighting any that had sales lower than the average for June.

Next, let's clear that rule and take a look at some of the other options you have

for top/bottom, Which are Top 10 Items,

Top 10%, Bottom 10 Items, Bottom 10%, and Above Average.

All the others here were just like the below average so

no need to look through them all.

Now lets take a look at the next option which is data bars.

For this one, lets select all the sales figures.

And then go up to Conditional Formatting and Data Bars.

You have the option to show different colors in Gradient Fill or Solid Fill.

Let's choose blue gradient fill.

Data bars are similar to bar graph.

The higher the value, the longer the bar.

They give you a quick way to see trends and especially, scale.

You can see California and Texas make up a good chunk of our total sales.

All right, now let's clear that rule and move to my favorite, color scales,

which we'll get into in the next video.