Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to highlight cells with same/duplicate values in Excel?

Do you know how to quickly highlight duplicate values in one column in Excel? And what if quickly highlighting all same values between two columns in Excel? This article will discuss several methods about them.

Highlight cells with same/duplicate values in one column with Conditional Formatting

Highlight cells with same/duplicate values in one column with Kutools for Excel

Highlight cells with same/duplicate values between two columns with Kutools for Excel

Quickly highlight all duplicate/unique values in a column in Excel

General, we can remove duplicates from a list easily with Data > Remove Duplicates in Excel. But how to select duplicate/unique values, or count them from a column? And what if selecting or counting duplicate/unique except the first duplicate one? Try Kutools for Excel’s Select Duplicate & Unique Cells utility! Full Feature Free Trial 30-day!
ad select duplicate unique values 1

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words...
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum...
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns...
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value...
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.


Highlight cells with same/duplicate values in one column with Conditional Formatting

We can apply the conditional Formatting feature to highlight all duplicates values in a certain column easily. You can do as follows:

1. Select the column that you will highlight all duplicate values, and click the Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
doc highlight same cells 1

2. In the opening Duplicate Values dialog box, specify the highlight color in the second drop down list, and click the OK button.
doc highlight same cells 2
Note: You can also customize the fill color with selecting the Custom Format from the second drop down list, and then specify the custom fill color on the Fill tab of Format Cells dialog box. See below screenshot:
doc highlight same cells 15

So far, all duplicate values in the selected column are highlighted with the specified color as below screenshots shown:


Highlight cells with same/duplicate values in one column with Kutools for Excel

Kutools for Excel also provides a Select Duplicate & Unique Cells utility to highlight duplicate cells in a certain column. What’s more, this utility enable to highlight the whole rows that duplicate values are in.

1. Select the column that you will highlight duplicate values, and click the Kutools > Select > Select Duplicate & Unique Cells. See screenshot:

2. In the opening Select Duplicate & Unique Cells dialog box, please (see screenshot above):
(1) Check the Duplicate (Except 1st one) option or All duplicates (Including 1st one) option;
(2) Check the Fill backcolor option, and click below drop down list and specify a fill color;
(3) It’s optional to check the Select entire rows option.

3. Click the Ok button. Then a dialog box comes out to show how many rows have been selected. Just click the OK button to close it.

Then you will get the similar highlight rows/cells with same values as below screenshots shown:


Highlight cells with same/duplicate values between two columns with Kutools for Excel

Sometimes, you may want to compare two columns, and then highlight the same values in one column. For example, you need to compare the Column B and Column G as below screenshot shown, and then highlight the same values in the Column B. You can try Kutools for Excel’s Compare Ranges utility to archive it easily.

1. Click the Kutools > Select > Select Same & Different Cells to open the Compare Ranges dialog box.
doc select same different cells

2. In the opening Compare Ranges dialog box, please:
doc highlight duplicate values 3092
(1) Specify the column that you will highlight same values in the Find values in box;
(2) Specify the column that you will highlight same values according to in the According to box;
(3) Check the Each row option in the Based on section;
(4) Check the Same Values option in the Find section.
(5) Check the Fill backcolor option, and click below drop down list to select a fill color;
(6) It’s optional to check the Select entire rows option.

Note: If your selected data has headers, please check the My data has headers option in the Compare Ranges dialog box.

3. Click the Ok button. Then a dialog box comes out to show how many rows have been selected. Just click the OK button to close it.

As long as values in Column B are same as values in Column G, corresponding cells or rows will be highlighted as below screenshots shown:


Demo: Highlight cells with same/duplicate values

 

In this Video, Kutools and Kutools Plus tabs are added by Kutools for Excel. If need it, please click for 30-day free trial without limitation!

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.