1. How Do I View Conditional Formatting In Excel

Excel for Office 365 Excel Online Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets. A conditional format changes the appearance of a cell range based on conditions (or criteria). If the condition is true, the cell range is formatted based on that condition; if the condition is false, the cell range is not formatted based on that condition.You can sort and filter by format, including cell background color and font color, whether you have manually or conditionally formatted the cells. The following example shows conditional formatting that uses cell background colors, a 3 arrow icon set, and data bars. Note: When you create a conditional format, you can reference only other cells on the same worksheet or, in certain cases, cells on worksheets in the same currently open workbook.

Note: In the data hierarchy, children do not inherit conditional formatting from the parent, and 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, and by value field.The default method of scoping fields in the Values area is by selection. Note: Quick Analysis is not available in Excel 2010 and previous versions.Use the Quick Analysis buttonto apply selected conditional formatting to the selected data. The Quick Analysis button appears automatically when you select data in your spreadsheet.Select the data in your spreadsheet.

Quiero poner una columna en verde si el valor de otra es 1, y en rojo si es 0. Y quisiera que se colorearan segun su valor, por ejemplo, el menor en verde, el que le sigue en amarillo, el que le sigue en rojo y asi sucesivamente. HOLA COMO HAGO PARA COLOREAR CELDAS CON TAN SOLO SELECCIONARLAS.

The Quick Analysis button appears on the lower-right corner of the selection.Click the Quick Analysis button, or press Ctrl+Q.Then, on the Formatting tab, move your mouse over the different options to see a Live Preview on your data, and then click on a formatting option. Notes:.The formatting options that appear in the Formatting tab will depend on the data you have selected. If your selection contains only text, then the available options are Text, Duplicate, Unique, Equal To, and Clear. When the selection contains only numbers, or both text and numbers, then the options are Data Bars, Colors, Icon Sets, Greater, Top 10%, and Clear.Live preview 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 and you select Duplicate, the live preview will not work.If the Text that Contains dialog box appears, enter the formatting option you want to apply and click OK.

How Do I View Conditional Formatting In Excel

If your conditional formatting needs are more complex, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values to 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.On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.The Conditional Formatting Rules Manager dialog box appears.Do one of the following:.To add a conditional format, click New Rule. The New 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 list box.Optionally, change the range of cells by clicking Collapse Dialogin the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or other worksheets, and then by selecting Expand Dialog.Select the rule, and then click Edit rule. Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells.

To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value other than an error value.Quick 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 to Conditional Formatting, and then click Color Scales.Select a two-color scale.Hover over the color scale icons to see which icon is a two-color scale. The top color represents higher values, and 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 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, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.Do one of the following:.To add a conditional format, click New Rule. The New 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 list box.Optionally, change the range of cells by clicking Collapse Dialogin 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, and then click Edit rule. Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value that you specify (such as 0, or 'N/A') instead of an error value.Quick 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 to Conditional Formatting, and then click Color Scales.Select a three-color scale. Notes:.You can set minimum, midpoint, and 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, and Maximum percent.In many cases, the default Midpoint value of 50 percent works best, but you can adjust this to fit unique 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, click 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 to 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 and lower numbers, especially with large amounts of data, such as top selling and 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, and stretches to 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 those cells. Notes:.Make sure that the Minimum value is less than the Maximum value.You can choose a different type for Minimum and Maximum. For example, you can choose a Minimum number and a Maximum percent.To choose a Minimum and Maximum color scale, click Bar Color.If you want to choose additional colors or create a custom color, click More Colors. The bar color you select is shown in the Preview box.To show only the data bar and not the value in the cell, select Show Bar Only.To apply a solid border to data bars, select Solid Border in the Border list box and choose a color for the border.To choose between a solid bar and a gradiated bar, choose Solid Fill or Gradient Fill in the Fill list box.To format negative bars, click Negative Value and Axis and then, in the Negative Value and Axis Settings dialog box, choose options for the negative bar fill and border colors. You can choose position settings and 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 list box. This is set to Context by default, but you can choose between a left-to-right and a right-to-left direction, depending on how you want to present your data. Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.The example shown here works with several examples of conditional formatting icon sets.You can choose to show icons only for cells that meet a condition; for example, displaying a warning icon for those cells that fall below a critical value and no icons for those that exceed it.

To do this, you hide icons by selecting No Cell Icon from the icon drop-down list next to the icon when you are setting conditions. You can also create your own combination of icon sets; for example, a green 'symbol' check mark, a yellow 'traffic light', and a red 'flag.' Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells. 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 hand 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 to 'SouthEast.'

The examples shown here work with examples of built-in conditional formatting criteria, such as Greater Than, and Top%. This formats cities with a population greater than 2,000,000 with a green background and average high temperatures in the top 30% with orange. You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify.

You can find values above or below an average or standard deviation in a range of cells. Note: You can't conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.In the example shown here, conditional formatting is used on the Instructor column to find instructors that are teaching more than one class (duplicate instructor names are highlighted in a rose color). Grade values that are found just once in the Grade column (unique values) are highlighted in a green color.Quick 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 to Conditional Formatting, and then click Highlight Cells Rules.Select Duplicate Values.Enter the values you want to use, and then select a format.Advanced 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 to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.Do one of the following:.To add a conditional format, click New Rule.

The New Formatting Rule dialog box appears.To change a conditional format, do 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 Dialogin 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, and then click Edit rule. The Edit Formatting Rule dialog box appears.Under Select a Rule Type, click Format only unique or duplicate values.Under Edit the Rule Description, in the Format all list box, select unique or duplicate.Click Format to display the Format Cells dialog box.Select the number, font, border, or fill format you want to 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. If your worksheet has one or more cells with a conditional format, you can quickly locate the cells so that you can copy, change, or delete the conditional formats. Color scales are visual guides that help you understand data distribution and variation.

There are two kinds of color scales—two-color scales and 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 and 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 a green color, middle value cells have a yellow color, and lower value cells have a red color.Select one or more cells in a range, table, or a PivotTable.Click Home Tables Conditional Formatting Color Scales and then select a color scale. A data bar helps you see the value of a cell relative to 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 and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.Select one or more cells in a range, table, or a PivotTable.Click Home Tables Conditional Formatting Data Bars and 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 hand 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 to 'SouthEast.' .Select one or more cells in a range, table, or a PivotTable.On the Home tab, in the Tables group, click 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 to use, and then select a format (fill, text, or border color), and then click OK. 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.Select one or more cells in a range, table, or a PivotTable.On the Home tab, in the Tables group, click Top/Bottom Rules.Select the command you want, such as Top 10 items or Bottom 10%.Enter the values you want to use, and then select a format (fill, text, or border color), and then click OK. 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 quality rating.Select one or more cells in a range, table, or a PivotTable.On the Home tab, in the Tables group, click Top/Bottom Rules.Select the command you want, such as Above Average or Below Average.Enter the values you want to use, and then select a format (fill, text, or border color), and then click OK. You can clear conditional formatting in selected cells or the entire worksheet.To clear conditional formatting in selected cells:.Select the cells in the worksheet.Click the Home tab Conditional Formatting Clear Rules Clear Rules from Selected Cells.To clear conditional formatting in the entire worksheet, click the Home tab Conditional Formatting Clear Rules Clear Rules from Entire Sheet.Do you have a specific function question?Need more help?You can always ask an expert in the, get support in the, or suggest a new feature or improvement on.See Also.