Mrinmoy Roy
Hiding duplicate rows are one of the most frequent tasks that we need to do in Excel. In this article, you will learn 4 methods to hide duplicate rows in Excel based on one column.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
4 Methods to Hide Duplicate Rows Based on One Column in Excel
1. Hide Duplicate Rows Based on One Column in Excel Using Advanced Filter
In this method, I will show you to hide duplicate rows based on the Category column by using the Advanced Filter in Excel.
To do that,
❶ Select the data table first.
❷ Then go to Data ➤ Sort & Filter ➤ Advanced.
The Advanced Filter dialog box will appear.
❸ Select Filter the list in place.
❹ Insert your entire table range in the List range box.
❺ Insert the cell range of the first column which is B4:B12 in the Criteria range box.
❻ Select Unique records only and hit OK.
This will automatically hide the duplicate rows based on the selected column.
Read More: Formula to Hide Rows in Excel (7 Methods)
2. Use New Rule of Conditional Formatting to Hide Duplicate Rows Based on One Column in Excel
In this method, I will show you to hide the duplicate rows using the New Rule option of the Conditional Formatting feature.
To do that,
❶ Select the data table first.
❷ Then go to Home ➤ Conditional Formatting ➤ New Rule.
A New Formatting Rule dialog box will appear.
❸ Select Use a formula to determine which cells to format.
❹ Insert the following formula in the Format values where this formula is true box.
=B5=B4It will compare consecutive cell values to check whether it is duplicate or not.
❺ Then click on the Format button.
Format Cells dialog box will appear.
❻ Go to the Font tab.
❼ Select White color in the Color section and hit OK.
Now all the duplicate rows will be hidden based on the first column.
Read More: Hide Rows Based on Cell Value with Conditional Formatting in Excel
Similar Readings
- Excel VBA: Unhide All Rows in Excel (5 Practical Examples)
- How to Unhide Multiple Rows in Excel (9 Methods)
- Excel Macro: Hide Rows Based on Cell Text in Excel (3 Simple Methods)
- VBA to Hide Rows in Excel (14 Methods)
3. Hide Duplicate Rows Based on One Column Using COUNTIF Function & New Rule in Excel
Now I will show you to hide the duplicate rows using the COUNTIF function.
❶ First select your data table.
❷ Then go to Home ➤ Conditional Formatting ➤ New Rule.
A New Formatting Rule dialog box will appear.
❸ Select Use a formula to determine which cells to format.
❹ Insert the following formula in the Format values where this formula is true box.
=COUNTIF($C$4:$C$12,$C4)>1Formula Explanation The COUNTIF function compares $C4 into the range $C$4:$C$12. If it finds any occurrences of more than 1 then it marks that as a duplicate entity. ❺ Then click on the Format button. Format Cells dialog box will appear. ❻ Go to the Font tab. ❼ Select White color in the Color section and hit OK. Now all the duplicate rows will be hidden based on the first column. Read More: VBA to Hide Rows Based on Criteria in Excel (15 Useful Examples) In this method, I will show you to hide duplicate rows using the CONCAT function and the context menu. ❶ First create a helper column and insert the following formula in the top cell of the Helper column. ❷ Then press ENTER. ❸ Drag the Fill Handle icon to the end of the Helper column. ❹ Select the entire Helper column. ❺ Go to Home ➤ Conditional Formatting ➤ Highlight Cells Rules ➤ Duplicate Values. The Duplicate Values dialog box will appear. ❻ Hit OK. All the duplicate values will be marked in red color. ❼ Select all the duplicate rows and right-click on them. ❽ Click on Hide from the context menu. Now all the duplicate rows will be hidden. Read More: How to Hide Rows Based on Cell Value in Excel (5 Methods) You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article. To sum up, we have discussed 4 methods to hide duplicate rows based on one column in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldem.
4. Use of CONCAT Function & Context Menu to Hide Duplicate Rows Based on One Column
=CONCAT(B5:E5)
Practice Section
Conclusion
Related Articles
