Take a Guess to Win Windows 10 Pro Key!
previous arrow
next arrow
Slider

Useful Excel Tricks for Conditional Formatting

Date:2021-4-7 Author:Sandra

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
Useful Excel Tricks for Conditional Formatting
Useful Excel Tricks for Conditional Formatting
  • Enter the formula:

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

Useful Excel Tricks for Conditional Formatting
  • Set the highlighted format
Useful Excel Tricks for Conditional Formatting

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.

Useful Excel Tricks for Conditional Formatting

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)
Useful Excel Tricks for Conditional Formatting
  • Set the highlighted format
Useful Excel Tricks for Conditional Formatting
Useful Excel Tricks for Conditional Formatting
Useful Excel Tricks for Conditional Formatting

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

Useful Excel Tricks for Conditional Formatting
  • Enter the formula:

=$E2=”Yes”

  • Set the highlighted format
Useful Excel Tricks for Conditional Formatting
Useful Excel Tricks for Conditional Formatting

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    
Useful Excel Tricks for Conditional Formatting

         

Useful Excel Tricks for Conditional Formatting

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

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *