Conditional Formatting - Formula to search values

Today we are looking at another example of using formulas in conditional formatting.

It is recommended to first read and understand the entrance Conditional Formatting - formulas for selecting cells.

Imagine you have the list of European football teams, located in the range B5:B31.

What we want to achieve is to enter some text in a cell, for example D2, and if that text is in any value from the list then highlight the cell(s) in the way you want.

To do that the formula to be used is:

Conditional Formatting - Formula to highlight duplicates, errors and omissions

Today we are looking at another example of using formulas in conditional formatting.

It is recommended to first read and understand the entrance Conditional Formatting - formulas for selecting cells

1. Highlight duplicates in a list

For this example we will use a standard list of data like this. This list is in the range B7:B23.

So the formula to highlight duplicates is:

=COUNTIF($B$8:$B8,$B8)>1

Conditional Formatting - Formula to highlight any day(s) of the week

Today we are looking at another example of using formulas in conditional formatting.

It is recommended to first read and understand the entrance Conditional Formatting - formulas for selecting cells

For this example we will use a normal list of days. In my case the list is in the range A1: A15.

Conditional Formatting - Formula to highlight alternate rows or columns

Here's an example of using conditional formatting formulas.

It is recommended to first read and understand the post Conditional Formatting - formulas for selecting cells

For this example we will use a normal table like this.

As can be clearly seen the table is for Moe's Tavern, and it shows a record of the daily sales.

1. Highlighting alternate rows

Conditional Formatting - formulas for selecting cells

An area where more flexibility is allowed in conditional formatting is the use of formulas for selecting which cells that must be formatted. In this section we will see examples of what kind of usages we can have with this option.

• Formula to highlight alternate rows or columns
• Formula to highlight specific day of the week
• Formula to highlight errors, omissions, repetitions
• Formula to highlight cells that contains a search value

-------------------------------------------------------------------------------------------------------------------------

But first let's see how a formula for conditional formatting is introduced.


1. First you have to go to Conditional Formatting> New Rule

Guide to Conditional Formatting in Excel

Conditional formatting in Excel is a very useful tool for analysing data and made it easy to give a special format to a group of cells based on value of another cell. Among the uses that can provide you is the power to apply a specific font or different fill colour to those cells that meet user-specified rules and thus facilitate their visual identification. Here I present a series of entries for better understanding of the tool.

1. Conditional Formatting - what it is and how to apply

2. Highlighting cells using conditional formatting

3. Formulas for selecting cells

• Formula to highlight alternate rows or columns

• Formula to highlight days of the week

• Formula to highlight errors, omissions, repetitions

• Formula to highlight cells that contains a search value

4. Conditional Formatting - Data Bars  (coming soon)

5. Conditional Formatting - Color Scales  (coming soon)

6. Conditional Formatting - Icon Sets  (coming soon)

7- Advanced techniques 1 (coming soon)

8- Advanced techniques 2 (coming soon)

Highlighting cells using conditional formatting

And what kind of effects can conditional formatting do?

1. Highlight cells rules

Operations that may occur;

• Greater than and/or equal to a value

• Less than and/or equal to a value

• Between 2 values

• Equal to a value

• Text containing ...

• A date occurring...

• Duplicates values

Conditional Formatting in Excel - what it is and how to apply

What is conditional formatting?

Conditional formatting in Excel is a very useful feature in the analysis of data as it empowers you to give a special format to a group of cells according to a condition(s). This format may be a different fill colour or a specific type of font for the cells that meet the established criteria so you can easily identify on the screen.

Although a special format could be given manually, conditional formatting creates an automatic formatting for cells that meet the specified condition, giving the user better visualisations.

Examples of conditional formatting