How to Use Conditional Formatting Rules in Excel

Found This Helpful

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.
(Courtesy of Microsoft)
Step 1:

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

Open your file and click Home.
Courtesy of Microsoft
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.

Select table cells and click Condition Formatting.
Courtesy of Microsoft
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....

Choose an option for Highlight Cells Rules.
Courtesy of Microsoft
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 a value and color plan for highlighting.
Courtesy of Microsoft
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.

Select table cells and click Conditional Formatting.
Courtesy of Microsoft
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.

Choose an option from Top/Bottom Rules.
Courtesy of Microsoft
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 a value for percentage and color plan.
Courtesy of Microsoft
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.

Select a group of table cells and click the Conditional Formatting button.
Courtesy of Microsoft
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.

Choose one Data Bars method from Gradient Fill.
Courtesy of Microsoft
Step 3:

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

Click More Rules.
Courtesy of Microsoft
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 a new Data Bars rule and click OK.
Courtesy of Microsoft
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.

Pick one Color Scales method.
Courtesy of Microsoft
Step 2:

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

Click More Rules.
Courtesy of Microsoft
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 a new Color Scales rule and click OK.
Courtesy of Microsoft
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.

Choose an Icon Sets method.
Courtesy of Microsoft
Step 2:

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

Click More Rules.
Courtesy of Microsoft
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.

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

References

Comments

Resources

Featured

Watch Video

#eHowHacks: Open a Wine Bottle With a Shoe