Home

Learning Hub

Articles

A Definitive Guide to Conditional Formatting in Excel

Conditional formatting in Excel serves as a transformative tool for your data, allowing you to add a touch of magic to your spreadsheets. Think of a situation where your data not only resides in cells but also narrates a story using various colours, icons, and dynamic visuals. You inject vitality into your data through conditional formatting, effortlessly spotlighting trends, anomalies, and essential insights. 

Whether it’s monitoring sales performance, tracking project milestones, or analysing survey results, conditional formatting empowers you to identify patterns instantly, providing unparalleled clarity for decision-making. Bid farewell to endless rows and columns of numbers and welcome a vibrant tapestry of information that communicates volumes with a single glance. Enter the realm of Excel’s conditional formatting, where data transcends its numerical confines, evolving into a captivating tale of triumph and exploration.

What is Conditional Formatting?

Conditional Formatting in Excel is a beneficial tool for improving data visualisation and comprehension in spreadsheets. Imagine a worksheet with thousands of rows of data; identifying patterns and trends in raw data alone is challenging. However, by applying formatting rules to cells, patterns become more apparent, facilitating easier data analysis. With a variety of preset formatting options and customisable features, conditional formatting offers both flexibility and efficiency in data analysis and presentation.

Basics of Conditional Formatting 

Conditional formatting encompasses four primary elements: if-then commands, preset conditions, custom conditions, and the application of multiple conditions. Here is the detail on how to utilise these components to establish and enforce rules in your Excel spreadsheets below:

• If-Then Logic: Conditional formatting rules rely on basic if-then logic: if certain criteria are met, specific formatting is applied. No need to hard-code logic; Excel offers built-in parameters for selecting conditions.

• Preset Conditions: Excel offers a vast array of preset rules covering most functions beginners need. We’ll introduce some popular ones shortly.

• Custom Conditions: Users can craft custom rules for scenarios requiring adjustments to preset conditions, possibly incorporating Excel formulas.

• Applying Multiple Conditions: Multiple rules can be applied to a single cell or cell range, but understanding rule hierarchy is crucial. We’ll guide you through managing stacked rules.

Employing conditional formatting in Excel is a straightforward method to keep teams updated on data, highlighting important dates, tasks, budget constraints, and more. When utilised effectively, it boosts productivity by minimising manual data scrutiny, aiding trend identification, and enabling focus on significant decisions.

Read About: 15 Excel Tips You should Definitely Master in 2024

How to Use Conditional Formatting in Excel?

Conditional formatting enhances data visibility. Discover the process of establishing and adjusting conditional formatting rules in Excel. Here, a rule is created to identify employees who sold over 140 units last year. 

Select a rangeYou have two options for selecting your preferred range. If your data is limited, simply highlight the range on the spreadsheet. For larger datasets, it’s more efficient to input the range manually. Here’s how.
• Navigate to the Home tab on your ribbon and click on Conditional Formatting. 

 

• Then, choose Manage Rules. In the Conditional Formatting panel, locate and click the New Rule icon (represented by a plus sign). 
• Specify your range in the Apply to range field, and click Done.
  Create the Rule
After selecting your range, proceed to create your rule, represented by your if/then statement. If the Conditional Formatting panel is open, you can formulate your condition within the Rule Type section. If not, navigate to the Home tab on your ribbon and click on Conditional Formatting. The most common rules are categorised under Highlight Cell Rules and Top/Bottom Rules. Although Excel may not group the rule options similarly in both the Conditional Formatting panel and menu, you can access the same rules in either section. For this tutorial segment, we’ll craft the rule using the Conditional Formatting panel. To highlight cells indicating more than 140 units sold last year, utilise the Greater than rule. 
• In the Rule Type section, retain the default selection (Highlight cells with). Then, select “Cell value” in the second field, which automatically generates two additional fields. 
• Keep the default selection (“Greater than”) in the third field. 
• Enter “140” in the fourth field. Remember, if you apply multiple conditional formatting rules to a specific range.

 

Excel will execute them in the order they were created. However, once a cell meets a rule’s criteria, subsequent rules will not supersede it.

1. Select a Formatting Style
   

   By default, Excel applies a light red fill and dark red text to cells meeting your rule. However, you have the flexibility to customise the formatting to your preference. 

    

   In the “Format with” section, click the down caret (⋁) to select from five other preset styles or create a custom style using the formatting tools. Since we aim to highlight positive outcomes, let’s change the fill colour to light green and the text colour to black, then click “Done.” 

    

   Excel will now highlight cells meeting your rule with a light green fill and black text, making it easy to identify instances where sales exceeded 140 units last year. 

    

   Cells meeting your rule will be highlighted with a light green fill and black text in Excel. This allows easy identification of instances where Keesha, Christina, Rogelio, and Vijay sold over 140 units last year.

    

   To remove a conditional formatting rule, simply hover over it in the Conditional Formatting panel and click the Delete icon, resembling a garbage can.

    

   Suppose you’ve established multiple rules and wish to delete them all at once. Adjacent to “Manage Rules,” select the “Delete all rules” icon resembling a garbage can.

Text that Contains Rule 

This condition will emphasise cells containing specific text, which can be handy if you wish to track the frequency of a particular name, for instance. In our demonstration sheet, we can employ this criterion to visually distinguish employees from Tampa.

A Date Occurring Rule

This condition will emphasise cells containing dates from today, yesterday, last week, last month, etc. It’s a dynamic criterion, implying that cells meeting the condition will adjust relative to the current date.

In the instance provided, a rule was established to highlight cells with dates occurring in the past month. Given the rule’s creation at the end of August 2023, Excel highlighted cells F11 to F14, featuring the date 07/31/2023. Had the same rule been created in, say, April 2023, only cell F4 (03/31/2023) would have been highlighted.

Duplicate Values Rule

This rule identifies duplicate values within the specified range.

If your aim is to identify unique values (non-duplicated data), it’s just as straightforward. Generate a new rule in the Conditional Formatting panel. Under Rule Type, select “Unique values.” 

In the demonstration below, Unique values rule is used to highlight any distinct sales targets within cells D3 and D14.

Top/Bottom Rules

The Top/Bottom Rules simplify the process of highlighting top and bottom performers within a selected range without the need for complex formulas.

Customisation options are not available for Above Average and Below Average rules, as you can’t specify the average. However, rules like top and bottom 10 items or top and bottom 10% can be adjusted to suit your requirements.

For instance, you can modify the Top 10 Items rule to highlight the top one, five, or even 100 items. In our demonstration sheet, we can utilise this rule to highlight the highest-performing employees based on the top three who sold the most units last year.

Conditional Formatting Styles 

There are three other Conditional Formatting options, which are used to help visualise your data: Data bars, Color scales, and Icon sets. 

Data Bars 

Data bars offer the capability to incorporate dynamic bar graphs into each cell’s background. These bars are filled proportionally concerning other cells within the specified range. For instance, in the demo sheet, a data bar was implemented for units sold last year (cells C3 to C14). The maximum number of units sold within this range is 200. Hence, cells containing this value display a full-width bar graph. Conversely, cells containing the minimum value of 80 exhibits a bar graph spanning less than half the cell’s width.

Color Scales 

With Color Scales, the burden of selecting formatting styles is alleviated. Upon specifying the rule range and type, Color Scales autonomously employs a colour-based hierarchy system, eliminating the need for manual intervention.

Various scale options are accessible, with the Green – Yellow – Red Color Scale being the most prevalent. Upon application, illustrated in the example below, it assigns a green background to cells with the highest values and a red background to those with the lowest values. Intermediate values are represented by shades of orange and yellow, creating a visual spectrum.

Icon Sets

Icon sets offer dynamic functionality akin to Color Scales. Users can select from various icons, such as arrows, shapes, and indicators, which are subsequently incorporated into each cell based on its numeric value. Notably, these icons automatically adjust if cell values are modified.

For instance, in the example below, the 3 Triangles icon set was employed for cells C3 to C14. Cells featuring the highest values showcase an upward-facing green arrow, while those with the lowest values display a downward-facing red arrow. Cells with values in between exhibit a light-orange bar, illustrating a graded progression.

To Wrap Up

Conditional formatting within Excel proves to be a potent instrument, enriching data visualisation and analysis by facilitating the identification of patterns, trends, and anomalies within spreadsheets. Through the application of formatting rules contingent on specific conditions, users expedite the highlighting of pertinent information and glean valuable insights from their data. This functionality holds immense value for professionals spanning diverse industries, encompassing finance, marketing, human resources, and beyond.

Embracing conditional formatting stands to notably enhance decision-making processes and streamline workflows, thereby fostering enhanced efficiency and effectiveness in business operations. For those seeking to fortify their expertise in data analysis and business intelligence, we recommend considering enrollment in our Accelerator Programme in Business Analytics and Data Science. Seize the opportunity to propel your career forward and unlock fresh avenues in the realm of data-driven decision-making.