How to Use Conditional Formatting Rules in Excel

By Zicheng Ren

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.

Set Up Highlight Cells Rules

Step 1

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

Step 2

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

Step 3

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....**

Step 4

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 1

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

Step 2

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.

Step 3

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 1

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

Step 2

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.

Step 3

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

Step 4

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 1

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.

Step 2

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

Step 3

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 1

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.

Step 2

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

Step 3

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.