Skip to main content

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

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.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

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.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

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


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

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!
Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How do you check to see if a row has duplicates within itself? i dont care if it matches anything in any other row, only itself.
This comment was minimized by the moderator on the site
thank you, you saved us a lot of work and time
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations