How to Use Conditional Formatting Rules in Excel

Conditional formatting rules in Excel 2013 help you visualize patterns and trends within a selected group of data and explore and identify the highlighted data. General rules such as Highlight Cells Rules and Top/Bottom Rules highlight a specified type of data within the group. Other conditional formatting rules such as Data Bars, Color Scales and Icon Sets give you options to view trends and detect critical issues within the group data.

...
Easily analyze your data using Excel Conditional Formatting Rules.
credit: Courtesy of Microsoft

Set Up Highlight Cells Rules

Step

Open an Excel file and navigate to the Home tab in the ribbon section.

...
Open your file and click Home.
credit: Courtesy of Microsoft
...
Select table cells and click Condition Formatting.
credit: Courtesy of Microsoft

Step

Select a group of table cells that you want to apply conditional formatting to and click the Conditional Formatting selection in the Styles group.

...
Choose an option for Highlight Cells Rules.
credit: Courtesy of Microsoft

Step

Choose Highlight Cells Rules from the drop-down list. Select from options such as "Greater Than...," "Less Than..." and "Between..." to highlight the entries that satisfy a condition you enter. For example, choose Greater Than....

...
Set a value and color plan for highlighting.
credit: Courtesy of Microsoft

Step

Set a value and color plan in the Greater Than dialog box and click OK to confirm the settings. For example, set 60 with Light Red Fill With Dark Red Text to mark the entries that are greater than 60. Excel gives you a preview of the effect before you click OK.

Set Up Top/Bottom Rules

Step

Select a range of table cells you want to apply conditional formatting to and click the Conditional Formatting button in the Styles group.

...
Select table cells and click Conditional Formatting.
credit: Courtesy of Microsoft
...
Choose an option from Top/Bottom Rules.
credit: Courtesy of Microsoft

Step

Choose Top/Bottom Rules from the drop-down list. Select one of the options, which include "Top 10 Items...," "Top 10%..." and "Bottom 10 Items..." to highlight a specific group of table cells. For example, choose Top 10%... to mark the top 10 percent of the data within the selected data.

...
Set a value for percentage and color plan.
credit: Courtesy of Microsoft

Step

Set a percentage of data and color plan for the highlights in the dialog box and click OK. For example, choose 20 and Green Fill With Dark Green Text to mark the top 20 percent of the data. Excel shows you a preview of the results before you click OK.

Set Up Data Bars

Step

Select a range of table cells you want to apply conditional formatting to and click the Conditional Formatting button in the Styles group.

...
Select a group of table cells and click the Conditional Formatting button.
credit: Courtesy of Microsoft
...
Choose one Data Bars method from Gradient Fill.
credit: Courtesy of Microsoft

Step

Choose Data Bars from the drop-down list. Choose one Data Bars method from the Gradient Fill list. Hover your mouse over each Data Bars method to preview the effect. The Data Bars choice represents the data in a cell by a graphical bar, with higher values assigned longer bars than lower values. You can use Data Bars to quickly compare values visually.

...
Click More Rules.
credit: Courtesy of Microsoft

Step

Click More Rules... if you want to set up a new conditional formatting rule.

...
Set up a new Data Bars rule and click OK.
credit: Courtesy of Microsoft

Step

Select a Rule Type from the list and edit the Rule Description in the New Formatting Rule dialog box and click OK to finish. For example, you can choose to manipulate only specific cells, choose a Format Style or change the Bar Appearance with different color plans. Preview the Data Bars in the bottom of the dialog box.

Set Up Color Scales

Step

Select a range of table cells for conditional formatting and click the Conditional Formatting button in the Styles group. Choose Color Scales from the drop-down list. Choose one Color Scales method from the list to highlight the data. You can hover your mouse over each method to preview the effect. For example, the chosen Color Scales method uses green on the highest values and red on the lowest values. You can tell the differences between the data by viewing the color scales.

...
Pick one Color Scales method.
credit: Courtesy of Microsoft
...
Click More Rules.
credit: Courtesy of Microsoft

Step

Click More Rules... if you want to set up a new conditional formatting rule.

...
Set up a new Color Scales rule and click OK.
credit: Courtesy of Microsoft

Step

Select a Rule Type and edit the Rule Description in the New Formatting Rule dialog box. Click OK to finish. For example, you can choose specific cells to manipulate, select a Format Style and change the color plan. You can preview the new rule before you click OK.

Set Up Icon Sets

Step

Select a group of table cells for conditional formatting and click the Conditional Formatting button in the Styles group. Choose Icon Sets from the drop-down list. Choose one Icon Sets method from the Directional list to highlight the data. Hover your mouse over each method to preview the effect. For example, the chosen Icon Sets method puts a green upper arrow on the highest values and a red down arrow on the lowest values. You can tell the differences between the data by noting the different colored directional icon sets.

...
Choose an Icon Sets method.
credit: Courtesy of Microsoft
...
Click More Rules.
credit: Courtesy of Microsoft

Step

Click More Rules if you want to set up a new Icon Sets rule.

...
Set up a new Icon Sets rule and click OK.
credit: Courtesy of Microsoft

Step

Select a Rule Type and edit the Rule Description in the New Formatting Rule dialog box. Click OK to finish. For example, you can choose specific cells to manipulate, select a Format Style or change the Value and Type that are applied. You can preview the new rule before you click OK.