How to hide duplicate rows in excel

Answer

Hiding duplicate rows in Excel requires identifying the duplicates first and then applying a filter or conditional formatting to hide them. The process is slightly different depending on whether you want to hide entire rows or just mark duplicates in a specific column.

Method 1: Using Conditional Formatting and Filtering

1. Select the data range
   Highlight the range of cells or the entire table where you want to check for duplicates.

2. Apply Conditional Formatting

   • Go to the Home tab.

   • Click on Conditional Formatting → Highlight Cells Rules → Duplicate Values.

   • Choose a formatting style to mark duplicates (for example, light red fill).
     This step visually identifies the duplicates in your dataset.

3. Apply a Filter

   • Select your table, then go to Data → Filter.

   • Click the filter arrow on the column with duplicates.

   • Filter by Color or specific formatting to display only duplicates.

4. Hide the duplicate rows

   • Select the visible duplicates.

   • Right-click on the selected rows → Hide.

This method does not delete the duplicates. It hides rows temporarily while keeping the original data intact.

Method 2: Using a Helper Column

1. Insert a helper column
   Add a new column next to your data (e.g., column A if your data starts in column B).

2. Use the COUNTIF formula
   In the helper column, enter the formula to mark duplicates: 

   =IF(COUNTIF($B$2:B2, B2)>1, "Duplicate", "Unique")

   Replace B2 with the first cell in the column you are checking. This formula labels each duplicate after its first occurrence as "Duplicate.

3. Filter duplicates

   • Select the helper column.

   • Go to Data → Filter → choose Duplicate.

4. Hide duplicate rows

   • Select all rows labeled "Duplicate".

   • Right-click → Hide.

This method works well when you want to hide duplicates based on one column or multiple columns (combine columns using =B2&C2&D2 in the helper column).

Method 3: Using Advanced Filter (No Formula)

1. Select your data range
   Include the column headers.

2. Go to Data → Advanced

   • Click Data → Advanced in the Sort & Filter group.

   • Choose Copy to another location.

   • Check Unique records only.

3. Copy unique rows
   Copy the filtered unique rows to another location. The original dataset remains intact. You can hide the original rows if needed.

Notes

• On Mac, the steps for conditional formatting and filtering are the same, but the menu layout differs slightly. Conditional Formatting is under Home → Styles → Conditional Formatting, and filters are under Data → Filter.

• Always back up your data before hiding rows. Hidden rows can be unhidden later using Home → Format → Hide & Unhide → Unhide Rows.

This approach ensures that duplicates are not removed but only hidden from view, maintaining data integrity.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report