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

August 9, 2022
695 Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

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.