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.
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.
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.
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.
The Duplicate Values dialogue box that pops up allows you to format the cell color you want your result to cells to look like.
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.
Re-select the range of cells maintaining the current format, in the Home tab under the Styles Section select Greater Than.
Input the number range then select Custom Format to choose your preferred Fill and Font.
Below is the result.
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.
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.
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.
HOW TO MANAGE NEWLY CREATED RULES.
To manage your newly created rule in custom conditional formatting in Excel. Select Manage Rules under Conditional Formatting.
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.