Hide Duplicate Rows Based on One Column in Excel (4 Methods)

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 & FilterAdvanced.

Hide Duplicate Rows Based on One Column in Excel Using Advanced Filter

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.

Advanced Filter dialog box: Hide Duplicate Rows Based on One Column in Excel Using Advanced Filter

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 FormattingNew Rule.

Use New Rule of Conditional Formatting to Hide Duplicate Rows Based on One Column in Excel

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=B4

It will compare consecutive cell values to check whether it is duplicate or not.

❺ Then click on the Format button.

New Formatting Rule dialog box: Use New Rule of Conditional Formatting to Hide Duplicate Rows Based on One Column in Excel

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


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 FormattingNew 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)>1

Formula 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.

Format Cells: Hide Duplicate Rows Based on One Column Using COUNTIF Function & New Rule in Excel

Now all the duplicate rows will be hidden based on the first column.

Hide Duplicate Rows Based on One Column Using COUNTIF Function & New Rule in Excel

Read More: VBA to Hide Rows Based on Criteria in Excel (15 Useful Examples)


4. Use of CONCAT Function & Context Menu to Hide Duplicate Rows Based on One Column

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.

=CONCAT(B5:E5)

❷ Then press ENTER.

Use of CONCAT Function to Hide Duplicate Rows Based on One Column

❸ Drag the Fill Handle icon to the end of the Helper column.

❹ Select the entire Helper column.

❺ Go to Home Conditional FormattingHighlight Cells RulesDuplicate 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.

Use of Context Menu to Hide Duplicate Rows Based on One Column

Now all the duplicate rows will be hidden.

Read More: How to Hide Rows Based on Cell Value in Excel (5 Methods)


Practice Section

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.


Conclusion

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.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo