Conditional formatting example by 6QNZXan1

VIEWS: 0 PAGES: 5

									To Create Conditional Formatting…
1. Select the cell(s) that you want to format.




2. Select Format from the Excel main menu and select Conditional Formatting…




2. Develop the criteria by…

a. determining whether you are looking for a certain cell value or a formula;




b. determining the relationship basis;
To Create Conditional Formatting…

c. and setting the specific criteria (in this case, 20% or .2).




3. Click on the Format button. This will provide the following dialog box from which you can set the
specific format for the selected cells.




4. Click OK on the Format Cells dialog box. This will return you to the Conditional Formatting dialog
box as shown below. You can now see how the conditional formatting will look.
To Create Conditional Formatting…

5. Click on the OK button to return you to the worksheet.

Click on the following link to download a working example of conditional formatting.
Conditional Formatting Example
Without Conditional Formatting

Expense Category                           2001            2002            $ Change

Salaries                             $       250,000   $     275,000   $        25,000
Taxes                                $        25,000   $      29,000   $         4,000
Benefits                             $        55,000   $      57,500   $         2,500
Travel                               $        75,000   $      68,000   $        (7,000)
Rent                                 $        16,500   $      20,000   $         3,500
Advertising                          $         7,500   $      10,000   $         2,500

Total Expenses                       $       429,000   $     459,500   $        30,500


With Conditional Formatting on % Change over 20%

Expense Category                           2001            2002            $ Change

Salaries                             $       250,000   $     275,000   $        25,000
Taxes                                $        25,000   $      29,000   $         4,000
Benefits                             $        55,000   $      57,500   $         2,500
Travel                               $        75,000   $      68,000   $        (7,000)
Rent                                 $        16,500   $      20,000   $         3,500
Advertising                          $         7,500   $      10,000   $         2,500

Total Expenses                       $       429,000   $     459,500   $        30,500
% Change

     10.00%
     16.00%
      4.55%
     -9.33%
     21.21%
     33.33%   Change any of the items
              in the blue shaded area
              and if the percentage
      7.11%   change exceeds 20% (+
              or -), the formatting of
              that percentage will
              change.

% Change      For example, change the
              2002 Salaries amount
     10.00%   from $275,000 to
     16.00%   $325,000.
      4.55%
     -9.33%
     21.21%
     33.33%

      7.11%

								
To top