Ms Office Tune Up

If you store a large amount of data in a worksheet, all the numbers tend to look the same after a while, which is a problem if part of your data analysis involves looking for values that are exceptional in some way. For example, suppose you have a worksheet that lists the 2006 and 2007 sales by sales reps and also calculates the percentage increase or decrease, year over year. As part of your analysis, you might be interested to know which reps sold less in 2007 than in 2006. You can just try eyeballing the negative values, or you can sort the list so that the negative values display at the top.

Excel 2007 gives you another way to analyze thins kind of data: You can highlight with spe­cial formatting those cells that have values above or below another value that you specify. Here’s how it works:

1. Select the range that has the values with which you want to work.
2. Choose Home, Conditional Formatting, Highlight Cells Rules. Excel displays a menu with the following choices:
   Greater Than Choose this command to apply formatting to cells with values greater than the value you specify.
   Less Than Choose this command to apply formatting to cells with values less than the value you specify.
   Between Choose this command to apply formatting to cells with values between the two values you specify.
   Equal To Choose this command to apply formatting to cells with values equal to the value you specify.
   Text that Contains Choose this command to apply formatting to cells with text values that contain the text value you specify (which is not case-sensitive).
   A Date Occurring Choose this command to apply formatting to cells with date values that satisfy the condition you choose: Yesterday, Today, Tomorrow, In the Last 7 Days, Next Week, and so on.
   Duplicate Values Choose this command to apply formatting to cells with val­ues that appear more than once in the range.
3. In most cases, you see a dialog box in which you enter your criteria. For example, if you choose Less Than, you see the Less Than dialog box, as shown in Figure1. Type the value (or cell address that contains the value) you want to use as a benchmark and choose a format.
4. Click OK. Excel formats the cells that meet your criteria (see Figure 2).

Figure 1 If you choose the Less Than command, use the Less Than dialog box to specify the criteria and formatting you want to apply to the range.

Figure 2 A range with the nega­tive values highlighted in column D.

Technorati Tags: highlight cell, excel 2007

Popularity: 2% [?]