Useful Excel Tricks for Conditional Formatting

As we know that Conditional Formatting can easily spot trends and patterns in your data using bars, colors, and icons to visually highlight important values. But in fact, many people still ignore that conditional formatting is a very useful tool. So today, Sandra would share several tricks for using Conditional Formatting.

Highlight Duplicate Data

• Select the A2:A11 cell range and create a new format rule

• Enter the formula:

=COUNTIF(A$2:A2,A2)>1

• Set the highlighted format

You can choose the color, change the Font, size, or Font style. The author chooses red color here, and then you will find that the repeated name will turn to red.

Highlight the Lowest Price for Each Item

• Select the B4:F13 cell range and create a new formatting rule
• Enter the formula: =B4=MIN($B4:$F4)

• Set the highlighted format

In the fruit price table, the lowest price of each fruit will change to a red and bold font.

Highlight Completed Tasks

Select the E2:E12 cell range and create a new format rule

• Enter the formula:

=$E2=”Yes”

• Set the highlighted format

Tasks that have been completed will become red and bold fonts.

Remind Employee’s Birthdays Within a Week

• Select the A4:D8 cell range and create a new formatting rule

• Enter the formula:

=DATEDIF($D4,NOW()+7,”yd”)<=7

• Set the highlighted format    

         

In this way, you will not miss the birthday of any of your colleagues, remember to send them your most sincere wishes.