Ways to add, change, and purge Excel conditional formatting

Use Excel conditional formatting to help you visually view and analyze data, identify critical issues, and identify patterns and trends.

Learn more about conditional formatting

When analyzing data, you often ask yourself some questions, such as:

• What are the anomalies in the summary of profits over the past five years?

• What trends have been reflected in marketing surveys over the past two years?

• Who has sold more than ￥50,000 this month?

• What is the overall age distribution of employees?

• What products have an annual growth rate of more than 10%?

In freshman, who has the best results, who has the worst results?

Conditional formatting helps answer these questions because it's easy to do the following: Highlight the cell or range of cells that you care about, emphasize the exception value, and visually display the data using data bars, color scales, and icon sets. Conditional formatting changes the appearance of a range of cells based on criteria. If the condition is True, the format of the range of cells is set based on the condition, and if the condition is False, the cell range is not formatted based on the condition.

Note When you create conditional formatting, you can refer to other cells in the worksheet, such as the =fy2006! A5, but cannot use external references to other workbooks.

Format all cells with a two-color scale

The color scale is an intuitive indicator to help you understand data distribution and data changes. A two-color scale uses the depth of two colors to help you compare cells in a range. The depth of the color indicates the height of the value. For example, in a green and red Two-color scale, you can specify that the higher-value cell's color is greener, and the lower-value cell's color is more red.

Quick Format

1, select a range of cells, or make sure that the active cell is in a table or PivotTable report.

2, on the Start tab, in the Styles group, click the arrow next to Conditional formatting, and then click Levels.

3, choose Two-color Scale.

Tip hover over the color scale icon to see which color scale is a two-color scale. The color above represents a higher value, and the following color represents a lower value.

Advanced formatting

1, select a range of cells, or make sure that the active cell is in a table or PivotTable report.

2, on the Start tab, in the Styles group, click the arrow next to Conditional formatting, and then click Manage Rules. The Conditional Formatting Rule Manager dialog box appears.

3. Do one of the following:

• To add conditional formatting, click New Rule. The New Formatting Rule dialog box appears.

• To change conditional formatting, do the following:

A make sure that the appropriate worksheet or table is selected in the Show Formatting Rules list box.

B You can also change a range of cells in the following ways: In the Apply to box, click Compress dialog box

To temporarily hide the dialog box, select a new range of cells on the worksheet, and then select Expand dialog Box

。

C Select the rule, and then click Edit Rule. The Edit Formatting Rule dialog box appears.

4, under Select Rule type, click Format all cells based on their values.

5. Under Edit Rule Description, in the Format Style list box, select double Color scale.

6, select the "Minimum" and "maximum" type. Do one of the following:

• Format lowest and highest values select Lowest value and highest value.

The min and max values are not entered at this time.

• Format numbers, dates, or time values select number, and then enter minimum and maximum values.

• Format a percentage select percent, and then enter minimum and maximum values.

Valid values are 0 through 100. Please do not enter a percent semicolon.

If you want to visually display all values proportionally, use percentages because the distribution of values is proportional.

• Format percentile select percentile, and then enter minimum and maximum values.

Valid percentile values are 0 to 100. If a range of cells contains more than 8,191 data points, you cannot use percentile values.

The percentile can be used for the following scenarios: to visually display a set of upper-bound values (such as the first 20% values) with a shade of color, visually display a set of lower-bound values (such as the latter 20%) in a different shade of color, because the extreme value represented by these two ratios may distort the display of the data.

• Format formula results Select Formulas, and then enter minimum and maximum values.

The formula must return a number, date, or time value. The formula begins with an equal sign (=). An invalid formula will cause all formatting to not be applied. It is a good idea to test the formula on the worksheet to make sure that the formula does not return an error value.

Comments

·“ The minimum and maximum values are the minimum and maximum values for a range of cells. Make sure the minimum is less than the maximum value.

• You can select a different "minimum" and "maximum" type. For example, you can select the number minimum and the percent maximum value.

7. To select the minimum and maximum values for the color scale, click the color for each tick, and then select the color.

If you want to select a different color or create a custom color, click More Colors.

The selected color scale is displayed in the Preview box.

Format all cells with a three-color scale

The color scale is an intuitive indicator to help you understand data distribution and data changes. The three-color scale uses the depth of three colors to help you compare cells in a range. The depth of the color indicates the high, medium, and low values. For example, in a green, yellow, and red three-color scale, you can specify that the higher-value cell's color is green, the median cell's color is yellow, and the lower-value cell has a red color.

Quick Format

1, select a range of cells, or make sure that the active cell is in a table or PivotTable report.

2, on the Start tab, in the Styles group, click the arrow next to Conditional formatting, and then click Levels.

3, choose the three-color scale. The top color represents a higher value, the middle color represents the middle value, and the bottom color represents the lower value.

Tip hover over the color scale icon to see which color scale is a three-color scale.

Advanced formatting

1, select a range of cells, or make sure that the active cell is in a table or PivotTable report.

2, on the Start tab, in the Styles group, click the arrow next to Conditional formatting, and then click Manage Rules. The Conditional Formatting Rule Manager dialog box appears.

3. Do one of the following:

• To add conditional formatting, click New Rule. The New Formatting Rule dialog box appears.

• To change conditional formatting, do the following:

1. Make sure that the worksheet or table is selected in the Show Formatting Rules list box.

2. You can also change a range of cells in the following ways: In the Apply to box, click Compress dialog box

To temporarily hide the dialog box, select a new range of cells on the worksheet, and then select Expand dialog Box

。

3, select the rule, and then click Edit Rule. The Edit Formatting Rule dialog box appears.

4, under Select Rule type, click Format all cells based on their values.

5. Under Edit Rule Description, in the Format Style list box, select three-color scale.

6, select the "minimum", "midpoint" and "maximum" type. Do one of the following:

• Format lowest and highest values select a midpoint.

The minimum value and highest value are not entered at this time.

• Format numbers, dates, or time values select number, and then enter minimum, midpoint, and maximum values.

• Format a percentage select percent, and then enter minimum, midpoint, and maximum values.

Valid values are 0 through 100. Please do not enter a percent semicolon.

If you want to visually display all values proportionally, use percentages because the distribution of values is proportional.

• Format percentile select percentile, and then enter minimum, midpoint, and maximum values for the value.

Valid percentile values are 0 to 100. If a range of cells contains more than 8,191 data points, you cannot use percentile values.

The percentile can be used for the following scenarios: to visually display a set of upper-bound values (such as the first 20% values) with a shade of color, visually display a set of lower-bound values (such as the latter 20%) in a different shade of color, because the extreme value represented by these two ratios may distort the display of the data.

• Format formula results Select Formulas, and then enter minimum, midpoint, and maximum values.

The formula must return a number, date, or time value. The formula begins with an equal sign (=). An invalid formula will cause all formatting to not be applied. It is a good idea to test the formula on the worksheet to make sure that the formula does not return an error value.

Comments

·“ The minimum, midpoint, and maximum values are the minimum, midpoint, and maximum values for a range of cells. Make sure that the minimum value is less than midpoint and that the midpoint value is less than the maximum value.

• You can select a different minimum, midpoint, and maximum type. For example, you can select the number minimum, the percentile midpoint, and the percent maximum.

• In many cases, the default 50% midpoint value works best, but you can adjust the default value to suit your needs.

7. To select the minimum, midpoint, and maximum values for the color scale, click the color for each tick, and then select the color.

If you want to select a different color or create a custom color, click More Colors.

The selected color scale is displayed in the Preview box.

Format all cells using the data bar

A data bar can help you see the values of a cell relative to other cells. The length of the data bar represents the value in the cell. The longer the bar, the higher the value, the shorter the data bar, and the lower the value. Data bars are especially useful when looking at higher and lower values in large amounts of data, such as the best-selling and unsalable toys in the holiday sales report.