Conditional Formatting in Excel: Basic to Advanced Guide
Last Updated :
24 May, 2025
When handling large datasets in Excel, it’s easy to lose sight of what matters without clear visual cues. Conditional formatting in Excel solves this by highlighting key information. In this guide, we’ll show you how to use conditional formatting to improve your spreadsheets with color scales, icon sets, and data bars, making your data clear and engaging.
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.
Follow the below steps to Use Conditional Formatting in Excel:
Prepare the Data Set
Enter the Data into the excel spreadsheet.
Prepare the Data Set1. Highlight Cells Based on Values
The Highlight Cell Rules feature allows you to format cells based on conditions like greater than, less than, between, or containing specific text. Follow the below steps to highlight cell rules in excel:
Step 1: Select the Column
Select the Column in which you want to apply conditional formatting. Here we have selected Column C(Salary).
Go to Home Tab, Click on Conditional Formatting, Select Highlight Cells Rules and Click on Greater Than.
Alternatively Press the Shortcut to open the Conditional Formatting Menu:
Go to Home Tab>> Click on Conditional Formatting >>Select " Highlight Cell Rules">> Click on Greater ThanStep 3: Enter the Value and Press OK
Enter the value you want to highlight, choose a formatting style, and click OK.
Enter the Value and Press OKStep 4: Preview Results
Press Enter and Preview Results. Cells with salaries greater than $60,000 will be highlighted in red.
Preview Results2. Clear Rules
If you need to remove Conditional Formatting, use the Clear Rules option.
Step 1: Click on Clear Rules
Go to Home > Conditional Formatting > Clear Rules and Select Choose Clear Rules from Selected Cells (or Entire Sheet).
Go to Home Tab>> Click on "Conditional formatting">> Click on Clear Rules>> Click on Clear Rules from Entire SheetStep 2: Preview Results
The conditional formatting will be removed from the selected range.
Preview Results3. Top/Bottom Rules – Identifying Outliers
Top/Bottom Rules highlight the highest or lowest values in a dataset.
Step 1: Select the Cells
Select the Performance Score column (Column D) D2:D9.
Select the CellsClick Home > Conditional Formatting > Top/Bottom Rules > Top 3.
Select the Formatting RuleStep 3: Preview Results
This will highlight the top 3 employees with the highest performance scores.
Preview ResultsUsing formulas in Conditional Formatting allows you to create custom rules beyond the built-in options.
Example: Highlight Employees in the Sales Department
Step 1: Select the Dataset
Select the Data Range (A1:E9).
Select the DataStep 2: Select the New Rule
- Go to Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- Enter the formula
=$B2="Sales"
- Click Format, choose a fill color (e.g., Green), and click OK.
Click on Home >>Select Conditional Formatting >> Click on New Rule >> Click on "Use a Formula to determine which cells to format">>Enter the FormulaStep 3: Preview Results
All employees in the Sales department will be highlighted.
Preview Results5. Using Color Scales to Visualize Data
Color Scales use gradient colors to represent high, medium, and low values.
Example: Apply a Color Scale to Bonus Percentage
Step 1: Select the Bonus Column
Select the Bonus column (E2:E9
).
Step 2: Select the Color Scales and Choose the Color Scales
Go to Home Tab, Select the Conditional Formatting, Click on the Color Scales and Choose a Red-Yellow-Green scale (or any other preferred scale).
- Green: Highest bonus percentage.
- Yellow: Medium values.
- Red: Lowest bonus percentage.
Go to Home Ta >> Click on Conditional Formatting >> Click on Color ScalesStep 3: Preview Results
Choose the Color Scale Pattern and Preview Results.
Preview Results6. Highlighting Blank or Duplicate Cells
Sometimes, blank cells indicate missing data. Conditional Formatting can help identify them easily.
Example: Highlight Empty Cells in the Dataset
Step 1: Select the entire dataset
Select the Entire Dataset. (In the below example we have selected A2:E9)
Select the entire datasetStep 2: Select New Rule
Click Home > Conditional Formatting > New Rule.
Go to Home Tab >>Click on Conditional Formatting >>Select New Rule- In the drop-down, choose Blanks.
- Click Format, pick a color (e.g., Green), and click OK.
Go to Home Tab >>Select Conditional Formatting>>Select New Rule>> Select "Format Only Cells that Contain">>Select "Blanks">> Click on Format >> Choose ColorStep 3: Preview Results
Choose the Color and Preview Results.
Preview ResultsYou can easily modify an existing Conditional Formatting Rule in Excel by following these steps:
Step 1: Select the Cells
- Click on any cell that has a conditional formatting rule applied.
Step 2: Open the Rules Manager
- Go to the Home tab, click Conditional Formatting, and select Manage Rules.
Go to Home Home Tab >> Manage RulesStep 3: Choose the Rule to Edit
- In the Rules Manager dialog box, choose the rule you want to modify and click Edit Rule
Select your Desired RuleStep 4: Make Changes & Apply
- Update the settings in the Edit Formatting Rule window as needed, then click OK to apply the changes.
How to Remove Conditional Formatting in Excel ( 3 Methods Explained)
You can easily remove conditional formatting in Excel using two simple methods:
This method allows you to remove conditional formatting from selected cells or the entire sheet.
Step 1: Select the Cells
- Highlight the cell range that contains the conditional formatting you want to remove.
- If you want to remove formatting from the entire sheet, you can skip selecting specific cells.
- Click on the Home tab in the ribbon.
- In the Styles group, click Conditional Formatting.
Step 3: Clear the Rules
- Choose Clear Rules from the drop-down menu.
- Select either:
- Clear Rules from Selected Cells – to remove formatting only from the selected range.
- Clear Rules from Entire Sheet – to remove all conditional formatting from the worksheet.
You can also use the keyboard Shortcut
Press Alt + H + E + F on your keyboard.
Select "Clear Rules" >> Clear Rules from Selected RulesThis method is useful when you want to edit or remove specific rules without affecting others.
Step 1: Select the Cells
- Click on any cell within the range that has conditional formatting applied.
- If you’re unsure, selecting the entire sheet will display all rules in the next step.
Step 2: Open the Rules Manager
- Go to the Home tab, click Conditional Formatting, and select Manage Rules from the drop-down menu.
Step 3: Choose the Rules to Delete
- The Conditional Formatting Rules Manager window will appear.
- From the Show Formatting Rules For drop-down, select either the current selection or the entire sheet.
- Select the rule you want to remove and click Delete Rule.
Click "OK"Removing conditional formatting in Excel can be a quick and easy task with the right keyboard shortcuts. If you want to remove all the conditional formatting rules from a selected range of cells, you can follow these steps:
Step 1: Select the Cell range
Select the range of cells from which you want to remove the conditional formatting.
Step 2: Press Keys
Press Alt + H + E + F on your keyboard.
This shortcut sequence will open the Clear Rules menu, allowing you to remove conditional formatting from the selected cells. Mastering this shortcut can save you time and streamline your data management process, ensuring your spreadsheets remain clean and organized.
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.
- 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.
- Data Bars and Colors Scales: Represent data visually using data bars or color scales to create intuitive maps, making patterns and trends more apparent.
- Icons Sets: Apply Icon Sets to cells, by using symbols such as arrows or checkmarks, to quickly assess data against preset conditions.
- 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.
- Cell Reference in Conditional Formatting: You can apply Conditional Formatting dynamically based on values in other cells using Cell References.
- Managing Rules: You can efficiently Manage and organize your conditional formatting rules through the "Conditional Formatting Rules Manager".
Conclusion
By mastering conditional formatting in Excel, you can significantly improve the clarity and readability of your spreadsheets. Whether you're highlighting top-performing sales, tracking deadlines, or simply looking to make data trends stand out, Excel conditional formatting offers a wide range of options to suit your needs. With these techniques, you’ll be able to create more dynamic and interactive reports that help you and others quickly interpret important information.
Similar Reads
How to use Conditional Formatting in Excel?
Microsoft Excel is a software that allows users to store or analyze the data in a proper systematic manner. It uses spreadsheets to organize numbers and data with formulas and functions. MS Excel has a collection of columns and rows that form a table. Generally, alphabetical letters are assigned to
4 min read
How to Apply Conditional Formatting Based On VLookup in Excel?
VLOOKUP is an Excel function to lookup data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Conditional Formatting Based on Vlookup: 1. Using the Vlookup formula to compare values in 2 different tables and highlighting those
3 min read
Adding Conditional Formatting to Excel Using Python Openpyxl
Adding conditional formatting using openpyxl is an easy and straightforward process. While working with Excel files, conditional formatting is useful for the visualization of trends in the data, highlighting crucial data points, and making data more meaningful and understandable. In this article, we
5 min read
How to Apply Conditional Formatting in a Pivot Table in Excel
One of the most useful ways to customize the pivot table formatting is using Conditional Formats. Conditional formatting rules can be applied to Pivot tables just like they can be applied to normal data ranges. So by using conditional formatting, we can highlight the cells with a certain color depen
8 min read
Conditional Formatting in Power BI
Conditional formatting visually enhances and customize our data's appearance based on certain conditions or rules. It helps us highlight important data by applying colors, style and symbols based on rules or conditions. It makes our reports more readable, insightful and visually attractive.Types of
5 min read
Instant Data Analysis in Advanced Excel
In order to execute complicated data analysis, reporting, and visualization tasks in Microsoft Excel, users must employ a collection of tools, functions, and features called "Advanced Excel." Pivot tables, lookup features, data validation, macros, and other features are among its features. Data anal
5 min read
How to Add a Conditional Column in Power Query in Excel?
We may use Power Query to generate new columns whose values depend on one or more criteria that have been applied to existing columns in your database. The condition imposed on an existing column in the data model serves as the foundation for Power BI's conditional column. On the Add column tab, in
2 min read
Advanced Excel - Discontinued Features
Imagine Excel as a superhero for numbers. We're exploring the cool stuff it used to do but doesn't anymore â like old superpowers it left behind. Join us to find out why and see how Excel keeps getting better! It's like a quick tour of Excel's history â fun and easy! There are many features in Excel
5 min read
Data Formatting in Excel
Data formatting in Excel is the key to transforming raw numbers into clear, professional, and actionable insights. From customizing dates and currencies to applying conditional formatting for quick analysis, mastering these techniques saves time and enhances your spreadsheetsâ impact. This guide wil
3 min read
How to Calculate Conditional Probability in Excel?
Conditional probability is calculating the probability of an event provided the event associated is already done. In other words, If we have to find the conditional probability of two events suppose A and B, then we are finding the probability of event A happening given that event B has already occu
3 min read