Excel in 2007 - Conditional Formatting

Excel in 2007 - Conditional Formatting

Overview

You can monitor formula results or other cell values by applying conditional formats. A conditional format is a format, such as cell shading, border or font colour that Excel automatically applies to cells if a specified condition is true. For example, you can apply a green font colour to cell(s) where the sales figure exceeds the forecast, and red if sales fall short.

4. Select Highlight Cells Rules .

NB. Conditional formatting overrides any previous, manually applied formatting. NB: Microsoft Excel 2007 can create up to 64 conditional formatting conditions. Earlier versions (97-2003), however, could only support 3. Additionally, Excel 2007 can conditionally format cells in ways that earlier versions could not (eg. cell overlap, data bars and icon sets etc). It is important to realise that when backsaving an Excel 2007 file as 97-2003, the aforementioned may not display or lead to unexpected results.

"Quick Formatting" using Pre-set Criteria

Finding Precise Values

To more easily identify cells within a range of cells, you can format them based on a comparison operator. For example, in an inventory sheet, you can highlight products with fewer than 10 items in stock with a yellow shading. Or in a retail shop summary sheet, you may wish to identify shops with profits greater than 10%, sales volume less than ?10,000 and region equal to "South-East."

1. Select the cell(s) to which you want to apply conditional formatting.

2. Select the Home tab.

3. From the Styles group, click on Conditional

Formatting

.

5. From the submenu select the criteria that you want to use, eg. Greater Than..., Less Than..., etc.

6. Enter the value(s) for the criteria.

7. From the with dropdown list, select the formatting option that you want to apply to cells that meet the criteria.

8. Click OK.

Finding Calculated Values

Excel 2007 offers a variety of pre-set criteria for identifying and formatting cells that contain values within a numeric range. These include:

? Top/bottom items in a selected range, such as the top 5 selling products in a sales report.

1

? Items in the top/bottom percentage of selected data, such as the bottom 15% of students in an exam result.

? Items which are above/below the average of the selected range, such as employees falling into the top 5% pay bracket.

1. Select the cell(s) to which you want to apply conditional formatting.

2. Select the Home tab.

3. From the Styles group, click on Conditional

Formatting

.

4. Select Top/Bottom Rules.

5. From the submenu, select the criteria that you want to use, eg. Top 10 Items..., Top 10%..., Above Average..., etc.

Excel in 2007 - Conditional Formatting

6. Enter the value for the criteria, if necessary.

7. From the with dropdown list, select the formatting option that you want to apply to cells that meet the criteria.

8. Click OK.

Clearing Conditional Formats

Do one of the following:

Worksheet

1. Select the Home tab.

2. Click the Conditional Formatting button in the Styles group.

3. Point at Clear Rules .

4. Select Clear Rules from Entire Sheet from the sub menu.

A Range of Cells, Table or PivotTable

1. Select the range, table or PivotTable from which you wish to clear the conditional formatting.

2. Select the Home tab.

3. Click the Conditional Formatting button in the Styles group.

4. Point at Clear Rules .

5. Depending on what you

have selected, click

Clear Rules from

Selected Cells / This

Table

/

This

PivotTable.

Conditionally Format Cells Using Special Effects

Excel 2007 provides functionality to conditionally format cells using special effects. These are NOT supported in previous versions of Excel and will not, therefore, be displayed if the file is back-saved. The special effects consist of:

? Data Bars ? graphically displays the value of a cell in relation to the other selected cells, by drawing a bar at the left of the cell. The longer the bar, the higher the value in comparison to the rest.

? Colour Scales - graphically displays the value of a cell in relation to the other selected cells, by adding shading to the cells. The lighter the shading, the higher the value in comparison to the rest.

? Icon Sets - graphically displays the value of a cell in relation to the other selected cells, by drawing symbols at the left of the cell. Different symbols represent the value of the cell in comparison to the rest.

1. Select the range that you wish to add special conditional formatting effects to.

2

2. Click the Conditional Formatting button in the Styles group.

3. Point at Data Bars, Color Scales or Icon Sets.

4. Hover the mouse pointer over the side menu to preview on the selected data, the various styles available.

5. Click your preferred style.

Custom Conditional Formatting Rules

The previous part of this guide has concentrated on applying built-in conditions and formatting styles. You can, however, customise the built-in styles and criteria and even create additional, more advanced criteria of your own. Excel 2007 offers you 6 categories upon which to base your own conditional formatting styles and criteria.

? Format all cells based on their value ? allows you to customise the colour and criteria that is used for the "special effects" conditional formatting.

? Format only cells that contain ? allows you to create your own custom criteria with associated conditional formatting style. For example, format in red font the sales values which fall below 500.

? Format only top or bottom ranked values ? allows you to create a conditional formatting style for items that fall into a top or bottom bracket of the selected range. For example, format in green font the students whose exam mark falls in the top 5% of the total.

? Format only values that are above or below average ? allows you to create a custom format which identifies cell values which are above or below a mid-point. For example, format in blue font all the employees who are earning above the average salary.

Excel in 2007 - Conditional Formatting

? Format only unique or duplicate value ? allows you to identify where there are identical cells values in a selected range, or where there are non-identical values. For example, identify in bold font on a list of order numbers, where one may have been accidentally entered twice.

? Use a formula to determine which cells to format ? allows you to create a custom format to identify cell values by means of a calculation. For example, identify with a grey cell shading all the cell which contain a value 2% above the mode of the selected cells.

To Create a Simple Custom Rule

1. Select the cell(s) that you want to apply conditional formatting to.

2. Click the Conditional Formatting button in the Styles group.

3. Click New Rule...

.

4. Pick the rule type from the Select a Rule Type: list (see previous section).

5. Specify your preferences and criteria in the Edit the Rule Description: section.

6. Click OK.

To Create an "Advanced" Custom Rule

Using OR conditions

An "advanced" conditional format would typically be one that uses multiple conditions and associated formats. For example, in a list of sales figures, you could format in blue font those that are 5000 and above, format in green font those that are between 3000 and 4999 and format in red font those that are 3000 or below.

1. Select the range that you want to apply the conditional formatting to.

2. Click the Conditional Formatting button in the Styles group.

3. Click Manage Rules...

.

4. Click New Rule...

.

5. Pick the rule type from the Select a Rule Type: list.

6. Specify your preferences and criteria in the Edit the Rule Description: section.

7. Click OK.

8. Continue adding new rules as described in 4 to 7 above.

9. Once all the rules have been added to the Rule list,

ensure they are in the correct order of precedence, ie.

listed from top to bottom in the order that they are to

be evaluated. You may need to use the blue up/down

arrows

to re-order them.

In the example below, the cells in range F2:F29 will have their font colour made blue if the value is 10000 or more, green if the cell value is 5000 or more and red if it is below 5000.

3

10. Click OK.

Using AND conditions

Excel 2007 also allows you to conditionally format cells in two different ways dependant on two separate conditions. For example, in a list of sales figures, you could format in blue font those that are 5000 or above, format in red font those that are below 5000 AND ALSO add a yellow fill colour to the top 5 sales and a lilac fill to the bottom 5. In the example below, the cells in range F2:F29 will have their font colour made blue if their value is 10000 or more, red if their value is less than 10000 AND the top 5 will have a yellow fill and the bottom 5 will have a lilac fill.

Rule Precedence

When creating complex rules involving multiple conditions, rules are evaluated by how they are listed in the dialog box (ie. from top to bottom). It can sometimes happen, therefore, that a rule that you wish to apply to cells is negated by another one further up or down the list. To overcome this, is it usually necessary to change the order of

Excel in 2007 - Conditional Formatting

precedence by re-ordering your rules using the Move Up

and Move Down arrows

, and/or applying the Stop

If True check box.

Editing and Deleting Custom Rules

To edit a rule:

1. Select the cell(s) for which you want to edit or delete the conditional formatting for.

7. Click OK.

2. Click the Conditional Formatting button in the Styles group.

3. Click Manage Rules...

.

4. Select Current Selection or This Worksheet from the Show formatting rules for: drop-down list.

5. Select the rule to be edited.

6. Click Edit Rule... 7. Make the necessary changes. 8. Click OK.

To delete a rule:

1. Select the cell(s) for which you want to edit or delete the conditional formatting for.

2. Click the Conditional Formatting button in the Styles group.

3. Click Manage Rules...

.

4. Select Current Selection or This Worksheet from the Show formatting rules for: drop-down list.

5. Select the rule to delete.

6. Click Delete Rule...

.

4

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download