Microsoft Excel :  Conditional Formatting

by Karyn Stille

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:

  1. 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.

 

  1. From the Format menu, choose Conditional Formatting.  The Conditional Formatting dialog box appears as shown below.

  1. 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.

  2. 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.

  1. 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.

  1. 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.

  1. 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! 

 

Free Microsoft Office Tutorials & MS Office Tips Newsletter
http://tutorials.esmartweb.com