How to Conditionally Format your Worksheet in Excel 2007 Pt. 1
If you want to monitor your data by highlighting certain conditions, Excel’s conditional formatting feature can aid you. For example, if your company offers a bonus whenever sales exceed 150,000 dollars, you can have Excel highlight cells containing sales figures whenever the value is more than 150,000 dollars. You can also have Excel highlight a cell when the entry is less than, between, or equal to a specified value. Use Excel’s conditional formatting feature to monitor text, dates, duplicate values, the top N, the top N percent, the bottom N, the bottom N percent, above average values, or below average values.
Changes affect conditionally-formatted data. If, after a change, a cell no longer meets the condition, Excel
removes the highlighting. If, after a change, a cell meets the condition, Excel adds highlighting. You
determine exactly what the condition is and what should happen if a cell meets the condition. Excel
provides you with a list of formats from which to choose, and you can also create a custom format.
Conditional Format
1. Click and drag to select the data you want to monitor.
2. Click the Home tab.
3. Click Conditional Formatting. A menu appears.
4. Click to select a menu option. A submenu appears.
5. Click to select a menu option. A dialog box appears.
Note: This example uses GreaterThan.
6. Type your criteria.
7. Click the small arrow and select the formatting you want to apply. You can choose to create a custom format.
8. Click OK. Excel highlights all of the data that meets your criteria.
Data Bars
1. Repeat Steps 1 to 3 listed under Conditional Format. This is the selected data. A menu appears.
2. Click Data Bars. A submenu appears.
3. Click the color data bar you want to use. Excel will then apply the data bars to the cells you selected.
Excel provides you with several colors to choose from when you add data bars to your worksheet.
You can select another color. Click the Home tab and then click Conditional Formatting in the Styles group. A menu appears. Click Data Bars and then click More Rules. The New Formatting Rule dialog box appears. Use the Bar Color field to select a new color. Click OK.
You can use color scales to conditionally format data. Color scales use gradations of color. For example, if
you use Excel’s yellow and green color scale, yellow represents lower values and green, higher values. To
apply a color scale, select the cells to which you want to apply the scale, click the Home tab, click
Conditional Formatting, click Color Scales, and then click the color scale you want to apply.
