Excel Formulas to Highlight the Duplicate Values from Two Columns!!
Tags: excelformula, conditionalformattin, excelformulas
Published: 2021-03-15 11:48:32

---
title: Excel Formulas to Highlight the Duplicate Values from Two Columns!!
published: true
date: 2021-03-15 11:46:53 UTC
tags: ExcelFormula,Conditionalformattin,Excelformulas
canonical_url: https://geekexcel.com/excel-formulas-highlight-duplicate-values-from-two-columns/
---

For instance, you have a list of names, and you need to find out the repeated terms from the input ranges. In this case, what would you do? Here, we will show the simple formula used to **find and highlight the duplicate values from two columns in Excel**. Let’s step them below!! Get an official version of ** MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

[![Highlight duplicate values](https://geekexcel.com/wp-content/uploads/2021/03/Highlight-duplicate-values.png)](https://geekexcel.com/excel-formulas-highlight-duplicate-values-from-two-columns/highlight-duplicate-values/#main)<figcaption id="caption-attachment-22477">Highlight duplicate values</figcaption>
## General Formula:

- To find and highlight the duplicate values in Excel, you can use the below formula.

**=[AND](https://geekexcel.com/how-to-use-logical-function-and-in-excel-365/)([COUNTIF](https://geekexcel.com/formulas-to-count-with-countif-function-in-excel-office-365/)(range1,A1),COUNTIF(range2,A1))**

## Syntax Explanations:

- **AND** – This Function checks Multiple Conditions and returns [**TRUE **](https://geekexcel.com/use-true-function-in-microsoft-excel-365-simple-methods/)if all the conditions are evaluated as true otherwise returns **[FALSE](https://geekexcel.com/use-false-function-in-microsoft-excel-365-simple-methods/)**. Read more on the **[AND function](https://geekexcel.com/how-to-use-logical-function-and-in-excel-365/)**.
- **COUNTIF** – In Excel, the **[COUNTIF function](https://geekexcel.com/formulas-to-count-with-countif-function-in-excel-office-365/)** will help to count the number of cells that meet a single condition or criteria.
- **Range** – It represents the input values in a worksheet.
- **Comma symbol (,)** – It is a separator that helps to separate a list of values.
- **Parenthesis ()** – The main purpose of this symbol is to group the elements.

## Example:

Let’s consider the below example. Here we will show how to format cells that have duplicate values.

- First, we will enter the input values in **Column B** and **Column D**.
- Now we are going to find the duplicate values from these two columns and highlight them with the conditional formatting option.

![Input Ranges](https://geekexcel.com/wp-content/uploads/2021/03/Input-Ranges-18.png)<figcaption id="caption-attachment-22478">Input Ranges</figcaption>

- So, apply the above-given formula in the conditional formatting option.
- To know **how to apply conditional formatting** in Excel, just **refer to this article<u>:</u>[How to Apply the Formula with Conditional Formatting In Excel?](https://geekexcel.com/how-to-apply-formula-with-conditional-formatting/)**
- Finally, it will highlight the cells that contain duplicate values. Refer to the below image.

![Result](https://geekexcel.com/wp-content/uploads/2021/03/Result-22.png)<figcaption id="caption-attachment-22479">Result</figcaption>

## Bottom-Line:

Hope you like this article on how to find and **highlight the duplicate values from two columns in Excel**. If you have any **doubts** about this article, please feel free to share it with us. We will assist you. Thank you so much for visiting our site!! To learn more, check out **[Geek Excel](https://geekexcel.com/)!!** and [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

### Read Also:

- **[Highlight Min Value in Selection Using Macros in Excel Office 365!!](https://geekexcel.com/highlight-min-value-in-selection-using-macros-in-excel-office-365/)**