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.
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.
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.
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.
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.
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 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.