NEW! FREE 1 Hour Excel Data Cleaning Course! Watch now 

Tutorials

Conditional Formatting with Formulas

2 Mins read

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.
[xyz-ihs snippet=”Excel-Curriculum”]

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.
[xyz-ihs snippet=”Excel-online-Course”]

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.
[xyz-ihs snippet=”Excel-Curriculum”]

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.
[xyz-ihs snippet=”Excel-online-Course”]

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 posts
Power BITutorials

Opportunity Analysis Power BI Dashboard

1 Mins read
Information: A software company that has two sales channels: direct and partner. The sales manager created this dashboard to track opportunities and…
Power BITutorials

Procurement Analysis Power BI Dashboard

1 Mins read
Information: It analyzes a manufacturing company’s spending on vendors by category and location. we explore these areas: Who the top vendors are…
Power BITutorials

Retail Analysis Power BI Dashboard

1 Mins read
Information: It analyzes retail sales data of items sold across multiple stores and districts The metrics compare this year’s performance to last…