Excel 简明教程

Conditional Format in Excel 2010

Conditional Formatting

MS Excel 2010 条件格式功能让你能够对值范围进行格式化，以便自动对超出一定限制的值进行格式化。

MS Excel 2010 Conditional Formatting feature enables you to format a range of values so that the values outside certain limits, are automatically formatted.

选择 Home Tab » Style group » Conditional Formatting dropdown 。

Choose Home Tab » Style group » Conditional Formatting dropdown.

Various Conditional Formatting Options

  1. Highlight Cells Rules − It opens a continuation menu with various options for defining the formatting rules that highlight the cells in the cell selection that contain certain values, text, or dates, or that have values greater or less than a particular value, or that fall within a certain ranges of values.

假设你想查找金额为 0 的单元格并将其标记为红色。选择单元格范围 » 开始选项卡 » 条件格式下拉菜单 » 突出显示单元格规则 » 等于。

Suppose you want to find cell with Amount 0 and Mark them as red.Choose Range of cell » Home Tab » Conditional Formatting DropDown » Highlight Cell Rules » Equal To.

apply condition formatting

单击确定后，值为零的单元格将标记为红色。

After Clicking ok, the cells with value zero are marked as red.

conditional formatting applied

  1. Top/Bottom Rules − It opens a continuation menu with various options for defining the formatting rules that highlight the top and bottom values, percentages, and above and below average values in the cell selection.

假设你想突出显示最高 10% 的行，你可以使用这些最高/最低规则来实现。

Suppose you want to highlight the top 10% rows you can do this with these Top/Bottom rules.

conditional format top10

  1. Data Bars − It opens a palette with different color data bars that you can apply to the cell selection to indicate their values relative to each other by clicking the data bar thumbnail.

通过此条件格式，数据条将出现在每个单元格中。

With this conditional Formatting data Bars will appear in each cell.

data bar condition

  1. Color Scales − It opens a palette with different three- and two-colored scales that you can apply to the cell selection to indicate their values relative to each other by clicking the color scale thumbnail.

请参阅下方的带有颜色比例尺的截图，条件格式已应用。

See the below screenshot with Color Scales, conditional formatting applied.

color scales

  1. Icon Sets − It opens a palette with different sets of icons that you can apply to the cell selection to indicate their values relative to each other by clicking the icon set.

请参阅下方的带有图标集条件格式的截图，条件格式已应用。

See the below screenshot with Icon Sets conditional formatting applied.

icon set

  1. New Rule − It opens the New Formatting Rule dialog box, where you define a custom conditional formatting rule to apply to the cell selection.

  2. Clear Rules − It opens a continuation menu, where you can remove the conditional formatting rules for the cell selection by clicking the Selected Cells option, for the entire worksheet by clicking the Entire Sheet option, or for just the current data table by clicking the This Table option.

  3. Manage Rules − It opens the Conditional Formatting Rules Manager dialog box, where you edit and delete particular rules as well as adjust their rule precedence by moving them up or down in the Rules list box.