Shamima Sultana
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.
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
⏩ 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.
➤ Now, click OK on the first dialog box of Duplicate Values.
Therefore, all the duplicates values are hidden including the first occurrence.
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
⏩ 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.
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.
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
⏩ 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=B3This 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.
➤ Now, click OK on the Edit Formatting Rule dialog box.
Therefore, all the consecutive duplicates values are hidden.
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.
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)>1Here, 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.
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.
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.
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.
