How to Apply Conditional Formatting in Microsoft Excel

Philip Wiest, Guest Blogger

Conditional formatting in Microsoft® Excel® is an essential skill to learn, so you easily review and analyze data, detect any issues and identify patterns and trends. Conditional formatting allows you to easily highlight cells and visualize data by using bars, color scales and icons that correspond to your data.

When you use conditionally formatting, it will change the appearance of your cells based on conditions that you specify. If the conditions are true, the cell range will be formatted. If the conditions are false, the cell range is not formatted.

Many conditions are built in to Excel, but you can also create your own. They can be applied to a range of cells, an Excel table or even a PivotTable report.

Apply conditional formatting using these quick analysis tool steps:

Highlight your data and the Quick Analysis button in Excel will automatically appear.
Click the button or press Ctrl+Q on your keyboard.
Select the Formatting tab on the pop-up and formatting option that you want to use (If you don’t know which format you’d like to use, hover each formatting option to see a live preview). Depending on the data in your cells, you will see different options. Text formatting may include Duplicate, Unique, Equal To and Clear Format. Cells with numbers or text and numbers will have options like Data Bars, Colors, Icon Set, Greater Than, Top 10% and Clear Format.
If a dialog box with Text that Contains appears, use the formatting option you want to apply and click OK.

Learn more Excel tips and tricks when you register now for: Getting the Most From Microsoft Excel.

Quick route: applying Excel conditional formatting for a two-color scale

On some Excel sheets, you may want a visual guide to help you better see the variations in your data. A two-color scale can be used to compare a range of cells using different shades of color to represent different values. In a red and yellow color scale, for example, you can specify that higher value cells have shades of red and lower value cells will have shades of yellow.

Select a range of cells, table or PivotTable.
On the Home tab on the ribbon, select Conditional Formatting and click Color Scales.
Choose a two-color scale. The top color will represent your higher values, and the bottom will be lower values.

In a PivotTable report, you can change the method of scoping for fields in the Values area using the Formatting Options button next to a PivotTable field that with conditional formatting.

Quick route: applying Excel conditional formatting using an icon set

When you are using numbers, you may want to use an icon set to annotate and classify data. The data will be categorized in three to five areas and separated by a threshold value. Each icon used will represents a range of values. In the arrows icon set, the green up arrow will be your highest values, the yellow sideways arrow will be mid-range values, and the red down arrow will be your lower values.

Select the cells that you want to conditionally format.
On the Home tab, click Conditional Formatting in the Style group.
Click Icon Set, and then select your icon.
To show icons only for cells that meet at condition, you can hide icons by selecting No Cell Icon from the dropdown list next to your icon.

In a PivotTable report, you can change the method of scoping for fields in the Values area using the Formatting Options button next to a PivotTable field that with conditional formatting.

Ready to learn more? Check out some of SkillPath’s live virtual training programs, on-demand video training or get it all with our unlimited eLearning platform.

Philip Wiest

Guest Blogger

Philip Wiest is an expert software trainer, computer analyst and database consultant who knows both the intricacies of computer systems and the ways today’s professionals need to use these essential business tools. Using his experience in Microsoft Excel and Microsoft Office, as well as Windows and the Internet, Phil uses a special mix of tactful guidance and distilled observation so his audiences learn and retain the critical keystrokes, application combinations and creative processes that save time and simplify computer use.

How to Apply Conditional Formatting in Microsoft Excel

Apply conditional formatting using these quick analysis tool steps:

Highlight your data and the Quick Analysis button in Excel will automatically appear.

Click the button or press Ctrl+Q on your keyboard.

If a dialog box with Text that Contains appears, use the formatting option you want to apply and click OK.

Quick route: applying Excel conditional formatting for a two-color scale

Select a range of cells, table or PivotTable.

On the Home tab on the ribbon, select Conditional Formatting and click Color Scales.

Choose a two-color scale. The top color will represent your higher values, and the bottom will be lower values.

Quick route: applying Excel conditional formatting using an icon set

Select the cells that you want to conditionally format.

On the Home tab, click Conditional Formatting in the Style group.

Click Icon Set, and then select your icon.

To show icons only for cells that meet at condition, you can hide icons by selecting No Cell Icon from the dropdown list next to your icon.