Open In App

Conditional Formatting in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

Excel Conditional Formatting is a handy feature that helps you visualize data and understand your spreadsheets better. Consider a worksheet that has tens of thousands of rows of data. Simply by looking at the raw data – patterns and trends would be very difficult to spot. By applying formatting rules to cells, anyone can spot patterns easily and changes in your data. With a range of preset formatting options and customization choices, conditional formatting provides flexibility as well as efficiency in analyzing and data presentation.

What is Conditional Formatting in Excel

Conditional Formatting is a feature in an Excel spreadsheet. It is used to maintain the status of the result easily. It is most often used as color-based formatting to highlight, emphasize, or differentiate among data and information stored in an Excel spreadsheet.

When it comes to applying alternative forms to data that fit particular criteria, Excel conditional formatting is a highly useful feature. It can make it easier for you to draw attention to the key details in your spreadsheets and quickly identify differences in cell values.

Types of Conditional Formatting Visualisation

You can use preset rules, including Color scales, Data Bars, Icon Sets, Sort filters, etc. to conditionally format your data, or you can construct your own rules that specify when and how the selected cells should be highlighted.

Data Bars

Similar to a bar graph, data bars are horizontal bars that are added to each cell

Data Bars Excel

Data Bars

Color scales

Alter each cell’s color depending on its value. A two- or three-color gradient is used for each color scale

Color scales Excel

Color scales

Icon Sets

Based on the value of each cell, icon sets assign a distinct icon to each cell.

Icon Sets Excel

Icon Sets

Where is Conditional Formatting in Excel

Navigate to the Home tab, access the Style group, and select the dropdown arrow adjacent to Conditional Formatting. Then, opt for Top/Bottom Rules. It’s important to note that you can find Conditional Formatting in the same location across Excel versions from 2010 to Excel 365.

Quick Tip: Go to Home tab > Select Conditional formatting > Select Rule

Conditional Formatting in Excel

Conditional Formatting in Excel

How to Use Conditional Formatting in Excel

Step 1: Select the Cells

Select the cells you want to format in the Spreadsheet

Step 2: Click on Conditional Formatting

Click on Conditional Formatting, On the Home tab, in the Styles group

Step 3: From a Set of Preset Rules, Pick the Required One

From a set of preset rules, pick the one that best serves your needs

Using Conditional Formatting In Excel

Using Conditional Formatting In Excel

Step 4: Enter the Value and Select the Chosen Format from the Drop-Down List

Enter the value in the box on the left of the dialogue box and select the chosen format from the drop-down list on the right (with Default Light Red Fill with Dark Red Text)

Conditional Formatting in Excel Tutorial

Using Conditional Formatting In Excel

Likewise, you have the flexibility to choose a different rule type that better suits your data. For instance, you can opt for:

  • Greater than or equal to
  • Between two values
  • Text that contains specific words or characters
  • Date occurring in a certain range
  • Duplicate values

How to Use a Preset Rule with Custom Formatting

You can select any other color for the background, text, or borders of the cells if none of the standard layouts appeals to you. This is how:

Step 1: Pick Custom Format

Pick Custom Format, In the preset rule dialogue box, from the drop-down list on the right 

Step 2: Edit the Format Cells and Click Ok

Change between the Font, Border, and Fill tabs in the Format Cells dialogue window to select the preferred font style, border style, and background color, respectively, click OK

custom formatting excel

Custom formatting

Step 3: Click OK to Apply the Custom Formatting of your Choice

custom formatting in excel

Custom formatting

How to Create a New Conditional Formatting Rule in Excel

You can create a new Conditional Formatting Rule in Excel from scratch. To do so, follow these steps:

Step 1: Select the Cell or Cells you Want to Format

Step 2: Navigate to the Home Tab and Select Conditional Formatting and Click New Rule

New Conditional Formatting Rule in Excel

New Conditional Formatting Rule in Excel

Step 3: Select the Rule Type and Click Ok

Now select the rule type, in the New Formatting Rule dialog box

Create Conditional Formatting Rule Excel

New Conditional Formatting Rule in Excel

Step 4: Click the Format button, Choose the Color, and Click Ok

Screenshot-(236)

Click on Format> choose Color

Step 5: Preview Result

New-Rule

New Rule Applied

How to Edit Conditional Formatting Rules in Excel

To make any changes in an already existing Formatting Rule in Excel, follow these steps:

Step 1: Select the Cells

Select any cell to which a rule already applies.

Step 2: Navigate to the Home Tab and Click on Manage Rules under Conditional Formatting

Editing Conditional Formatting Rules in Excel

Editing Conditional Formatting Rules in Excel

Step 3: Select the Rule you Want to Edit

This will open the Rules Manager dialog box, select the rule you want to modify, then Click on Edit Rule. 

Editing Conditional Formatting Rules Excel

Editing Conditional Formatting Rules in Excel

Step 4: Make the Changes and Click Ok

Make the required changes in the Edit Formatting Rule dialogue window, Click on Ok 

Excel Conditional Formatting Formula Examples

Here are some Scenario-Based Examples of Conditional Formatting in Excel:

How to Find Duplicates in Excel

Step 1: Select the Cells in which you want to Find Duplicates

Select the cells where you want to identify duplicate values. For example: select the column TOT as shown in the figure.

excel conditional formatting examples

Step 2: Navigate to the Home Tab and Click on Cell Formatting

Click on the Conditional Formatting and click on new rules. Now a window appears, select ‘format only unique and duplicate values’ and click on the format.

conditional formatting in excel example

Step 3: Choose the Color

Select the Fill option in the tab and choose the background color and click ok.

identifying duplicates in excel

Step 4: Preview Duplicates

Now the duplicate values as shown in the figure.

duplicate conditional formatting rules in excel

How to Highlight Cells with Value Greater or Less than a Number in Excel

Follow the below steps for highlighting cells with a value greater or less than a given number:

Step 1: Select the Cells

Select the cells that you want to highlight with Greater or less than a number.

conditional formatting highlight cells rules

Step 2: Navigate to the Home Tab and Click on Conditional Formatting

Click on the conditional formatting and click on new rules. Now select “Format all cells based on their values“. In the ‘Minimum’ and choose the value you want to identify the lesser values and choose the color. Similarly, In the Maximum and choose the value you want to identify the lesser values and choose the color. And click ok. 

New-Formatting-Rule-(1)

New Formatting Rule

Step 3: Preview Result

In the below figure, the values with green color indicate greater values and the values with red color indicate fewer values.

Preview-the-Result-1-(1)

Preview the Result

How to Highlight Top or Bottom values in Excel

Step 1: Select the Cells where you want to Highlight Top and Bottom N items

To highlight the top and/or the bottom N items select the cells where you want to highlight top and bottom N items .

Step 2: Navigate to the Home tab and Click on the Conditional Formatting

Step 3: Choose Top/Bottom Cells

Click on the conditional formatting->Top/bottom cells->Top 10 Items. Choose a value in format cells that rank at the top and choose the format and click ok.

Screenshot-(237)

Click on Conditional Formatting > Top/Bottom Rules > Top 10

Step 4: Preview the Result

In the below figure, the red color indicates the top 5 values in rank.

Preview-the-Result-(1)

Preview the Result

How to Copy Conditional Formatting to Another Cell in Excel

You won’t need to start over when applying a conditional format you’ve already established to different data. To transfer the current conditional formatting rule(s) to another data collection, just utilize Format Painter.

Step 1: Choose the cell whose Conditional Formatting you want to Copy

Step 2: Click on Format Painter under Home

Click on the first cell in the range you want to format, then drag the paintbrush down to the last cell to paste the copied formatting

Copy Excel Conditional Formatting

Copy Excel Conditional Formatting

How to Remove Conditional Formatting in Excel

There are two easy ways to delete conditional formatting rules

Step 1: Select the BestChoice after choosing the desired cell range

Step 2: Click Conditional Formatting then Select Clear Rules

Deleting conditional formatting rules

Deleting conditional formatting rules

Remove Conditional Formatting Rules using Conditional Formatting Rules Manager

Step 1: Select the Rule

Step 2: Navigate to the Home Button and Select Conditional Formatting

Step 3: Select Conditional Formatting Rules Manager and Select Delete Rule

Under the Conditional Formatting Rules Manager

delete conditional formatting excel

Deleting conditional formatting rules

Keyboard Shortcut to Remove Conditional Formatting

To remove Conditional formatting rules from the selected range of cells use ALT + H + L + C + S

Note: To use the keyboard shortcut provided above, begin by highlighting the cells from which you wish to remove the Conditional Formatting. Afterward, press the designated keys.

Excel Conditional Formatting Best Uses

  1. Highlighting Top and Bottom Values: You can use Conditional Formatting to highlight the highest and lowest values in a Dataset so that you can easily identify significant data points.
  2. Data Bars and Colors Scales: Represent data visually using data bars or color scales to create intuitive maps, making patterns and trends more apparent.
  3. Icons Sets: Apply Icon Sets to cells, by using symbols such as arrows or checkmarks, to quickly assess data against preset conditions.

Advanced Conditional Formatting Techniques

  1. Formulas in Conditional Formatting: You can use custom formulas to create complex conditions for formatting. This can help you for more precise control over the formatting rules.
  2. Cell Reference in Conditional Formatting: You can apply Conditional Formatting dynamically based on values in other cells using Cell References.
  3. Managing Rules: You can efficiently Manage and organize your conditional formatting rules through the “Conditional Formatting Rules Manager”.

FAQs

Here are some of the most frequently asked questions on Excel Conditional Formatting

What are the 3 conditional formatting options?

Three categories have been established for Conditional Formatting in Excel.

  • Similar to a bar graph, Data bars are horizontal bars that are added to each cell
  • Color scales alter each cell’s color depending on its value. A two- or three-color gradient is used for each color scale
  • Icon Sets, based on the value of each cell, icon sets assign a distinct icon to each cell

How to Copy Conditional Formatting to other cells in Excel?

Yes, we can copy Conditional Formatting to other cells in Excel. There are multiple ways to copy the conditional formatting to other cells, such as – 

  • Simple copy-paste
  • Copy and paste conditional formatting only
  • Using the format painter

How do I remove Conditional Formatting from cells in Excel?

To remove the selected range conditional formatting, please follow the below-mentioned steps:

  • Select the range that you want to remove the conditional formatting from
  • Click Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells

Can I use icons or data bars in Conditional Formatting?

Yes, you can use Icons or Data bars in Conditional Formatting in Excel. To use either of these, follow the below steps:

  •  Select the range that you want to apply the conditional formatting to
  • Click on Conditional Formatting on the Home Tab
  • Point to Data Bars, and choose from gradient fill or solid fill or Point to Icon sets and choose your desired icons

How to apply Conditional Formatting based on another cell’s value in Excel?

You must use a formula if you wish to format an entire row depending on the value of a single cell or apply conditional formatting based on another cell. 



Last Updated : 19 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads