Microsoft excel 2010 conditional formatting

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. 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. On the Home tab, in the Style group, click the arrow next to Conditional Formatting , click Data Bars , and then select a data bar icon.

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. To add a conditional format, click New Rule. Select a Minimum and Maximum Type. In this case, you do not enter a value for Minimum and Maximum. Format a percentage: Select Percent and then enter a value for Minimum and Maximum. Format a percentile Select Percentile and then enter a value for Minimum and Maximum. Use a percentile when you want to visualize a group of high values such as the top 20th percentile in one data bar proportion and 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 , and then enter a value for Minimum and Maximum. 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. 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. Tip: You can sort cells that have this format by their icon - just use the context menu.

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.

On the Home tab, in the Style group, click the arrow next to Conditional Formatting , click Icon Set , and then select an icon set.

Select an icon set. The default is 3 Traffic Lights Unrimmed. The number of icons and the default comparison operators and threshold values for each icon can vary for each icon set. You can adjust the comparison operators and threshold values. The default range of values for each icon are equal in size, but you can adjust these to fit your unique requirements.

Make sure that the thresholds are in a logical sequence of highest to lowest from top to bottom. Format a number, date, or time value: Select Number. Format a percentage: Select Percent. Format a percentile: Select Percentile. Use a percentile when you want to visualize a group of high values such as the top 20th percentile using a particular icon and low values such as the bottom 20th percentile using another icon, 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. To make the first icon represent lower values and the last icon represent higher values, select Reverse Icon Order. The size of the icon shown depends on the font size that is used in that cell. As the size of the font is increased, the size of the icon 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. Note: You cannot conditionally format fields in the Values area of a PivotTable report by text or by date, only by number. If you'd like to watch videos of these techniques, see Video: Conditionally format text and Video: Conditionally format dates. 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 or on other worksheets, and then by selecting Expand Dialog.

Under Select a Rule Type , click Format only cells that contain. Under Edit the Rule Description , in the Format only cells with list box, do one of the following:. Format by number, date, or time: Select Cell Value , select a comparison operator, and then enter a number, date, or time.

Format by text: Select Specific Text , choosing a comparison operator, and then enter text. For example, select Contains and then enter Silver , or select Starting with and then enter Tri.

Quotes are included in the search string, and you may use wildcard characters. The maximum length of a string is characters. Note: Wildcards? To see a video of this technique, see Video: Conditionally format text.

Format by date: Select Dates Occurring and then select a date comparison. To see a video of this technique, see Video: Conditionally format dates.

Format cells with blanks or no blanks: Select Blanks or No Blanks. A blank value is a cell that contains no data and 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. To specify a format, click Format. The Format Cells dialog box appears. 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. You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify.

Under Select a Rule Type , click Format only top or bottom ranked values. Valid values are 1 to Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.

By default, 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:. 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 the command you want, such as Above Average or Below Average. Under Select a Rule Type , click Format only values that are above or below average. Under Edit the Rule Description , in the Format values that are list box, do 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.

By default, the conditionally format is based on all visible values. The formats you select are displayed in the Preview box. 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 pale red color. Grade values that are found just once in the Grade column unique values are highlighted in a green color.

Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box. 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. Notes: If there's already a rule defined that you just want to work a bit differently, duplicate the rule and edit it. The duplicate rule then appears in the list.

If you don't see the options that you want, you can use a formula to determine which cells to 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 specify the formatting criteria.

For example, you may want to compare values in a selection 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. Your formula must return True or False 1 or 0 , but you can use conditional logic to string together a set of corresponding conditional formats, such as different colors for each of a small set of text values for example, product 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 cell references. For more information, see Create or change a cell reference and Switch between relative, absolute, and mixed references. Tip: If any cells contain a formula that returns an error, conditional formatting is not applied to those cells.

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 or other worksheets, and then by clicking Expand Dialog. Under Apply Rule To , to optionally change the scope for fields in the Values area of a PivotTable report, do the following:.

To scope by selection: Click Selected cells. Under Select a Rule Type , click Use a formula to determine which cells to format. Under Edit the Rule Description , in the Format values where this formula is true list box, enter a formula.

The following example shows the use of two conditional formatting rules. If the first rule doesn't apply, the second rule applies. If both the down payment and the monthly payments fit these requirements, cells B4 and B5 are formatted green. Second rule: if either the down payment or the monthly payment doesn't meet the buyer's budget, B4 and B5 are formatted red.

Change some values, such as the APR, the loan term, the down payment, and the purchase amount to see what happens with the conditionally formatted cells. Formula for first rule applies green color. Formula for second rule applies red color. A conditional format applied to every cell in this worksheet shades every other row in the range of cells with a blue cell color.

You can select all cells in a worksheet by clicking the square above row 1 and to the left of column A. The MOD function returns a remainder after a number the first argument is divided by divisor the second argument. The ROW function returns the current row number. Most Active Hubs Microsoft Teams. Security, Compliance and Identity. Microsoft Edge Insider. Azure Databases. Autonomous Systems. Education Sector. Microsoft Localization. Microsoft PnP.

Healthcare and Life Sciences. Internet of Things IoT. Enabling Remote Work. Small and Medium Business. Humans of IT. Green Tech. MVP Award Program. Video Hub Azure. In Excel , errors in the range would lead to conditional formatting not being applied to all the cells in the range. Users told us that this was frustrating. In Excel , conditional formatting silently ignores the errors and continues applying conditional formatting to the rest of the cells in the range.

This month, we're announcing some top requested features like tracking changes in Excel and live transcriptions in Teams, as well as some soon-to-be-favorites like meeting recaps, turning Word documents in presentations, and more. Since we introduced Microsoft to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected.

Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing….

Skip to main content Skip to main content.