How to Use Conditional Formatting in Excel 2021

Edited 4 weeks ago by ExtremeHow Editorial Team

Microsoft OfficeExcelVisualizationWindowsMacAdvancedProductivity

Microsoft Excel is a powerful tool that allows us to process and analyze data with various functions and features. Among these, conditional formatting is a standout feature that helps visualize data, highlight cells, and identify trends or issues within a dataset. This tool can improve the way we interpret and represent our data by applying specific formatting styles such as color scales, data bars, and icon sets to cells that meet certain conditions.

This document provides a comprehensive guide on how to use conditional formatting in Excel 2021. We'll cover everything from basic concepts to advanced applications, ensuring that even users new to Excel can effectively leverage this feature. Whether you're working with a simple spreadsheet or a complex dataset, understanding this tool will greatly enhance your data manipulation skills.

Introduction to conditional formatting

Conditional formatting is a feature in Excel that enables you to automatically apply cell formatting — such as text color, fill color, and more — to a range of cells based on the contents of those cells. For example, you can use conditional formatting to highlight the top 10% of values in a list with a particular color or to color cells with duplicate values red.

At its core, conditional formatting works by allowing you to define rules or conditions. These rules are evaluated in the context of the data to decide what the formatting should be. Conditions are set using comparison operators, functions, and formulas that return TRUE or FALSE, determining whether specific formatting is applied.

Accessing conditional formatting in Excel 2021

To access conditional formatting in Excel 2021, first open your Excel workbook and select the range of cells you want to format. After selecting the range, go to the Home tab on the ribbon. Here, you will see the Conditional Formatting dropdown in the Styles group.

Types of conditional formatting

Excel 2021 provides a variety of conditional formatting styles, allowing users to tailor their data presentation in a variety of ways. Some of the common types are explored below:

1. Highlight Cells Rules:

   These are probably the simplest types of conditions you can set. For example, you can highlight cells that are greater than a specific value or contain specific text. You can apply these rules by going to Conditional Formatting > Highlight Cells Rules.

2. Top/Bottom Rule:

   These rules help highlight the top or bottom X number, percentage, average in a dataset. This application is ideal for quickly ranking data. Find it under Conditional Formatting > Top/Bottom Rules.

3. Data Bar:

   Data bars add a bar graph-like appearance directly within cells. These are useful for providing an immediate visual impression of the data shape, accessible under Conditional Formatting > Data Bars.

4. Color scale:

   This option uses a gradient of two or more colors to display the data range. This is useful for visualizing data distribution and intensity. You can apply a color scale through Conditional Formatting > Color Scale.

5. Icon sets:

   Icon sets allow you to use symbols, such as check marks or traffic lights, to annotate your cells based on their values. Find these options under Conditional Formatting > Icon Sets.

Creating custom rules

In addition to the built-in rules, Excel 2021 allows creating custom rules, which lets you design conditions according to your needs. Here's how you can create a custom rule:

1. Select the cells you want to format.
2. Go to Home > Conditional Formatting and select New Rule....
3. In the New Formatting Rule dialog box, choose the rule type:
   • Format cells that contain: Select this to activate custom conditions about cell values.
   • Use a formula to determine which cells to format: This is useful when you need more control; here, you can enter a formula that returns a TRUE/FALSE value.
4. Set the format and position as needed.
5. Click OK to apply the rule.

Example of using formula-based conditional formatting

Let's look at an example where you want to highlight a row based on a specific condition using a formula:

Suppose we have a table containing marks of students and we want to highlight entire rows of students who scored less than 50 marks.

1. Select the range, for example, A2:D10 where A2 contains the first score.
2. Go to Home > Conditional Formatting > New Rule...
3. Select Use a formula to determine which cells to format.
4. Enter the formula: =B2 < 50. Replace B2 in your selection with the cell that contains the score.
5. Click Format..., then choose your formatting, such as fill color.
6. Click OK to close the Format Cells dialog and click OK again to set the rule.

Editing and deleting conditional formats

Over time, you may need to modify or delete rules. Here's how you can manage existing conditional formatting rules in Excel 2021:

• Edit the conditional formatting rule:

  To edit an existing rule, go to Home > Conditional Formatting > Manage Rules.... This opens the Rules Manager, which shows all the rules for the current selection, worksheet, or workbook.

  Here you can select the rule to edit by clicking Edit Rule. Modify the rule details in the dialog box, then save the changes by clicking OK.

• Delete a conditional formatting rule:

  In the Rules Manager, select a rule and click Delete Rule to delete it. To remove all rules from a worksheet or selection, go to Conditional Formatting > Clear Rules and choose the appropriate option.

Advanced technologies

Advanced users can use conditional formatting for complex datasets by incorporating functions and Excel features such as references, named ranges, and more. Below are some advanced tips:

Using named ranges

Named ranges provide a clear method to refer to specific sets of cells in formulas, making conditional formats easier to understand and maintain. Especially when formulas rely on constants used in many places, naming those constants centralizes the change and highlights their purpose.

Dynamic conditional formatting with the INDIRECT function

The INDIRECT function can be used to create dynamic references in formulas. This allows for more complex situations where you may want to dynamically reference a cell based on input or calculations.

Common problems and troubleshooting

While powerful, conditional formatting can sometimes behave unexpectedly, especially if rules overlap or interact with spreadsheet behavior in non-obvious ways. Here are some common problems and solutions:

• Conflicts between rules: Sometimes rules may overlap, leading to unexpected results. Check the rule order and precedence in the Rule Manager.
• Incorrect formula references: Double-check relative versus absolute references in formulas, as errors can produce strange results.
• No visual change: Make sure the data type of the cells matches the data type expected in your rules (for example, text versus numbers).

Conclusion

Mastering conditional formatting can help you make your Excel workbooks more interactive, practical, and user-friendly. From visual cues to automatic highlighting of important information, this feature is integral for those who frequently work with data analysis and reporting. As you have seen through the various examples and tips, the creativity and functionality provided by conditional formatting is enormous. Practicing these concepts will enable you to present your data more effectively, ensuring clarity and focusing on important information.

  Share This Article

If you find anything wrong with the article content, you can request an update