How to Hide Duplicates in Excel (4 Ways)

While working in Excel, you may have duplicate values in your dataset. For any reason, if you need to hide those duplicate values or rows then you will be able to do it. In this article, I’m going to explain how to hide duplicates in Excel.

To make the explanation easy to understand I’m going to use a sample dataset of an online fruit store. The dataset has 5 columns that represent fruit order and delivery details. These columns are Product Name, Order Id, Price, Order Date, and Status.

Sample dataset to Hide Duplicates in Excel


Download to Practice


4 Ways to Hide Duplicates in Excel

1. Using Conditional Formatting to Hide Duplicates

The Conditional Formatting feature provides so many options to work with Duplicate Values is one of them.

Here, I’ll show you how you can hide duplicates using Highlight Duplicates.

To start with, select the cell range where you want to apply Conditional Formatting.

➤ I selected the cell range B4:F13.

Now, open the Home tab >> from Conditional Formatting >> go to Highlight Cells Rules >> select Duplicate Values

Using Conditional Formatting to Hide Duplicates

⏩ A dialog box will pop up.

From there select Format cells that contain: and values with.

⏩ I selected Duplicates in Format cells that contain and CustomFormat in values with

⏩ Another dialog box will pop up to choose the format.

From there you can select any color, but to hide duplicates you will need to choose the color of your cell (same background color).

⏩ As my used cell color is White so I selected the color White then click OK.

Using Conditional Formatting to Hide Duplicates

➤ Now, click OK on the first dialog box of Duplicate Values.

Therefore, all the duplicates values are hidden including the first occurrence.

Using Conditional Formatting to Hide Duplicates


2. Using Advanced Filter to Hide Duplicate Rows

If you have duplicate rows in your sheet then you can use the Advanced Filter to hide duplicate rows.

To begin with,

First, select the cell range from where you want to hide duplicates.

➤ I selected the range B4:F13

Then, open Data tab >> select Advanced

Using Advanced Filter to Hide Duplicate Rows

⏩ A dialog box will pop up.

From there select the required information.

⏩ In Action I selected Filter the list, in-place

In the List range, there is the selected cell range B3:F13.

Mark on Unique records only.

Finally, click OK.

Hence, you will see all the duplicate rows are hidden in the dataset.

Using Advanced Filter to Hide Duplicate Rows

In case you want to unhide the duplicate rows you easily can do it.

First, open Data tab >> select Clear 

Therefore, the applied Advanced Filter will be cleared and you will get back your duplicate rows.

Using Advanced Filter to Hide Duplicate Rows


Similar Readings:


3. Hide Duplicates Using Condition

You can use any formula in Conditional Formatting to format any cell or cell range. Here, I will apply a formula to hide duplicates in the dataset.

To begin with, select the cell range where you want to apply the formula to format the cell.

➤ I selected the cell range B4:F13.

Now, open the Home tab >> from Conditional Formatting >> select New Rule

Hide Duplicates Using Condition in Excel

⏩ A dialog box will pop up.

From there Select a Rule Type

⏩ I selected the rule Use a formula to determine which cells to format.

In Edit the Rule Description, provide the following formula

=B4=B3

This formula will check the active cell’s B4 value is equal to the cell above which is the B3 cell. If they are equal, the result of this formula is TRUE and format will be applied to the cells otherwise FALSE no format will be applied.

Now, click on Format to choose the format.

⏩ Another dialog box will pop up to choose the format.

From there you can select any color, but to hide duplicates you will need to choose the color, matches the background of your cell.

⏩ As my used cell color is White so I selected the color White then click OK.

Hide Duplicates Using Condition in Excel

➤ Now, click OK on the Edit Formatting Rule dialog box.

Therefore, all the consecutive duplicates values are hidden.

Hide Duplicates Using Condition in Excel


4. Using COUNTIF & Context Menu to Hide Duplicates in Excel

By applying the COUNTIF function and Context Menu you can hide the duplicate rows.

For this procedure, I’ve made some adjustments to the dataset. Kept the Product Name, Order ID, and Status columns, and merged them to provide a clear view of duplicate rows.

Using COUNTIF & Context Menu to Hide Duplicates in Excel

To begin with, select the cell range where you want to apply the formula to format the cell.

➤ I selected the cell range B4:E12.

Now, open the Home tab >> from Conditional Formatting >> select New Rule

⏩ A dialog box will pop up.

From there Select a Rule Type

⏩ I selected the rule Use a formula to determine which cells to format.

In Edit the Rule Description, provide the following formula

=COUNTIF($C$4:$C$12,$C4)>1

Here, in the COUNTIF function, I selected the cell range $C$4:$C$12 as range and selected the $C4 cell as criteria to check which cells value is occurring more than once.

Now, click on Format to choose the format.

⏩ I selected the color Red to format the cell values.

Finally, click OK.

Using COUNTIF & Context Menu to Hide Duplicates in Excel

As a result, all the duplicate values will be formatted.

Now, using the context menu I’ll hide the duplicate rows.

First, select any duplicate cell then hold the CTRL key and select other duplicate rows that you want to hide.

Then, right-click on the mouse and select Hide.

Using COUNTIF & Context Menu to Hide Duplicates in Excel

Hence, all the selected duplicate rows are hidden in the dataset.


Practice Section

I’ve provided a practice sheet in the workbook to practice these explained examples.

Practice hide Duplicates in excel


Conclusion

In this article, I’ve explained 4 ways to hide duplicates in Excel. You can follow any of the methods depending on your need. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Further Readings

Shamima Sultana

Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo