Conditional format in excel 2010

Excel for nfnagradi.net 365Excel for the webExcel 2019Excel 2016Excel 2013Excel 2010Excel 2007More...Less

Conditional formatting can help make patterns và trends in your data more apparent. To use it, you create rules that determine the format of cells based on their values, such as the following monthly temperature data with cell colors tied khổng lồ cell values. 

*

You can apply conditional formatting lớn a range of cells (either a selection or a named range), an Excel table, và in Excel for Windows, even a PivotTable report.

Bạn đang xem: Conditional format in excel 2010



Conditional formatting typically works the same way in a range of cells, an Excel table, or a PivotTable report. However, conditional formatting in a PivotTable report has some extra considerations:

There are some conditional formats that don"t work with fields in the Values area of a PivotTable report. For example, you can"t format such fields based on whether they contain quality or duplicate values. These restrictions are mentioned in the remaining sections of this article, where applicable.

If you change the layout of the PivotTable report by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed.

The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows.


Note: In the data hierarchy, children vì chưng not inherit conditional formatting from the parent, & the parent does not inherit conditional formatting from the children.


There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, và by value field.

The default method of scoping fields in the Values area is by selection. You can change the scoping method lớn the corresponding field or value field by using the Apply formatting rule to option button, the New Formatting Rule dialog box, or the Edit Formatting Rule dialog box.

Method

Use this method if you want khổng lồ select

Scoping by selection

A contiguous mix of fields in the Values area, such as all of the sản phẩm totals for one region.

A non-contiguous mix of fields in the Values area, such as sản phẩm totals for different regions across levels in the data hierarchy.

Scoping by value field

Avoid making many non-contiguous selections.

Conditionally format a set of fields in the Values area for all levels in the hierarchy of data.

Include subtotals & grand totals.

Scoping by corresponding field

Avoid making many non-contiguous selections.

Conditionally format a mix of fields in the Values area for one cấp độ in the hierarchy of data.

Exclude subtotals.

When you conditionally format fields in the Values area for top, bottom, above average, or below average values, the rule is based on all visible values by mặc định. However, when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

A column và its parent row field.

A row and its parent column field.


Use the Quiông chồng Analysis button khổng lồ apply selected conditional formatting lớn the selected data. The Quick Analysis button appears automatically when you select data.

Select the data that you want to conditionally format. The Quichồng Analysis button appears on the lower-right corner of the selection.

*

Clichồng the Quiông xã Analysis button , or press Ctrl+Q.

In the pop-up that appears, on the Formatting tab, move sầu your mouse over the different options khổng lồ see a Live Preview on your data, and then click on the formatting option you want.

*


Notes: 

The formatting options that appear in the Formatting tab depend on the data you have sầu selected. If your selection contains only text, then the available options are Text, Duplicate, Unique, Equal To, và Clear. When the selection contains only numbers, or both text and numbers, then the options are Data Bars, Colors, Inhỏ Sets, Greater, Top 10%, và Clear.

Live pReviews will only render for those formatting options that can be used on your data. For example, if your selected cells don"t contain matching data & you select Duplicate, the live pđánh giá will not work.


If the Text that Contains dialog box appears, enter the formatting option you want to apply và cliông chồng OK.

If you"d like lớn watch a Clip that shows how lớn use Quiông xã Analysis lớn apply conditional formatting, see Video: Use conditional formatting.


*


Quiông chồng formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, cliông xã the arrow next to Conditional Formatting, & then cliông chồng Color Scales.

Select a two-color scale.

Hover over the color scale icons khổng lồ see which inhỏ is a two-color scale. The top color represents higher values, & the bottom color represents lower values.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Formatting Options button that appears next khổng lồ a PivotTable field that has conditional formatting applied.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, cliông xã the arrow next to Conditional Formatting, & then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

To add a completely new conditional format, clichồng New Rule. The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then cliông chồng Duplicate Rule. The duplicate rule appears in the dialog box. Select the duplicate, then select Edit Rule. The Edit Formatting Rule dialog box appears. 

To change a conditional format, do the following:

Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for danh sách box.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box khổng lồ temporarily hide the dialog box, by selecting the new range of cells on the worksheet, và then by selecting Expvà Dialog.

Select the rule, and then cliông xã Edit rule. The Edit Formatting Rule dialog box appears.

Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:

Selection: Click Selected cells.

All cells for a Value label: Cliông xã All cells showing values.

All cells for a Value label, excluding subtotals and the grand total: Cliông chồng All cells showing values for .

Under Select a Rule Type, clichồng Format all cells based on their values (default).

Under Edit the Rule Description, in the Format Style các mục box, select 2-Color Scale.

To select a type in the Type box for MinimumMaximum, bởi one of the following:

Format lowest and highest values: Select Lowest ValueHighest Value.

In this case, you bởi not enter a Minimum & MaximumValue.

Format a number, date, or time value: Select Number and then enter a Minimum & MaximumValue.

Format a percentage Percent: Enter a Minimum and MaximumValue.

Valid values are from 0 (zero) to lớn 100. Don"t enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

Format a percentile: Select Percentile & then enter a Minimum and MaximumValue. Valid percentiles are from 0 (zero) to lớn 100.

Use a percentile when you want lớn visualize a group of high values (such as the top 20thpercentile) in one color grade proportion and low values (such as the bottom 20th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

Format a formula result: Select Formula and then enter values for MinimumMaximum.

The formula must return a number, date, or time value.

Start the formula with an equal sign (=).

Invalid formulas result in no formatting being applied.

It"s a good idea to lớn thử nghiệm the formula lớn make sure that it doesn"t return an error value.


Notes: 

Make sure that the Minimum value is less than the Maximum value.

You can choose a different type for the Minimum & Maximum. For example, you can choose a number for Minimum a percentage for Maximum.


To choose a Minimum & Maximum color scale, cliông chồng Color for each, và then select a color.

If you want lớn choose additional colors or create a custom color, cliông xã More Colors. The color scale you select is shown in the Preview box.


Quiông chồng formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, click the arrow next lớn Conditional Formatting, and then cliông chồng màu sắc Scales.

Select a three-color scale. The top color represents higher values, the center color represents middle values, & the bottom color represents lower values.

Hover over the color scale icons khổng lồ see which icon is a three-color scale.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Formatting Options button that appears next to a PivotTable field that has conditional formatting applied..

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, cliông chồng the arrow next khổng lồ Conditional Formatting, và then cliông xã Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

To add a new conditional format, cliông chồng New Rule. The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then click Duplicate Rule. The duplicate rule is copied và appears in the dialog box. Select the duplicate, then select Edit Rule. The Edit Formatting Rule dialog box appears. 

To change a conditional format, vị the following:

Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for menu box.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to lớn temporarily hide the dialog box, by selecting the new range of cells on the worksheet, và then by selecting Exp& Dialog.

Select the rule, and then cliông chồng Edit rule. The Edit Formatting Rule dialog box appears.

Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:

Selection: Clichồng Just these cells.

Corresponding field: Clichồng All cells with the same fields.

Value field: Cliông chồng All cells.

Under Select a Rule Type, click Format all cells based on their values.

Under Edit the Rule Description, in the Format Style menu box, select 3-Màu sắc Scale.

Select a type for Minimum, Midpoint, & Maximum. Do one of the following:

Format lowest và highest values: Select a Midpoint.

In this case, you do not enter a Lowest & HighestValue.

Format a number, date, or time value: Select Number & then enter a value for Minimum, Midpoint, & Maximum.

Format a percentage: Select Percent và then enter a value for Minimum, Midpoint, and Maximum. Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.

Use a percentage when you want to lớn visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional.

Format a percentile: Select Percentile and then enter a value for Minimum, Midpoint, & Maximum.

Valid percentiles are from 0 (zero) lớn 100.

Use a percentile when you want khổng lồ visualize a group of high values (such as the top 20th percentile) in one color grade proportion and low values (such as the bottom 20th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

Format a formula result: Select Formula and then enter a value for Minimum, Midpoint, and Maximum.

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting being applied. It"s a good idea to lớn chạy thử the formula to lớn make sure that it doesn"t return an error value.


Notes: 

You can phối minimum, midpoint, & maximum values for the range of cells. Make sure that the value in Minimum is less than the value in Midpoint, which in turn is less than the value in Maximum.

You can choose a different type for Minimum, Midpoint, and Maximum. For example, you can choose a Minimum number, Midpoint percentile, & Maximum percent.

In many cases, the mặc định Midpoint value of 50 percent works best, but you can adjust this to fit chất lượng requirements.


To choose a Minimum, Midpoint, and Maximum color scale, click Color for each, and then select a color.

To choose additional colors or create a custom color, cliông xã More Colors.

The color scale you select is shown in the Preview box.


A data bar helps you see the value of a cell relative lớn other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, và a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling và bottom selling toys in a holiday sales report.

The example shown here uses data bars to highlight dramatic positive and negative values. You can format data bars so that the data bar starts in the middle of the cell, và stretches lớn the left for negative values.

*


Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to lớn those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to lớn return a value (such as 0 or "N/A") instead of an error value.


Quiông chồng formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Style group, click the arrow next lớn Conditional Formatting, cliông chồng Data Bars, and then select a data bar inhỏ.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, cliông xã the arrow next lớn Conditional Formatting, & then clichồng Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

To add a conditional format, cliông xã New Rule. The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then click Duplicate Rule. The duplicate rule is copied and appears in the dialog box. Select the duplicate, then select Edit Rule. The Edit Formatting Rule dialog box appears. 

To change a conditional format, vị the following:

Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for danh mục box.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box lớn temporarily hide the dialog box, by selecting the new range of cells on the worksheet, và then by selecting Expvà Dialog.

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

Under Apply Rule To, to lớn optionally change the scope for fields in the Values area of a PivotTable report by:

Selection: Cliông chồng Just these cells.

Corresponding field: Cliông chồng All cells with the same fields.

Value field: Clichồng All cells.

Under Select a Rule Type, cliông chồng Format all cells based on their values.

Under Edit the Rule Description, in the Format Style danh sách box, select Data Bar.

Select a MinimumMaximumType. Do one of the following:

Format lowest & highest values: Select Lowest Value & Highest Value.

In this case, you vị not enter a value for Minimum & Maximum.

Format a number, date, or time value: Select Number và then enter a MinimumMaximumValue.

Format a percentage: Select Percent & then enter a value for Minimum & Maximum.

Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.

Use a percentage when you want to lớn visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional.

Format a percentile Select Percentile & then enter a value for MinimumMaximum.

Valid percentiles are from 0 (zero) to 100.

Use a percentile when you want to lớn visualize a group of high values (such as the top 20th percentile) in one data bar proportion & low values (such as the bottom 20th percentile) in another data bar proportion, because they represent extreme values that might skew the visualization of your data.

Format a formula result Select Formula, và then enter a value for Minimum and Maximum.

The formula has to return a number, date, or time value.

Start the formula with an equal sign (=).

Invalid formulas result in no formatting being applied.

It"s a good idea khổng lồ chạy thử the formula to make sure that it doesn"t return an error value.


Notes: 

Make sure that the Minimum value is less than the Maximum value.

You can choose a different type for Minimum & Maximum. For example, you can choose a Minimum number and a Maximum percent.


To choose a Minimum & Maximum color scale, clichồng Bar Color.

If you want to choose additional colors or create a custom color, clichồng More Colors. The bar color you select is shown in the Preview box.

To show only the data bar và not the value in the cell, select Show Bar Only.

To apply a solid border khổng lồ data bars, select Solid Border in the Border các mục box and choose a color for the border.

To choose between a solid bar và a gradiated bar, choose Solid Fill or Gradient Fill in the Fill list box.

To format negative bars, cliông chồng Negative Value và Axis and then, in the Negative Value và Axis Settings dialog box, choose options for the negative bar fill and border colors. You can choose position settings & a color for the axis. When you are finished selecting options, click OK.

You can change the direction of bars by choosing a setting in the Bar Direction danh mục box. This is mix to lớn Context by mặc định, but you can choose between a left-to-right and a right-to-left direction, depending on how you want to lớn present your data.


The example shown here works with several examples of conditional formatting inhỏ sets.

*

You can choose lớn show icons only for cells that meet a condition; for example, displaying a warning ibé for those cells that fall below a critical value & no icons for those that exceed it. To bởi vì this, you hide icons by selecting No Cell Icon from the icon drop-down danh mục next lớn the inhỏ when you are setting conditions. You can also create your own combination of inhỏ sets; for example, a green "symbol" check mark, a yellow "traffic light", & a red "flag."


Tip: If any cells in the selection contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied lớn those cells, use an IS or IFERROR function khổng lồ return a value (such as 0 or "N/A") instead of an error value.


Quiông xã formatting

Select the cells that you want khổng lồ conditionally format.

On the Home tab, in the Style group, cliông chồng the arrow next to Conditional Formatting, cliông xã Ibé Set, and then select an inhỏ phối.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

Advanced formatting

Select the cells that you want lớn conditionally format.

On the Home tab, in the Styles group, cliông chồng the arrow next lớn Conditional Formatting, và then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

To add a conditional format, clichồng New Rule. The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then cliông chồng Duplicate Rule. The duplicate rule is copied và appears in the dialog box. Select the duplicate, then select Edit Rule. The Edit Formatting Rule dialog box appears. 

To change a conditional format, vị the following:

Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to lớn temporarily hide the dialog box, by selecting the new range of cells on the worksheet, và then by selecting Expvà Dialog.

Select the rule, và then cliông chồng Edit rule. The Edit Formatting Rule dialog box appears.

Under Apply Rule To, lớn optionally change the scope for fields in the Values area of a PivotTable report by:

Selection: Cliông chồng Just these cells.

Corresponding field: Cliông xã All cells with the same fields.

Value field: Clichồng All cells.

Under Select a Rule Type, cliông chồng Format all cells based on their values.

Under Edit the Rule Description, in the Format Style danh mục box, select Icon Set.

Select an ibé phối. The default is 3 Traffic Lights (Unrimmed). The number of icons & the default comparison operators và threshold values for each ibé can vary for each ibé mix.

You can adjust the comparison operators & threshold values. The default range of values for each ibé are equal in kích cỡ, but you can adjust these khổng lồ fit your unique requirements. Make sure that the thresholds are in a logical sequence of highest to lớn lowest from top khổng lồ bottom.

Do one of the following:

Format a number, date, or time value: Select Number.

Format a percentage: Select Percent.

Valid values are from 0 (zero) to lớn 100. Do not enter a percent (%) sign.

Use a percentage when you want to lớn visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional.

Format a percentile: Select Percentile. Valid percentiles are from 0 (zero) khổng lồ 100.

Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) using a particular inhỏ & low values (such as the bottom 20th percentile) using another ibé, because they represent extreme values that might skew the visualization of your data.

Format a formula result: Select Formula, and then enter a formula in each Value box.

The formula must return a number, date, or time value.

Start the formula with an equal sign (=).

Invalid formulas result in no formatting being applied.

It"s a good idea khổng lồ thử nghiệm the formula to make sure that it doesn"t return an error value.

To make the first inhỏ represent lower values and the last ibé represent higher values, select Reverse Inhỏ Order.

To show only the inhỏ và not the value in the cell, select Show Inhỏ Only.


Notes: 

You may need khổng lồ adjust the column width lớn accommodate the icon.

The kích thước of the icon shown depends on the fonts form size that is used in that cell. As the size of the phông is increased, the size of the inhỏ increases proportionally.


To more easily find specific cells, you can format them by using a comparison operator. For example, in an inventory worksheet sorted by categories, you could highlight products with fewer than 10 items on hand in yellow. Or, in a retail store summary worksheet, you might identify all stores with profits greater than 10%, sales volumes less than $100,000, & region equal lớn "SouthEast."

The examples shown here work with examples of built-in conditional formatting criteria, such as Greater Than, và Top %. This formats cities with a population greater than 2,000,000 with a green background & average high temperatures in the top 30% with orange.

*


Note: You cannot conditionally format fields in the Values area of a PivotTable report by text or by date, only by number.


Quichồng formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Style group, cliông xã the arrow next to lớn Conditional Formatting, and then cliông xã Highlight Cells Rules.

Select the comm& you want, such as Between, Equal To Text that Contains, or A Date Occurring.

Enter the values you want khổng lồ use, & then select a format.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

If you"d lượt thích lớn watch videos of these techniques, see Video: Conditionally format text and Video: Conditionally format dates.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, clichồng the arrow next to Conditional Formatting, & then clichồng Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

To add a conditional format, cliông chồng New Rule. The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then cliông xã Duplicate Rule. The duplicate rule is copied & appears in the dialog box. Select the duplicate, then select Edit Rule. The Edit Formatting Rule dialog box appears. 

To change a conditional format, vì the following:

Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for các mục box.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to lớn temporarily hide the dialog box, by selecting the new range of cells on the worksheet or on other worksheets, và then by selecting Expand Dialog.

Select the rule, và then clichồng Edit rule. The Edit Formatting Rule dialog box appears.

Under Apply Rule To, lớn optionally change the scope for fields in the Values area of a PivotTable report by:

Selection: Cliông chồng Just these cells.

Xem thêm: Chăm Sóc Tóc Sau Khi Ép Tóc Bao Lâu Thì Gội Đầu, Cho Mái Tóc Ép Đẹp Mượt Mà

Corresponding field: Cliông xã All cells with the same fields.

Value field: Cliông xã All cells.

Under Select a Rule Type, clichồng Format only cells that contain.

Under Edit the Rule Description, in the Format only cells with list box, vì chưng one of the following:

Format by number, date, or time: Select Cell Value, select a comparison operator, & then enter a number, date, or time.

You can also enter a formula that returns a number, date, or time value.

If you enter a formula, start it with an equal sign (=).

Invalid formulas result in no formatting being applied.

It"s a good idea khổng lồ kiểm tra the formula to make sure that it doesn"t return an error value.

Format by text: Select Specific Text, choosing a comparison operator, và then enter text.

For example, select Contains và then enter Silver, or select Starting with and then enter Tri.

Quotes are included in the tìm kiếm string, and you may use wildthẻ characters. The maximum length of a string is 255 characters.

You can also enter a formula that returns text.

If you enter a formula, start it with an equal sign (=).

Invalid formulas result in no formatting being applied.

Format cells with blanks or no blanks: Select Blanks or No Blanks.

A blank value is a cell that contains no data và is different from a cell that contains one or more spaces (spaces are considered as text).

Format cells with error or no error values: Select Errors or No Errors.

Error values include: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, & #NULL!.

To specify a format, click Format. The Format Cells dialog box appears.

Select the number, fonts, border, or fill format you want khổng lồ apply when the cell value meets the condition, và then cliông xã OK.

You can choose more than one format. The formats you select are shown in the Preview box.


You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify. For example, you can find the top 5 selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department .

Quick formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Style group, cliông xã the arrow next to Conditional Formatting, & then clichồng Top/Bottom Rules.

Select the commvà you want, such as Top 10 items or Bottom 10 %.

Enter the values you want khổng lồ use, và then select a format.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, cliông chồng the arrow next to Conditional Formatting, và then cliông chồng Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

To add a conditional format, cliông chồng New Rule. The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then click Duplicate Rule. The duplicate rule is copied & appears in the dialog box. Select the duplicate, then select Edit Rule. The Edit Formatting Rule dialog box appears. 

To change a conditional format, vì chưng the following:

Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for các mục box.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog.

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

Under Apply Rule To, khổng lồ optionally change the scope fields in the Values area of a PivotTable report by:

Selection: Click Just these cells.

Corresponding field: Clichồng All cells with the same fields.

Value field: Cliông chồng All cells.

Under Select a Rule Type, cliông xã Format only top or bottom ranked values.

Under Edit the Rule Description, in the Format values that rank in the menu box, select Top or Bottom.

Do one of the following:

To specify a top or bottom number, enter a number và then clear the % of the selected range box. Valid values are 1 to lớn 1000.

To specify a top or bottom percentage, enter a number & then select the % of the selected range box. Valid values are 1 lớn 100.

Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.

By mặc định, the conditional format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

A column & its parent row field, by selecting each Column group.

A row & its parent column field, by selecting each Row group.

To specify a format, click Format. The Format Cells dialog box appears.

Select the number, phông, border, or fill format you want to lớn apply when the cell value meets the condition, và then click OK.

You can choose more than one format. The formats you select are shown in the Preview box.


You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a chất lượng rating.

Quichồng formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Style group, cliông chồng the arrow next to Conditional Formatting, và then cliông xã Top/Bottom Rules.

Select the comm& you want, such as Above Average or Below Average.

Enter the values you want khổng lồ use, and then select a format.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, cliông xã the arrow next khổng lồ Conditional Formatting, & then cliông xã Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

To add a conditional format, cliông chồng New Rule. The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then cliông chồng Duplicate Rule. The duplicate rule is copied và appears in the dialog box. Select the duplicate, then select Edit Rule. The Edit Formatting Rule dialog box appears. 

To change a conditional format, vị the following:

Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box lớn temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog.

Select the rule, và then clichồng Edit rule. The Edit Formatting Rule dialog box appears.

Under Apply Rule To, to lớn optionally change the scope for fields in the Values area of a PivotTable report by:

Selection: Click Just these cells.

Corresponding field: Click All cells with the same fields.

Value field: Click All cells.

Under Select a Rule Type, cliông chồng Format only values that are above sầu or below average.

Under Edit the Rule Description, in the Format values that are danh sách box, bởi vì one of the following:

To format cells that are above or below the average for all of the cells in the range, select Above or Below.

To format cells that are above or below one, two, or three standard deviations for all of the cells in the range, select a standard deviation.

Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.

By mặc định, the conditionally format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

A column & its parent row field, by selecting each Column group.

A row & its parent column field, by selecting each Row group.

Cliông xã Format to display the Format Cells dialog box.

Select the number, phông, border, or fill format you want lớn apply when the cell value meets the condition, và then cliông xã OK.

You can choose more than one format. The formats you select are displayed in the Preview box.


In the example shown here, conditional formatting is used on the Instructor column lớn find instructors that are teaching more than one class (duplicate instructor names are highlighted in a pale red color). Grade values that are found just once in the Grade column (unique values) are highlighted in a green color.

*

Quick formatting

Select the cells that you want to conditionally format.

On the Home tab, in the Style group, cliông chồng the arrow next lớn Conditional Formatting, và then clichồng Highlight Cells Rules.

Select Duplicate Values.

Enter the values you want to lớn use, và then select a format.

Advanced formatting

Select the cells that you want to lớn conditionally format.

On the Home tab, in the Styles group, click the arrow next to lớn Conditional Formatting, và then cliông chồng Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

To add a conditional format, cliông xã New Rule. The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then cliông chồng Duplicate Rule. The duplicate rule is copied and appears in the dialog box. Select the duplicate, then select Edit Rule. The Edit Formatting Rule dialog box appears. 

To change a conditional format, bởi the following:

Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, và then by selecting Exp& Dialog.

Select the rule, and then clichồng Edit rule. The Edit Formatting Rule dialog box appears.

Under Select a Rule Type, cliông chồng Format only unique or duplicate values.

Under Edit the Rule Description, in the Format all danh mục box, select unique or duplicate.

Cliông xã Format lớn display the Format Cells dialog box.

Select the number, phông, border, or fill format you want khổng lồ apply when the cell value meets the condition, and then click OK.

You can choose more than one format. The formats you select are shown in the Preview box.


Select the cells that you want to format.

On the Home tab, clichồng Conditional Formatting > New Rule.

*

Create your rule and specify its format options, then cliông xã OK.

If you don"t see the options that you want, you can use a formula to determine which cells khổng lồ format - see the next section for steps).


If you don"t see the exact options you need when you create your own conditional formatting rule, you can use a logical formula to lớn specify the formatting criteria. For example, you may want lớn compare values in a selection lớn a result returned by a function or evaluate data in cells outside the selected range, which can be in another worksheet in the same workbook. Your formula must return True or False (1 or 0), but you can use conditional xúc tích và ngắn gọn to lớn string together a set of corresponding conditional formats, such as different colors for each of a small mix of text values (for example, hàng hóa category names).


Note: You can enter cell references in a formula by selecting cells directly on a worksheet or other worksheets. Selecting cells on the worksheet inserts absolute cell references. If you want Excel to adjust the references for each cell in the selected range, use relative sầu cell references. For more information, see Create or change a cell reference và Switch between relative sầu, absolute, and mixed references.


Tip: If any cells contain a formula that returns an error, conditional formatting is not applied to lớn those cells. To address this, use IS functions or an IFERROR function in your formula khổng lồ return a value that you specify (such as 0, or "N/A") instead of an error value.


On the Home tab, in the Styles group, click the arrow next khổng lồ Conditional Formatting, và then cliông xã Manage Rules.

The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

To add a conditional format, clichồng New Rule. The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then cliông chồng Duplicate Rule. The duplicate rule is copied và appears in the dialog box. Select the duplicate, then select Edit Rule. The Edit Formatting Rule dialog box appears. 

To change a conditional format, vị the following:

Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for danh mục box.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to lớn temporarily hide the dialog box, by selecting the new range of cells on the worksheet or other worksheets, và then by clicking Exp& Dialog.

Select the rule, and then clichồng Edit rule. The Edit Formatting Rule dialog box appears.

Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report, vày the following:

To scope by selection: Clichồng Selected cells.

To scope by corresponding field: Click All cells showing values.

To scope by Value field: Cliông xã All cells showing for .

Under Select a Rule Type, click Use a formula to lớn determine which cells lớn format.

Under Edit the Rule Description, in the Format values where this formula is true menu box, enter a formula.You have to start the formula with an equal sign (=), và the formula must return a logical value of TRUE (1) or FALSE (0).

Clichồng Format lớn display the Format Cells dialog box.

The following Clip shows you the basics of using formulas with conditional formatting.



If you want to lớn apply an existing formatting style lớn new or other data on your worksheet, you can use Format Painter to lớn copy the conditional formatting to lớn that data.

Clichồng the cell that has the conditional formatting that you want lớn copy.

Click Home > Format Painter.

*

The pointer changes to a paintbrush.


If your worksheet contains conditional formatting, you can quickly locate the cells so that you can copy, change, or delete the conditional formats. Use the Go To Special command to find only cells with a specific conditional format, or khổng lồ find all cells that have conditional formats.

Find all cells that have a conditional format

Cliông xã any cell that does not have a conditional format.

On the Home tab, in the Editing group, click the arrow next to Find và Select, và then clichồng Conditional Formatting.

Find only cells that have the same conditional format

Cliông xã any cell that has the conditional format that you want lớn find.

On the Home tab, in the Editing group, cliông chồng the arrow next to lớn Find và Select, and then clichồng Go To Special.

Clichồng Conditional formats.

Cliông xã Same under Data validation.


When you use conditional formatting, you phối up rules that Excel uses to lớn determine when to lớn apply the conditional formatting. To manage these rules, you should understvà the order in which these rules are evaluated, what happens when two or more rules conflict, how copying and pasting can affect rule evaluation, how khổng lồ change the order in which rules are evaluated, and when lớn stop rule evaluation.

Learn about conditional formatting rule precedence

You create, edit, delete, & view all conditional formatting rules in the workbook by using the Conditional Formatting Rules Manager dialog box. (On the Home tab, cliông xã Conditional Formatting, and then click Manage Rules.)

The Conditional Formatting Rules Manager dialog box appears.

When two or more conditional formatting rules apply, these rules are evaluated in order of precedence (top lớn bottom) by how they are listed in this dialog box.

Here"s an example that has expiration dates for ID badges. We want to mark badges that expire within 60 days but are not yet expired with a yellow background color, và expired badges with a red background color.

*

In this example, cells with employee ID numbers who have certification dates due to lớn expire within 60 days are formatted in yellow, và ID numbers of employees with an expired certification are formatted in red. The rules are shown in the following image.

*

The first rule (which, if True, sets cell background color to lớn red) tests a date value in column B against the current date (obtained by using the TODAY function in a formula). Assign the formula lớn the first data value in column B, which is B2. The formula for this rule is =B2

What happens when more than one conditional formatting rule evaluates to True

Sometimes you have sầu more than one conditional formatting rule that evaluates to True. Here"s how rules are applied, first when rules don"t conflict, & then when they vị conflict:

When rules don"t conflict For example, if one rule formats a cell with a bold phông & another rule formats the same cell with a red color, the cell is formatted with both a bold phông and a red color. Because there is no conflict between the two formats, both rules are applied.

When rules conflict For example, one rule sets a cell fonts color to red & another rule sets a cell fonts color to lớn green. Because the two rules are in conflict, only one can apply. The rule that is applied is the one that is higher in precedence (higher in the danh sách in the dialog box).

How pasting, filling, and the Format Painter affect conditional formatting rules

While editing your worksheet, you may copy and paste cell values that have sầu conditional formats, fill a range of cells with conditional formats, or use the Format Painter. These operations can affect conditional formatting rule precedence in the following way: a new conditional formatting rule based on the source cells is created for the destination cells.

If you copy & paste cell values that have sầu conditional formats khổng lồ a worksheet opened in another instance of Excel (another Excel.exe pháo process running at the same time on the computer), no conditional formatting rule is created in the other instance and the format is not copied to lớn that instance.

What happens when a conditional format và a manual format conflict

If a conditional formatting rule evaluates as True, it takes precedence over any existing manual format for the same selection. This means that if they conflict, the conditional formatting applies và the manual format does not. If you delete the conditional formatting rule, the manual formatting for the range of cells remains.

Manual formatting is not listed in the Conditional Formatting Rules Manager dialog box nor is it used to determine precedence.

Controlling when rule evaluation stops by using the Stop If True check box

For backwards compatibility with versions of Excel earlier than Excel 2007, you can select the Stop If True check box in the Manage Rules dialog box khổng lồ simulate how conditional formatting might appear in those earlier versions of Excel that bởi vì not nfnagradi.net more than three conditional formatting rules or multiple rules applied khổng lồ the same range.

For example, if you have more than three conditional formatting rules for a range of cells, & are working with a version of Excel earlier than Excel 2007, that version of Excel:

Evaluates only the first three rules.

Applies the first rule in precedence that is True.

Ignores rules lower in precedence if they are True.

The following table summarizes each possible condition for the first three rules:

If rule

Is

And if rule

Is

And if rule

Is

Then

One

True

Two

True or False

Three

True or False

Rule one is applied and rules two & three are ignored.

One

False

Two

True

Three

True or False

Rule two is applied và rule three is ignored.

One

False

Two

False

Three

True

Rule three is applied.

One

False

Two

False

Three

False

No rules are applied.

You can select or clear the Stop If True check box to change the mặc định behavior:

To evaluate only the first rule, select the Stop If True check box for the first rule.

To evaluate only the first & second rules, select the Stop If True check box for the second rule.

You can"t select or clear the Stop If True check box if the rule formats by using a data bar, color scale, or icon mix.

If you"d lượt thích to lớn watch a video showing how to manage conditional formatting rules, see Video: Manage conditional formatting.


The order in which conditional formatting rules are evaluated - their precedence - also reflects their relative sầu importance: the higher a rule is on the list of conditional formatting rules, the more important it is. This means that in cases where two conditional formatting rules conflict with each other, the rule that is higher on the menu is applied và the rule that is lower on the danh mục is not applied.

On the Home tab, in the Styles group, cliông chồng the arrow next to Conditional Formatting, và then click Manage Rules.

The Conditional Formatting Rules Manager dialog box appears.

The conditional formatting rules for the current selection are displayed, including the rule type, the format, the range of cells the rule applies to lớn, and the Stop If True setting.

If you don"t see the rule that you want, in the Show formatting rules for menu box, make sure that the right range of cells, worksheet, table, or PivotTable report is selected.

Select a rule. Only one rule can be selected at a time.

To move the selected rule up in precedence, click Move Up. To move sầu the selected rule down in precedence, clichồng Move Down.

*

Optionally, to stop rule evaluation at a specific rule, select the Stop If True check box.


Clear conditional formatting on a worksheet

On the Home tab, cliông chồng Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

Follow these steps if you have conditional formatting in a worksheet, and you need khổng lồ remove it.

For an entire worksheet

On the Home tab, click Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

In a range of cells

Select the cells that contain the conditional formatting.

Clichồng the Quiông xã Analysis Lens

*
button that appears khổng lồ the bottom right of the selected data.


Notes: Quiông chồng Analysis Lens will not be visible if:

All of the cells in the selected range are empty, or

There is an entry only in the upper-left cell of the selected range, with all of the other cells in the range being empty.


Cliông chồng Clear Format.

*

Find và remove sầu the same conditional formats throughout a worksheet

Cliông xã on a cell that has the conditional format that you want khổng lồ remove throughout the worksheet.

On the Home tab, cliông xã the arrow next khổng lồ Find & Select, and then cliông chồng Go To Special.

Click Conditional formats.

Click Same under Data validation. lớn select all of the cells that contain the same conditional formatting rules.

On the Home tab, clichồng Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.


Color scales are visual guides that help you understand data distribution và variation. There are two kinds of color scales—two-color scales & three-color scales.

A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and yellow color scale, you can specify that higher value cells have a more green color và lower value cells have a more yellow color.

A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have sầu a green color, middle value cells have a yellow color, & lower value cells have a red color.


Select the cells that you want to conditionally format.

Cliông chồng Home > Conditional Formatting > Color Scales và then select a color scale.


A data bar helps you see the value of a cell relative lớn other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher và lower numbers, especially with large amounts of data, such as top selling & bottom selling toys in a holiday sales report.

Select the cells that you want khổng lồ conditionally format.

Cliông xã HomeConditional Formatting > Data Bars & then select a data bar.


To more easily find specific cells within a range of cells, you can format those specific cells based on a comparison operator. For example, in an inventory worksheet sorted by categories, you can highlight the products with fewer than 10 items on h& in yellow. Or, in a retail store summary worksheet, you can identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal khổng lồ "SouthEast."

Select the cells that you want lớn conditionally format.

On the Home tab, in the Tables group, cliông xã Highlight Cell Rules.

Select the command you want, such as Between, Equal To Text that Contains, or A Date Occurring.

Enter the values you want lớn use, and then select a format (fill, text, or border color), và then cliông xã OK.

If you"d lượt thích to watch videos of these techniques, see Video: Conditionally format text và Video: Conditionally format dates.


You can find the highest và lowest values in a range of cells that are based on a cutoff value you specify. For example, you can find the top 5 selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department.

Select the cells that you want to conditionally format.

On the Home tab, in the Tables group, click Top/Bottom Rules.

Select the comm& you want, such as Top 10 items or Bottom 10 %.

Xem thêm: Đội Hình Dự Kiến Việt Nam Vs Thái Lan : Văn Hậu, Công Phượng Dự Bị

Enter the values you want to use, & then select a format (fill, text, or border color), & then cliông xã OK.


You can find values above sầu or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an
Chuyên mục: Công nghệ