To get a better idea of UNIQUE works, take a look at the following list of names.<\/p>\n
You can see there are three duplicated entries: David, Eric, and Caroline.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Unique-image-1.png” alt=”unique-function-google-sheets-1″ title_text=”Unique image 1″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
If you\u2019re familiar with Microsoft Excel, you probably know the Remove Duplicates<\/a>\u00a0option that enables you to find and delete any repeated values in a range of data. Google Sheets does not have the same feature but it has an alternative for removing duplicates: you guessed it, the UNIQUE function.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =UNIQUE(range)<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” custom_margin=”||-4px|||” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” custom_margin=”-30px|||||” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I will use a similar dataset to the example in the first image.<\/p>\n Note that the UNIQUE function is not one-dimensional. This means it can accept ranges with two or more rows\/columns. But let\u2019s start with a single-dimensional column, just to grasp the concept better.<\/p>\n Here\u2019s an example:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Unique-image-2.png” alt=”unique-function-google-sheets-2″ title_text=”Unique image 2″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n When you type =UNIQUE(A2:A15)<\/strong> into cell C2 and hit the Enter key, the UNIQUE function returns all the unique values from range A2:A15.<\/p>\n Results start appearing from C2 (the cell where the function is) and flow downwards in the sheet, as you can see in the image below.<\/p>\n You\u2019ll notice that the duplicated results, David, Eric and Caroline, are automatically removed from the result column.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Unique-image-3.png” alt=”unique-function-google-sheets-3″ title_text=”Unique image 3″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n What happens if you extend the input range to multiple columns? Let\u2019s find out.<\/p>\n Here I\u2019ve used both the Name<\/strong> and Age<\/strong> columns, as shown in the image below:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Unique-image-4.png” alt=”unique-function-google-sheets” title_text=”Unique image 4″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Just like before, UNIQUE returns unique values from the range. The results are displayed from cell C2 onwards.<\/p>\n You can see that the output values flow\u00a0 [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Unique-image-5.png” alt=”unique-function-google-sheets-5″ title_text=”Unique image 5″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the example above, two of the duplicated results for Name<\/strong> (Eric and Caroline),\u00a0turn out to be unique when combined with Age<\/strong>. Eric in row 3 is 23 years old, whereas Eric in row 15 is 25 years old. These are different data entries and not duplicates. However, there are two Davids aged 21, which the function reads as duplicates, so it eliminates one of them.<\/p>\n The fact that the UNIQUE function can filter data across multiple columns is extremely useful, but what if you have data across multiple columns, but you want to filter based on only one of the columns?<\/p>\n For example, what if you want to get rid of all the duplicated names in the example above while still keeping the ages in the output? If this is the case, I encourage you to look into the SORTN function<\/a>.<\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”255443″ theme_builder_area=”post_content”]<\/p>\n Automate data transfers between spreadsheets Find out how<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n A helpful trick to clean up your spreadsheet and make the data easier to interpret is to use the SORT function in combination with the UNIQUE function. This will filter out all the duplicates and simultaneously sort the data so that the result is a sorted list of unique entries. In the example below, I do this with the formula =Sort(Unique(A2:B15))<\/strong>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Unique-image-6.png” alt=”unique-function-google-sheets-6″ title_text=”Unique image 6″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the last example I\u2019ll show you how to use the UNIQUE function for horizontal data in a row.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/UNIQUE-FORMULA-GOOGLE-SHEETS-HORIZONTAL-DATA.png” alt=”UNIQUE FORMULA GOOGLE SHEETS HORIZONTAL DATA” title_text=”UNIQUE FORMULA GOOGLE SHEETS HORIZONTAL DATA” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]Because the UNIQUE function only works with vertical data in columns, you have to use the TRANSPOSE<\/a> function to change the data from horizontal to vertical form within the function.<\/p>\n Then once the UNIQUE function has done its work, you can transpose it again back to a horizontal format. That\u2019s why the TRANSPOSE function is used twice in the example above. The formula here is =Transpose(Unique(Transpose(B1:O1)))<\/strong>.[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Unique-image-8.png” alt=”unique-function-google-sheets-8″ title_text=”Unique image 8″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If you simply want to count how many unique values appear in a specific range, learn how to use\u00a0COUNTUNIQUE<\/a>\u00a0in our guide\u00a0How to use the COUNTUNIQUE function in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" As the name suggests, the Google Sheets UNIQUE\u00a0function enables you to pull out\u00a0unique\u00a0rows from a range, discarding any data that’s duplicated. This is really handy when you have a large volume of data, such as responses from a Google Form. UNIQUE allows you to quickly identify which values (e.g., a person, or a product name) […]<\/p>\n","protected":false},"author":39,"featured_media":29223,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Syntax<\/h3>\n
\n
<\/ul>\n
How to use Google Sheets UNIQUE function<\/h3>\n
UNIQUE function with multiple columns<\/h4>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\"){kind=icon}
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
<\/span><\/p>\nUNIQUE function with SORT function<\/h3>\n
UNIQUE function with horizontal data<\/h3>\n
Remember that UNIQUE needs space<\/h4>\n
\n
UNIQUE function: key tips<\/h3>\n
\n
![\"UNIQUE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/unique1.png\")
<\/p>![\"UNIQUE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/unique2.png\")
<\/p>![\"UNIQUE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/unique3.png\")
<\/p>![\"UNIQUE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/unique4.png\")
<\/p>![\"UNIQUE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/unique5.png\")
<\/p>![\"UNIQUE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/unique6.png\")
<\/p>