To hide duplicates means to get rid of them by either replacing them with other values or deleting them. There are various ways of doing this in Google Sheets and Excel. This post discusses all these workarounds of hiding duplicates in Google Sheets and Excel.
How to hide duplicates in Excel
Table of Contents
a) Using the Remove duplicate tool
1. Open the Excel document that contains the duplicate values.
2. Highlight the dataset that you want to hide the duplicates.
3. On the toolbar, click on the Data tab. In the Data Tools section, click on the Remove duplicates button.
4. From the box, toggle on the My data has header button if the dataset has a header.
5. Then, click the select all button. Finally, click on the Ok button.
b) Using conditional formatting to hide duplicates
Steps:
1. Open the Excel document that contains the duplicate values.
2. Highlight the dataset that you want to hide the duplicates.
3. On the toolbar, click on the Home tab. In the Styles section, click on the Conditional Formatting drop-down button.
4. From the menu, hover the cursor over the Highlight Cells Rules button.
5. On the drop-down menu, choose the Duplicate option and select the formatting color you want. Click the Ok button to close the dialogue box.
6. All the duplicated cells will be highlighted.
7.
Manually remove or delete the cells that contain the duplicated values.
c) Using the Advanced tool to hide duplicates
Steps:
1. Open the Excel document that contains the duplicate values.
2. Highlight the dataset that you want to hide the duplicates.
3. On the toolbar, click on the Data tab. In the Sort & Filter section, click on the Advanced button.
4. In the dialogue box, select the cells sorted in the List range section if you did not highlight your data (step 2).
5. Check the Unique record only checkbox, and hit the Ok button.
How to hide duplicates in Google Sheets
a) Using the Remove Duplicates tool
This is an in-built tool in Google Sheets that can be used to hide duplicates in Google Sheets.
Here are the steps to follow while using this method:
1. Open the Google Sheets document that contains the duplicate values.
2. Highlight the section that contains the values to be removed.
3. Locate the menu bar, and click on the Data tab.
4. From the menu, hover the cursor over the data cleanup button and click on the Remove duplicates option.
5. From the box, the toggle on the data has a header row if the dataset has a header.
6. Then, check the select all button. Finally, click on the Remove Duplicates button.
b) Using the UNIQUE function
Steps:
1. Open the Google Sheets document that contains the duplicate values.
2. On the Formulae bar, type this formulae =UNIQUE(
3. Select all the columns and rows that contain the dataset you want to hide duplicates. That is, =UNIQUE(A2:A8).
4. Finally, hit the Enter button.
