Imagine you have a spreadsheet with 500 plus rows of data.
It's hard, if not impossible, to see patterns and trends in the data.
Even with simpilar 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 Sales 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 command 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 of 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.
Let's 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
worksheet, we could apply formatting to cells with a specific date or month listed.
You can apply conditional formatting to cells that are the top of 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 is really
one of my favorites. We'll select our Order Amounts 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 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 part of conditional formatting
is the ability to apply multiple rules or formatting to cells.
Right now we have data bars applied 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 cells.
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 command,
you'll see Manage Rules at the bottom of the menu.
Select this, and a dialog box will appear. From here, you can change how your selected
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 spreadsheet only has about 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.