Buy Now
Conditional Formatting is a great basic tool to highlight data in your Excel Spreadsheets. Learn more about them in this article.

Using Conditional Formatting In Excel

conditional formatting excel exceltips exceltools Jan 25, 2022

How to Apply Conditional Formatting in Excel

We will all, at some point, encounter spreadsheets that are probably too big for Microsoft Excel. This makes looking for significant values or outliers incredibly hard unless you’re Peter Parker with his Spidey Sense. We might not have genetically mutated spiders, but we do have conditional formatting. Like charts and spark lines, conditional formatting provides another way to visualize data and make worksheets easier to understand.

 

Understanding Conditional Formatting

Conditional formatting allows you to automatically apply formatting such as colors, icons, and data bars to one or more cells based on the cell's value. To do this, you will need to create a conditional formatting rule. For example, one of the conditional formatting rules might be: If the value is less than $2000, the cell colors are in red. By applying this rule, you will quickly see cells containing values under $2000.

 

How To Create a Conditional Formatting Rule:

We have a worksheet with sales data in our example, and we'd like to see which salespeople are meeting their monthly sales goals. The sales target is $4000 per month, so we'll create a conditional format rule for any cells with a value above 4000.

  1. Select the cells required for the conditional formatting rule.

 

  1. On the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
  2. Hover over the desired conditional formatting type, then select the desired rule from the list that appears. In our example, we want to highlight cells that are over $4000. 
  3. The dialog box appears. Enter the desired value(s) in the empty field. In our example, we'll enter 4000 as our values.
  4. Select a formatting style from the drop-down list. For example, we'll choose Green Fill with Dark Green Text in our sample and click OK.      
  5. Conditional formatting will be applied to the selected cells. So, it's easy to see which salespeople hit the $4,000 sales target per month in our example.

 

You can apply multiple conditional formatting rules to a range of cells or a worksheet, allowing you to visualize different indicators and patterns in your data. You can also purchase pre-made models provided by simple sheets.

 

Conditional Preformat

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

  • Data bars are horizontal bars that are added to each cell, much like a bar graph.
  • Hue allows changing the color of each cell based on its value. Each color scale uses a shade of two or three colors. For example, in the green-yellow-red color scale, the highest values are green, the middle values are yellow, and the lowest are red.
  • Icon sets add a specific icon to each cell based on its value.

 

Using a preset conditional format:

  1. Select the cells required for the conditional formatting rule.
  2. Click the conditional formatting command. A drop-down menu will appear.
  3. Hover over the setting you selected earlier, then choose a setting style from the menu that appears.

 

  1. 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 over Clear Rules, and choose the rules you want to clear. We'll select Clear Rules from the Entire Sheet to remove all conditional formatting from the worksheet in this example.

 

  1. Conditional formatting will be removed.

Click Manage Rules to edit or delete specific rules. This is especially useful if you apply multiple rules to the worksheet.

 

Conditional Formatting allows you to step up your spreadsheet game, not only aesthetically, but also increases your spreadsheets’ functionality making your data more readable and therefore more useful.

If you want some great examples on how to use conditional formatting, be sure to check out Simple Sheets’ great Excel templates such as the Fishbone Diagram, COVID-19 Business Health Tracker, and the Excel 2022 Calendar Template!

 

 

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.