imagine you have a spreadsheets with 500
plus rows of data it's hard if not
impossible to see patterns and trends in
the data even with similar spreadsheets
it can be difficult to spot patterns
with row after row of data an Excel
feature called conditional formatting
can help with this with conditional
formatting you can apply formatting to
one or more cells based on the value of
the cell you can highlight interesting
or unusual cell values and visualize the
data using formatting such as data bars
let's take a look at how we can use
conditional formatting in our company
cells spreadsheet first select an area
that you'd like to highlight our order
amounts are really what we would
probably want to apply conditional
formatting to on the Home tab locate the
Styles group click the conditional
formatting commands and a menu will
appear with your formatting options
let's take a look at how a few of these
work the first one highlight cell rules
such as greater than and less than
let's choose less than and apply
formatting to cells with values less
than $200 after entering the value we
can use the drop down menu to choose the
color and type with formatting for Excel
to apply and then click OK
now in the spreadsheet the cells with
values less than $200 appear highlighted
this allows us to easily identify the
low orders to remove these formatting
rules we'll just click the conditional
formatting command and select clear
rules since my cells aren't selected
right now I'll just choose clear rules
from entire worksheet and there you go
the formatting is removed
take a look at some of the other
conditional formatting options besides
locating cells that are greater than or
less than a value you can also apply
formatting to cells that are between two
values equal to a value or if we had
selected the entire worksheets we could
apply formatting to cells with a
specific date or month listed you can
apply conditional formatting to cells
that are the top or bottom 10% in value
or above and below average
let's select above-average select the
color of formatting we want click OK
and now those values that are above
average are highlighted so we can easily
see now let's clear the formatting on
these selected cells let me show you one
other option that's really one of my
favorites we'll select our order our
mouse again and take a look at a
conditional formatting option called
data bars we'll select a color and now
these values are formatted with the data
bars this allows us to easily see which
values are greatest cells with smaller
values appear with less of the data bar
while cells with greater values appear
with more of the data bar the real
powerful parts of conditional formatting
is the ability to apply multiple rules
or formatting to cells right now we have
data bars apply to the cells that show
us which cells have the largest values
now what if I also want to mark the ones
that are below average or equal to a
certain amount now you can see that we
have our formatting for the data bars
and for the below average sales before
we finish talking about conditional
formatting let me show you how you can
have more control over these formatting
rules if you click the conditional
formatting commands you'll see manage
rules at the bottom
the menu select this and the dialog box
will appear from here you can change how
your selective formatting rules are
applied you can select a rule and move
it up or down which will make it so
Excel performs this formatting rule in a
different order you can edit a rule or
even delete a rule we'll just click OK
and now you can see that the formatting
to the below-average cells has been
deleted
while this spreadsheets only has 40 rows
imagine you are working with a
spreadsheet with hundreds of rows data
bars and the other conditional
formatting options make it very easy to
see data in meaningful ways