Arrow left
Back to product guides
Google Sheets Formulas

UNIQUE Function in Google Sheets: Explained

In this article, you will learn how to use the UNIQUE formula in Google Sheets. The UNIQUE formula is one of the ways to remove duplicates from your data set or pick up different keywords from a list of keywords.

How to use the UNIQUE function in Google Sheets

  1. Type “=UNIQUE” or go to “Insert”“Function” (or directly navigate to the “Functions” icon)  “Filter”“UNIQUE”.
  2. Select a range from which you delete duplicates.
  3. Determine the way of filtering (i.e., by row(s) or column(s) and whether you include the items considered duplicates in the original data) if needed.
  4. Press the “Enter” key.
How to insert the UNIQUE function in Google Sheets

The general formula is as follows:

=UNIQUE(range, [by_column], [exactly_once])

Range: This argument requires a range or an array you want to filter.

By_column (Optional): This parameter defines how the function checks the “range”. You input “FALSE” or “TURE” or keep this item blank. With “FALSE”, the function checks items by rows, and with “TRUE”, it sorts the things by columns. Without any input, the default setting is “FALSE”.

Exactly_once (Optional): This argument determines how the function shows the result. You input “FALSE” or “TURE” or keep this argument blank. With “FALSE”, the function shows things considered duplicates in the original data set (not all of them but one from each duplicate), and with “TRUE”, they are excluded from the result. Without any input, the default setting is “FALSE”.

Notes

  1. Secure enough space for the formula to spread its result.
  2. Ensure you don’t have the extra space(s) or other hidden texts in each cell in the range to be filtered.
  3. Ensure that the items to be sorted have the same formatting (e.g., currency mark).

Look at the following example. This picture shows a simple case. In this case, both arguments are blank; thus, the show formula is equivalent to =UNIQUE(B3:B9, “FALSE”, “FALSE”). If you change the “by_column” argument to “TRUE”, the formula shows the same list of items as the range (B3:B9) is considered one item. When you keep the “by_column” parameter blank and input “TRUE” in the last argument, the “exactly_once”, you get a similar sorted list as shown in the screenshot below but without “LiveFlow”, as it is a duplicate in the original list and thus excluded from the result by the parameter.

How the UNIQUE function works for a column in Google Sheets

Finally, see how the formula works for an array. In the following screenshot, the upper table is the initial data set, and the lower table is the result returned by the UNIQUE formula. The highlighted rows are duplicates. Can you see that there is no duplicate in the filtered table? For the “by_column” argument, this example describes how it works better. With “FALSE” input, the formula works by rows, meaning it goes through items grouped like [Isabella/North/20/Plan A] from the top to the bottom, and finds the same items.

How the UNIQUE function works for an array in Google Sheets

How do I prevent duplicates in Google Sheets?

As alternative ways of the UNIQUE function, you can use the Pivot table, Google Sheets’ Data clean-up tool (Remove duplicates), etc. to remove duplicates, and Conditional Formatting to identify duplicates in your data.

Other functions related to filtering

If you don’t get the right answer to your question in this article or you have other questions related to filtering, you may find the answers in the following articles.

Formulas for filtering

FILTER Function in Google Sheets: Explained - show values that meet the criteria defined

Google Sheets’ functions for filtering

Filter in Google Sheets: Explained - sort items quickly

Filter Views in Google Sheets: Explained - sort items and share the filter with your collaborators

Analyze your live financial data in a snap

Are you learning this formula to process financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711