Conditional Formatting with Formulas

Let’s begin by defining the term Conditional Formatting?

Conditional formatting is also expressed as conditional automatic formatting. It is triggered under certain defined conditions by the user.

Say we use conditional formatting to make characters bold and italic at the start of each line. And it worked. Then, conditional formatting is an effective way to highlight information in the spreadsheet.

Excel presents a set of predefined rules to apply on the data without defining any new formulas. Moreover, rules can be custom defined to apply any new formulas. The reason behind defining a new formula is:

A rule can trigger on a certain condition to apply the logic you have in mind. Formulas will provide the largest efficiency and effectiveness.

conditional formatting

A most common use of conditional formatting is this:

When we must highlight certain values greater than or lesser than certain values.

As an example, let’s use conditional formatting in the list shown in the picture above. The list shows names of employees, positions held and their salaries. Our task here is to highlight employees with salaries greater than 5000.

Conditional formatting will highlight all the selected salaries with a mentioned color.

The Process of conditional formatting consists of the following steps:

  • Select the cell to be formatted.
  • Select the conditional formatting menu from the style group in the home tab menu.
  • A Conditional formatting menu will appear to choose the interesting rule.
  • After that click OK. The rule will be selected and applied to the specified column.

Note:
Each rule demands different criteria or condition to trigger the rule.
The Format can be chosen from the pre-built option or it can be custom defined.

new-color-scale-rule-and-two-colour-scale-example

Let’s discuss it with some examples:

Two color scale:

This slide represents a newly defined rule for the column salary. They show data from least to most on a color scale from light to dark.

Here the first picture represents the menu to define the new rule.

The second picture shows the salary column with light to dark colored values.
Average-rule-and-Average-rule-result

AVERAGE rule

In this example, a new rule is defined based on average. Select all the cells and if their average will be greater than 10000 than the whole row will be selected.

Troubleshooting:

For starters, one common problem is:

How to apply conditional formatting rules such that they function well.

The most important thing to remember while writing a formula is this.

Make sure that your formula starts with an equal sign. If the formula did not start with an equal sign it is converted into a string of text. The formula becomes useless.

Most times, the formula is entered correctly but it does not give values correctly. When this happens, check formula for dummy formulas.

Dummy Formulas are life savers. They serve in conditional formatting formulas. They test to ensure the accuracy of work you are doing. Moreover, it’s a great way to analyze how conditional formatting works.

Restrictions:

Restrictions on conditional formatting are:

The First restriction on operations including union and intersection. These operations do not allow Conditional formatting.

The application of a valid formula logic will help in overcoming this restriction. Apply a valid formula logic to a cell. Then refer to that cell in the place of the formula.

The Second restriction is on the usage of icons, color scales or data bars when applying a custom formula. A custom formula only helps in number formatting, color filling options.

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018