Custom conditional formatting in Excel

Custom conditional formatting allows you to take more control of your formatting rather than using the automatic formatting provided by Excel.

In custom conditional formatting in Excel you might want to find cells that are Greater Than a defined range, Less Than or Between a defined range.

custom conditional formatting

Duplicate Values: Cells containing duplicate values can be easily highlighted using custom conditional formatting in Excel.

HOW TO FORMAT NUMBERS GREATER THAN OR LESS THAN A DEFINED RANGE

 Select the range of cells. In the Home tab under the Styles section, click on Conditional Formatting, select Highlight Cell Rules, click on Greater Than.

custom conditional formatting in Excel

A dialogue box would pop up indicating the number range you want formatted. Input your preferred range and click OK. The cells within the defined range will be highlighted.

Greater Than conditional formatting in Excel

When you want to format cells that are Less Than or Between a defined range, same process as above must be applied.

HOW TO FORMAT CELLS WITH DUPLICATE VALUES

In the Home tab under the Styles section, click on Conditional Formatting, select Highlight Cell Rules, click on Duplicate Values.

Finding duplicate values in Excel

The Duplicate Values dialogue box that pops up allows you to format the cell color you want your result to cells to look like.

Custom formatting duplicate values in Excel

HOW TO APPLY A CONDITIONAL FORMATTING OVER ANOTHER.

This a color scales conditional formatting, let’s say you want the numbers Great Than 900 to be formatted in Darker Blue with a White Font.

color scales conditional formatting in Excel

Re-select the range of cells maintaining the current format, in the Home tab under the Styles Section select Greater Than.

Applying two different conditional formatting

Input the number range then select Custom Format to choose your preferred Fill and Font.

Applying two conditional formatting

Below is the result.

Applying different conditional formatting in Excel

HOW TO CREATE A NEW CONDITIONAL FORMATTING

Creating a New Rule Conditional Formatting allows you to defined how your formatted cells look like.

Go to Conditional Formatting, click on New Rule. Take note to have your cell range selected.

how to create new rule conditional formatting

In the New Rule dialogue box that pops up. Select a Rule Type, you can also select the Format Style based on color preference, also select different color for the minimum, midpoint and maximum values.

Creating a new rule conditional formatting

Click OK to apply your custom formatting. Here is a sample of the result below.

Watch this video below for some custom conditional formatting tips and tricks.

 

New Rule conditional formatting in Excel

HOW TO MANAGE NEWLY CREATED RULES.

To manage your newly created rule in custom conditional formatting in Excel. Select Manage Rules under Conditional Formatting.

Manage Rules

Conditional formatting rule manager

Conditional formatting Rule Manager shows you all the applied rule and in what order they were applied. In the manager you can Delete Rule, Edit Rule or add New Rules.

Download file for practice: Conditional Formatting

Custom conditional formatting is a really great powerful tool if you want to do some data analysis in Excel. Here is a visual explanation. Thanks, sharing is caring.