Your
boss wants a spreadsheet detailing the total sales by quarter for
each sales region from the previous year and would like all regions
below $100,000 in sales to be highlighted, as well as the regions
above $300,000 in sales. She explains that she would like to
see at a glance those regions that are below standard and also those
regions that greatly exceed company expectations in each quarter.
Your boss hands you the information and expects you to figure out
how best to display it.
After
inputting the data and playing a bit with formatting, you have come
up with the following spreadsheet:
Applying
Conditional Formatting
Rather
than examining each figure yourself and applying formatting manually
to each one, conditional formatting offers the ability to format one
time and apply it to any cell that meets the conditions you set.
For example, we could have Excel look for cells below $100,000 and
apply a red font color in bold typeface. We could also have
Excel look for those cells above $300,000 and apply a blue font
color in bold typeface. In this way, the figures your boss
wants to stand out can be seen at a glance. Let's take a look
at the steps:
First, select the cells in which you would like
Excel to apply the formatting. In this case, we want only
those cells with figures relating to individual quarters.
From the Format menu, choose Conditional
Formatting. The Conditional Formatting dialog
box appears as shown below.
The first condition to set, is formatting the
cells below $100,000. In the second field, choose less
than. The dialog box changes to reflect your
selection.
Type in 100000 in the
last field to complete the statement as read across the dialog
box. Cell Value is less than 100000. There
is no need to include symbols such as commas and dollar signs.
Choose the Format button. The Format
cells dialog box opens and allows you to select formatting
for this condition. Note that in addition to choosing a
typeface and color, you can select the Border and/or Patterns
tab to border cells and shade cell backgrounds. In our
case, we chose bold typeface and red font color. Choose OK.
Because we have another condition to add for
cells above $300,000, choose the Add >> button on
the Conditional Formatting dialog box. The second
condition is set up in the same manner as above and is shown
in the graphic below. You can add up to 3 conditions.
Once all the conditions and formats are in
place, choose OK. The results are displayed on
the spreadsheet.
While we used a short, simple example above for
display purposes, imagine the amount of formatting time this would
save on a large spreadsheet of data. Using conditional
formatting also eliminates the possibility of human error - there
is no need to wonder if you missed formatting a cell or to
quadruple check your work!
Notice: You have the author's
permission to use the tutorials on this site for your personal
use only. If you would like to use these tutorials for
training classes or other commercial use, please contact kstille@esmartweb.com.
Distribution in print or on the Web without the author's permission
is strictly prohibited.
Featured Sites
HandofGrace.com
Quality,
affordable sterling silver beaded bracelets & mothers
bracelets. Swarovski crystals, fresh water pearls, Venetian
glass and other unique findings are featured in our bracelets.
The perfect gift for any occasion!
BakedByMom.com
Offering homemade cookie and candy boxes or gifts at reasonable prices, Baked by Mom strives to provide the best quality product in it's class. Try the White Chocolate Chip & Lime cookies, Rocky Road Candy and Caramels - they get rave reviews!
