Excel : Conditional Formatting

19 Nov 2017
Conditional Formatting in Excel

Using "Format" feature in Excel, helps us determine how it displays a value. Changing the format of the cell is very user friendly. You just need to select the cell in question, right click and select format in the drop down. We can now select the desired option. But conditional formatting is more flexible, applying specified formatting only when certain conditions are met. 

Conditional formatting allows you to automatically apply formatting—such as colorsicons, and data bars—to one or more cells based on the cell value. To do this, you'll need to create a conditional formatting rule.

Conditional Formatting 

Conditional Formatting Basics

Before we walk you through creating and applying conditional formatting, you should understand the basics of how it works. The following structural aspects of Excel conditional formatting will guide how you create and apply rules:

  • If-Then Logic: All conditional formatting rules are based on simple if-then logic: if X criteria is true, then Y formatting will be applied (this is often written as p → q, or if p is true, then apply q). You won’t have to hard-code any logic, though - Excel and other spreadsheet apps have built-in parameters so you can simply select the conditions you want the rules to meet. Advanced users can also apply the program’s built-in formulas to logic rules.
  • Preset Conditions: Excel has a huge library of preset rules encompassing nearly all functions that beginner users will want to apply. We’ll familiarize you with several of the most popular ones in the next section. 
  • Custom Conditions: For situations where you want to manipulate a preset condition, you can create your own rules. If appropriate, you can use Excel formulas in the rules you write.
  • Applying Multiple Conditions: You can apply multiple rules to a single cell or range of cells. However, be aware of rule hierarchy and precedence - we’ll show you how to manage stacked rules in the walkthrough.

Overall, applying conditional formatting is an easy way to keep you and your team members up to date with your data - calling visual attention to important dates and deadlines, tasks and assignments, budget constraints, and anything else you might want to highlight. When applied correctly, conditional formatting will make you more productive by reducing time spent manually combing data and making it easier to identify trends, so you can focus on the big decisions.

Execute the following steps:

  1. Select the desired cells for the conditional formatting rule.
  2. From the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
  3. Hover the mouse over the desired conditional formatting type, then select the desired rule from the menu that appears
  4.  Conditional Formatting menu

 

  1.  A dialog box will appear. Enter the desired value(s) into the blank field. In our example, we'll enter 4000 as our value.
  2. Select a formatting style from the drop-down menu. In our example, we'll choose Green Fill with Dark Green Text, then click OK.

    Conditional Formatting create dialog

  3. The conditional formatting will be applied to the selected cells. In our example, it's easy to see which salespeople reached the $4000 sales goal for each month.

     

You can apply multiple conditional formatting rules to a cell range or worksheet, allowing you to visualize different trends and patterns in your data.

Conditional Formatting Create Result

Conditional formatting presets

Excel has several predefined styles—or presets—you can use to quickly apply conditional formatting to your data. They are grouped into three categories:

  • Data Bars are horizontal bars added to each cell, much like a bar graph.

    Conditional Formatting Data Bars

  • Color Scales change the color of each cell based on its value. Each color scale uses a two- or three-color gradient. For example, in the Green-Yellow-Red color scale, the highest values are green, the average values are yellow, and the lowest values are red.

    Conditional Formatting Color scale

  • Icon Sets add a specific icon to each cell based on its value.

    Conditional Formatting icon sets

To use preset conditional formatting:

  1. Select the desired cells for the conditional formatting rule.
  2. Click the Conditional Formatting command. A drop-down menu will appear.
  3. Hover the mouse over the desired preset, then choose a preset style from the menu that appears.

    Conditional formatting Preset menu

  4. The conditional formatting will be applied to the selected cells.

     

Removing conditional formatting

To remove conditional formatting:

  1. Click the Conditional Formatting command. A drop-down menu will appear.
  2. Hover the mouse over Clear Rules, and choose which rules you want to clear. In our example, we'll select Clear Rules from Entire Sheet to remove all conditional formatting from the worksheet.

    Conditional Formatting Remove Menu

  3. The conditional formatting will be removed.

Please watch this section to see more on Excel functions and other Advanced Features.