Welcome to this article where I will share an exciting trick to make your Google Sheets data unique using the UNIQUE function. Whether you want to remove duplicates from your dataset or select different keywords from a list, the UNIQUE formula has got you covered.
How to Use the UNIQUE Function in Google Sheets
- To begin, type “=UNIQUE(” in a cell or go to “Insert” → “Function” (or directly access the “Functions” icon) → “Filter” → “UNIQUE”.
- Next, select the range of cells from which you want to remove duplicates.
- If needed, decide whether to filter by row(s) or column(s) and whether you want to include duplicate items in the original data.
- Finally, press the Enter key to apply the formula.
The general formula structure is as follows:
- Range: Provide the range or array you want to filter.
- By_column (optional): Specify how the function checks the range. Use “FALSE” for row-wise filtering and “TRUE” for column-wise sorting. Leaving it empty defaults to row-wise filtering.
- Once_only (optional): Determine how the function displays the result. Use “FALSE” to show all duplicate items from the original dataset and “TRUE” to exclude them. By default, “FALSE” is assumed.
Notes:
- Make sure you have enough space in your sheet for the formula to distribute its unique results.
- Check that there are no extra spaces or hidden text in the cells of the filtered range.
- Ensure that the items to be sorted have the same format, such as the same currency symbol.
Let’s take a look at an example. The image below demonstrates a simple case where both additional arguments are empty. Thus, the displayed formula is equivalent to =UNIQUE(B3:B9, "FALSE", "FALSE"). By changing the “by_column” argument to “TRUE,” the formula displays the same list of items as the range (B3:B9) considered as a single item. When you leave the “by_column” parameter blank and enter “TRUE” as the last argument, “once_only,” you get a sorted list that excludes duplicates like “LiveFlow” from the original list.
Now, let’s see how the formula works for an array. In the following screenshot, the top table represents the initial dataset, and the bottom table shows the result returned by the UNIQUE formula. The highlighted rows are duplicates. Notice how the filtered table contains no duplicates. This example best describes how the “by_column” argument operates. With an input of “FALSE,” the formula works by rows. It goes through the grouped items like [Isabella/North/20/Plan A] from top to bottom and finds the same items.
How to Avoid Duplicates in Google Sheets?
Apart from using the UNIQUE function, you can explore other alternatives to remove duplicates in Google Sheets, such as Pivot Tables, the Google Sheets Data Cleaning Tool (Remove Duplicates), or conditional formatting to identify duplicates in your data.
I also invite you to visit Crawlan.com to discover more interesting tips and tricks about Google Sheets.
Now that you have all the necessary information, go ahead and make your data unique with the help of the UNIQUE function in Google Sheets. Have fun exploring and manipulating your data like never before!
