Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

How To Use Formulas In Conditional Formatting To Find New Customers From Data

Conditional Formatting is helping us from last 8 to 9 years. A well-designed Data Analysis & Presentation Tool created by Microsoft to enhance user experience. Everyone has a different way to work with Conditional Formatting. But, the good thing is that it is simple to use even it so powerful.

In this post, I will use the formulas in conditional formatting to highlight new customers in month’s sale data.

Highlight New Customers From Month’s Sale Data Using Conditional Formatting

We have to start with a list of customer’s ID & name which is maintained from the start of the business. And, we have 26 customers in current month’s data.

We have to find old & new customers by using formulas in Conditional Formatting.

  • Select cell E2, from where the current month’s customer ID list starts.

Use Conditional Formatting To Find New Customers From Sales Data

  • Go to Home tab -> Style Section -> Conditional Formatting -> New Rule. You can also use Shortcut Key Alt-H L N.

Use Foramulas In Conditional Formatting To Find New Customer From The Sales Data

  • Now Select the last option from the list. “Use a formula to determine which cell to format”. And, enter the formula “=COUNTIFS(B:B,E5)=0”.

Use Formulas In Conditional Formatting In Heighlight New Customers

  • We have to select the color pattern to highlight the customer ID which is not in the master list. In Simple Words, we are highlighting new customers. You can also choose completely different cell formatting like (Font Size, Font Color Etc). After Specifying the formatting press OK.
  • Now, All the new customers are highlighted in the data.

highlight new customers by using formauls in conditional formatting

How Above Formula Work With Conditional Formatting

The formula used in conditional formatting in above example is verifying the count of customer ID from the master data. If the count equals to “0” then customer ID will be highlighted.

Download Sample File


Apart from this, there are lots of wonderful things which are possible with the help of Conditional Formatting. So we will discuss all that later on.

Related Tips