WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to Find Distinct Values in Excel (3 Easy Methods)

August 9, 2022
6.3K Views

While working on different lists in excel you need to identify Excel Distinct Values (2016/2019/mac/online) so that you can find out all different/unique/distinct sorts of entries on any list and omitting all the repeated entries. Now this may be a very tricky and time consuming task especially when the lists are very long. But luckily enough there exist quite simple methods in excel that can enable you to do this task very quickly and efficiently.

This article covers the basic methods to identify Excel Distinct Values (2016/2019/mac/online). Once you go through this article you will be able to easily identify unique vales in different representation/output ways. Let’s cover the simple methodologies of finding Excel Distinct Values WPS/ (2016/2019/mac/online)

Three different ways to identify Excel Distinct Values .

1.1 Using Home tab to identify Excel Distinct Values:

You can identify Excel Distinct Values using home tab by following simple below mentioned steps and picture illustrations.

Steps:

1.Open WPS Excel /Spreadsheet file where you want to identify Excel Distinct Values.Go to Home Tab.

2.There will be an Auto filter section in it.Hover your mouse cursor to Auto filter button and press left click of mouse.

3.A drop down shall appear.

4.There will be an entry with the name of advanced filter in it.

5.Press that advanced filter button.A new window shall appear.

6.Make sure that in the topic section of the window filter the list, in-place is selected.

7.In the List range section on that new window, select the list in which you wish to find unique values by either manually entering the list range or by using the mouse cursor and selecting the range through mouse cursor,

8.Then press Enter.Now mark the Unique records only checkbox at the bottom of the window.

9.Now Press the OK button.Now your list will show only unique values in the list and all the duplicated entries are now hidden.

In this way you can identify Excel Distinct Values.

1.2 Using data tab to identify Excel Distinct Values

You can identify Excel Distinct Values using data tab by following simple below mentioned steps and picture illustrations.

Steps:

1.Open WPS Excel /Spreadsheet file where you want to identify Excel Distinct Values.Go to Data Tab.

2.There will be an advanced filter button in it.

3.Press that advanced filter button.A new window shall appear.Make sure that in the topic section of the window filter the list, in-place is selected.

4.In the List range section on that new window, select the list in which you wish to find unique values by either manually entering the list range or by using the mouse cursor and selecting the range through mouse cursor, Then press Enter.

5.Now mark the Unique records only checkbox at the bottom of the window.Now Press the OK button.

6.Now your list will show only unique values in the list and all the duplicated entries are now hidden.In this way you can identify Excel Distinct Values.

1.3 Using new paste location to identify Excel Distinct Values

You can identify Excel Distinct Values using new paste location by following simple below mentioned steps and picture illustrations.

Steps:

1.Open WPS Excel /Spreadsheet file where you want to identify Excel Distinct Values.

2.Go to Home Tab or Data tab.Click the advanced filter button by following any of the two methods covered in above sections of the article.A new window shall appear.Make sure that in the topic section of the window copy to another location is selected.

3.In the List range section on that new window, select the list in which you wish to find unique values by either manually entering the list range or by using the mouse cursor and selecting the range through mouse cursor, Then press Enter.

4.Now enter the copy to range i.e. the new location where you want to paste all the unique values of your list

5.Now mark the Unique records only checkbox at the bottom of the window.Now Press the OK button.Now your list at new location is generated showing only unique values in the original list.

In this way you can identify Excel Distinct Values in excel list without altering the original list.

In this way you can easily identify Excel Distinct Values in the WPS excel/spreadsheet.

This article has covered the simplest ways on identifying Excel Distinct Values. You can use these simple steps as explained above and easily identify Excel Distinct Values WPS Excel/Spreadsheet whenever required.

Hopefully you have learned How to identify Excel Distinct Values in WPS Excel/Spreadsheet?  If you want to know more about Excel features, you can follow WPS Academy to learn.
You can also download WPS Office to edit the word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience. 

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.