Apply conditional formatting to the selected cells with a value greater than 10
Let's say you have a spreadsheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw data. Excel gives us several tools that will make this task easier. One of these tools is called conditional formatting. With conditional formatting, you can apply formatting to one or more cells based on the value of the cell. You can highlight interesting or unusual cell values, and visualize the data using formatting such as colors, icons, and data bars. Show
In this lesson, you'll learn how to apply, modify, and remove conditional formatting rules. Conditional formattingConditional formatting applies one or more rules to any cells you want. An example of a rule might be If the value is greater than 5000, color the cell yellow. By applying this rule to the cells in a worksheet, you'll be able to see at a glance which cells are more than 5000. There are also rules that can mark the top 10 items, all cells that are below the average, cells that are within a certain date range, and many more. Optional: You can download this example for extra practice. To create a conditional formatting rule:
If you want, you can apply more than one rule to your cells. Conditional formatting presetsExcel has several presets you can use to quickly apply conditional formatting to your cells. They are grouped into three categories:
To use preset conditional formatting:
To remove conditional formatting rules:
You can edit or delete individual rules by clicking the Conditional Formatting command and selecting Manage Rules. This is especially useful if you have applied multiple rules to the cells. Use Excel conditional formatting to highlight worksheet cells automatically, based on rules (conditions) that you set. Make cells a different colour, or change border, font style, or number format. Rules can be based on a selected cell's contents, or values in a different cell. See the simple examples below. What is Excel Conditional Formatting?With Excel conditional formatting, you can highlight one or cells, when specific conditions (rules) are met. For example, you can set a conditional formatting rules for a column where the cells contain a number:
That makes it easy to spot the months with low sales. How Does Conditional Formatting Work?There are two steps for making conditional formatting work:
Usually, the rules are based on the contents of a cell -- the formatted cell, OR a different cell. Here are examples of those 2 types of rules:
What Cell Formatting Can You Change?You can apply the following formats with your rules:
What Cell Formatting Cannot Change?In Conditional formatting, you cannot apply formatting options that would affect:
For example, you cannot apply formatting to change any of the following:
Border OptionsOn the Borders tab for normal cell formatting, there are 6 thin border options, and 7 thicker border options. Those options are shown at the left, in the image below. On the Borders tab for Conditional Formatting, only the 6 thin border options are available. Those options are shown at the right in the image below. Example 1: Cell Color Based on AmountOn this worksheet, there is a list of six months, and the units sold per month. To quickly see the months with poor sales, we'll use conditional formatting to highlight numbers below 50, with light red cell fill colour. How to Highlight Low Numbers in ListTo highlight the low numbers (below 50), follow these steps:
The Less Than dialog box opens, with
On the worksheet, that formatting has been applied to some of the selected cells. Choose Settings for Less ThanInstead of leaving the default settings in the Less Than dialog box, make the following changes:
Conditional Formatting on Selected CellsOn the worksheet, the conditional formatting rule was applied to the selected cells
Test the Conditional FormattingTo test the conditional formatting rule, make the following changes:
Less Than Dialog Default SettingsWhen the Less Than dialog box opens, there is a number in the first box. To get that default number, here's what Excel does:
For the default cell formatting, Excel shows the first item from the drop down list of options. Example 2: Highlight With Top 10 RuleA quick way to highlight cells is with the conditional formatting Top 10 rule. Despite its name of Top 10, you can adjust the rule to highlight any number of items In this example, there are 6 months of sales amounts, for 4 different regions.
We'll highlight the top 5 sales amounts, using light green fill colour, without including any of the totals. Here are the steps to highlight the top 5 amounts:
The Top 10 Items dialog box opens, and
On the worksheet, that formatting has been applied to the 10 highest amounts in the selected cells. NOTE: If there are duplicate numbers, you might see more than 10 numbers highlighted Set the NumberInstead of leaving the default settings in the Top 10 Items dialog box, make the following changes:
Conditional Formatting on Selected CellsOn the worksheet, the conditional formatting rule was applied to the selected cells
Change Conditional Formatting RuleAfter you create a conditional formatting rule, you might want to change it. For example, in the previous example, a Top/Bottom rule was created, to highlight the top 5 amounts. Now, we'd like to change that Top/Bottom rule, and highlight only the top 3 amounts. Existing Conditional Formatting RuleIf you want to change an existing Top/Bottom rule, or any other type of conditional formatting rule, do not choose the option again, from the list of Conditional Formatting options, in the drop down menu.
To successfully change a conditional formatting rule, follow the steps below. Those steps show how to find the existing rule in the Conditional Formatting Rules Manager, and change it there. Correctly Change Existing RuleIn this example, a Top/Bottom rule was set up in cell B2:E7 (in the previous section) To successfully change that existing conditional formatting rule, follow the steps below:
Conditional Formatting Rules ManagerThe Conditional Formatting Rules Manager opens, showing a list of rules for the selected cells.
To change one of the conditional formatting rules:
In the bottom section, Edit the Rule Description, you can make changes to the rule and its formatting
Delete Conditional Formatting RuleIf you create a conditional formatting rule, you might want to delete that rule later. For example, you no longer want to highlight the top amounts. There are two options for deleting rule, and the steps are shown below:
Delete ALL RulesIf you want to remove all the conditional formatting rules from selected cells, or the entire sheet, follow these steps.
WARNING: The rules will be cleared immediately, with no verification message. Tip:If you change your mind about clearing the rules, you can use the Undo command (Ctrl+Z), before doing anything else. Delete Specific RulesTo delete a specific rule, follow these steps, to select rules in the Conditional Formatting Rules Manager:
Conditional Formatting Rules ManagerThe Conditional Formatting Rules Manager dialog box opens, showing a list of rules for the selected cells. NOTE: To see other rules, you can select from the drop down list at the top of the dialog box. To delete one of the conditional formatting rules:
Video: Use 2 Rules to Format CellsIn this example, you'll set conditional formats that colour a cell, based on its own value. There will be 2 conditional formatting rules for the selected cells:
This video shows the steps, and the written steps are below the video. List on WorksheetThis example is shown in the video (above), and you can download the Excel file, to get the sample data. Or, set up your own sample data, by following these steps.
Type Rule Amounts on WorksheetWhen you're setting limits for your conditional formatting rules, those limits are easier to see and maintain, if you put them on a worksheet.
Follow these steps to set up the high and low amounts for the formatting rules:
The cells with values greater than 75 are now coloured green. Apply 2nd RuleTo colour the low values in red fill, you can apply a second conditional formatting rule to the cells.
The cells with values greater than 75 are now coloured green, and cells less than 50 are red. Fix Conditional Formatting Extra RulesAfter you set up conditional formatting rules, you might see a problem with new rules being created automatically -- you could end up with hundreds of extra rules! This usually happens because a conditional formatting rule refers to a cell in a different row.
You can see how to clean up those extra rules, manually or with a macro, on the Fix Conditional Formatting Extra Rules page. Conditional Formatting IconsIn Excel 2007 and later versions, you can use built-in icon sets for conditional formatting. These icon set are a quick way to highlight the high, medium and low values in a list of numbers. Many of the icon sets use shapes in the traditional colours of green, red and yellow. A few of the icon sets are in other colours, such as all grey, or black and white. In this example, there is a list of month names, with quantities. We'll apply a conditional formatting icon set to highlight the high, medium and low quantities in that list. To apply conditional formatting icons, follow these steps:
On the worksheet, that formatting has been applied to the selected cells.
Customize Your Own Icon SetIn Excel 2010 and later versions, you can customize the Conditional Formatting Icons, to some extent. Here’s how to do that, and build your own custom set of icons, by choosing from the individual built-in icons. For this example, the list shows month names, and the number of errors recorded each month.
Select Different IconsHere's how to change the icons, to create a custom icon set for this data:
The Conditional Formatting Rules Manager opens, showing a list of rules for the selected cells.
After you've selected your custom icons, complete these steps:
On the worksheet, the custom icon set has been applied to the selected cells.
Conditional Formatting Data BarsIf cells contain numbers, you can add conditional formatting data bars, to show the differences among the amounts. Watch this short video to see how to set up data bars in a cell, and the written instructions are on the Conditional Formatting Data Bars page. Pivot Table Conditional FormattingTo use conditional formatting in an Excel pivot table, you'll need to do a couple of extra steps. Otherwise, the conditional formatting will not adjust automatically, if you change the pivot table layout. Watch this video to see the steps for applying conditional formatting to pivot tables cells. Then the video shows how to adjust the rule, so new cells are correctly formatted if the pivot table layout changes. The written instructions are on the Pivot Table Conditional Formatting page. Get the Sample FileConditional Formatting Basics: Click here to get a zipped sample Conditional Formatting file with the examples from this tutorial. The zipped workbook is in xlsx format, and there are no macros in the file. Pivot Table Conditional Formatting: To see the pivot tables example, download the Pivot Table Conditional Formatting workbook. The zipped Excel file is in xlsx file format, and does not contain any macros. More Examples: For more Conditional Formatting rules and advanced examples, go to the Conditional Formatting Examples page. |