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.
Hide Duplicates in Excel: 4 Ways
1. 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.
- Select the range B4:F13
- Then, open the Data tab >> select Advanced
⏩ A dialog box will pop up.
- From Action, select Filter the list, in-place
- In the List range, range B3:F13 is selected
- 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 the Data tab >> select Clear
Therefore, the applied Advanced Filter will be cleared and you will get back your duplicate rows.
2. 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.
- Select the cell range B4:E12.
- Now, open the Home tab >> from Conditional Formatting >> select New Rule
⏩ A dialog box will pop up.
- From Select a Rule Type >> select 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 the range and selected the $C4 cell as the criteria to check which cell 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.
3. 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.
- Select 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.
- 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 I selected the color White and then clicked OK.
- Now, click OK on the first dialog box of Duplicate Values.
Therefore, all the duplicate values are hidden including the first occurrence.
Read More: Excel Remove Duplicates Not Working
4. Hide Duplicates Using Formula in Condition Formatting
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.
- 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 >> select 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, that matches the background of your cell.
- As my used cell color is White I selected the color White and then clicked OK.
- Now, click OK on the Edit Formatting Rule dialog box.
Therefore, all the consecutive duplicate values are hidden.
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 needs. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.
Further Readings
- How to Remove Duplicate Names in Excel
- Remove Both Duplicates in Excel
- How to Remove Duplicate Rows in Excel Table
- How to Remove Duplicates but Keep the First Value in Excel
- Remove Duplicate Rows Except for 1st Occurrence in Excel
- How to Delete Duplicates But Keep One Value in Excel
- How to Undo Remove Duplicates in Excel
<< Go Back to Remove Duplicates in Excel | Duplicates in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
