How to Hide Duplicates in Excel (4 Methods)

To explain this process clearly, let’s use a sample dataset from an online fruit store. The dataset contains 5 columns representing fruit order and delivery details: Product NameOrder IDPriceOrder Date, and Status.

Sample dataset to Hide Duplicates in Excel

Method 1 – Using Advanced Filter to Hide Duplicate Rows

If you have duplicate rows in your sheet, follow these steps:

  • Select the range B4:F13.
  • Go to the Data tab and choose Advanced.

Using Advanced Filter to Hide Duplicate Rows

  • A dialog box will appear.
  • Under Action, select Filter the list, in-place.
  • In the List range, ensure that B3:F13 is selected.
  • Check the box for Unique records only.
  • Click OK.

As a result, all duplicate rows will be hidden in the dataset.

Using Advanced Filter to Hide Duplicate Rows

If you need to unhide the duplicate rows later:

  • Go to the Data tab.
  • Select Clear.

This will clear the applied Advanced Filter, and your duplicate rows will reappear.

Using Advanced Filter to Hide Duplicate Rows


Method 2 – Using COUNTIF & Context Menu to Hide Duplicates in Excel

  • Begin by making adjustments to your dataset. Keep the Product NameOrder ID, and Status columns, and merge them if necessary to provide a clear view of duplicate rows.

Using COUNTIF & Context Menu to Hide Duplicates in Excel

  • Select the cell range where you want to apply the formatting formula. For example, choose the cell range B4:E12.
  • Go to the Home tab and click on Conditional Formatting, then select New Rule.

  • In the dialog box that appears:
    • Choose Use a formula to determine which cells to format under Select a Rule Type.
    • In the Edit the Rule Description field, enter the following formula:
=COUNTIF($C$4:$C$12,$C4)>1
      • Here, the COUNTIF function checks if the value in cell $C4 occurs more than once within the range $C$4:$C$12.
  • Click on Format to choose the formatting style. For example, select the color Red to format the cell values.
  • Finally, click OK.

Using COUNTIF & Context Menu to Hide Duplicates in Excel

  • As a result, all duplicate values will be formatted according to your chosen style.

  • To hide the duplicate rows using the context menu:
    • First, select any duplicate cell.
    • While holding the CTRL key, select other duplicate rows that you want to hide.

    • Right-click the mouse and choose Hide.

Using COUNTIF & Context Menu to Hide Duplicates in Excel

This will hide all the selected duplicate rows in your dataset.


Method 3 – Using Conditional Formatting to Hide Duplicates

The Conditional Formatting feature in Excel offers various options, and working with duplicate values is one of them.

Let’s see how you can hide duplicates using the Highlight Cells Rules:

  • Begin by selecting the cell range where you want to apply Conditional Formatting. For example, choose the cell range B4:F13.
  • Now, follow these steps:
    • Open the Home tab.
    • Under Conditional Formatting, go to Highlight Cells Rules and select Duplicate Values.

Using Conditional Formatting to Hide Duplicates

  • A dialog box will appear.
  • In the dialog box:
    • Choose Format cells that contain and values with.
    • For Format cells that contain, select Duplicates.
    • For Custom Format, another dialog box will pop up to choose the format.

  • In the second dialog box, you can select any color. However, to hide duplicates, choose the same background color as your cell.
    • For example, if your cell color is White, select White as the format color.

Using Conditional Formatting to Hide Duplicates

  • Click OK on both dialog boxes.

As a result, all duplicate values, including the first occurrence, will be hidden in your dataset.

Using Conditional Formatting to Hide Duplicates

Read More: Excel Remove Duplicates Not Working 


Method 4 – Hide Duplicates Using Formula in Condition Formatting

You can utilize any formula in Conditional Formatting to format cells or cell ranges. In this method, we’ll apply a formula to hide duplicates in the dataset.

  • Start by selecting the cell range where you want to apply the formula for formatting. For example, choose the cell range B4:F13.
  • Follow these steps:
    • Open the Home tab.
    • Under Conditional Formatting, select New Rule.

Hide Duplicates Using Condition in Excel

  • A dialog box will appear.
  • In the dialog box:
    • Choose Use a formula to determine which cells to format under Select a Rule Type.
=B4=B3
      • This formula checks if the value in the active cell (B4) is equal to the cell above it (B3). If they are equal, the result of this formula is TRUE, and formatting will be applied to the cells; otherwise, if they are not equal, the result is FALSE, and no format will be applied.
  • Click on Format to choose the format.

  • Another dialog box will pop up for selecting the format.
  • In the second dialog box, you can select any color. However, to hide duplicates, choose a color that matches the background of your cell.
    • For example, if your cell color is White, select White as the format color.

Hide Duplicates Using Condition in Excel

  • Click OK on the Edit Formatting Rule dialog box.

As a result, all consecutive duplicate values will be hidden in your dataset.

Hide Duplicates Using Condition in Excel

Practice Section

A practice sheet has been provided in the workbook to practice these explained examples.

Practice hide Duplicates in excel

 

Download to Practice

You can download the practice workbook from here:

 

Further Readings

 

<< Go Back to Remove Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo