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 show unique values in Excel

February 13, 2023
12.4K Views

While working on different lists in excel you need that excel only show unique values WPS Office(2016/2019/mac/online) lists so that you can find out all different/unique/distinct sorts of entries on the 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 excel to only show unique values (2016/2019/mac/online). Once you go through this article you will be able to easily find unique vales in different representation/output ways. Let’s cover the simple methodologies of finding unique values in excel WPS/ (2016/2019/mac/online)

Three different ways to only show unique values in Excel.

1.1 Using Home tab to only show unique values in Excel

You can only show unique values in Excel using home tab by following simple below mentioned steps and picture illustrations.

Steps:

1.Open WPS Excel /Spreadsheet file where you want to only show unique values in Excel.

2.Go to Home Tab.

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

4.A drop down shall appear.

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

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

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

8.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,

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

10.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 only show unique values in Excel list.

1.2 Using data tab to only show unique values in Excel

You can only show unique values in Excel using data tab by following simple below mentioned steps and picture illustrations.

Steps:

1.Open WPS Excel /Spreadsheet file where you want to only show unique values in Excel.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 only show unique values in Excel list.

1.3 Using new paste location to only show unique values in Excel

You can only show unique values in Excel using new paste location by following simple below mentioned steps and picture illustrations.

Steps:

1.Open WPS Excel /Spreadsheet file where you want to only show unique values in Excel.

2.Go to Home Tab or Data tab.

3.Click the advanced filter button by following any of the two methods covered in above sections of the article.

4.A new window shall appear.Make sure that in the topic section of the window copy to another location is selected.

5.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.

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

7.Now mark the Unique records only checkbox at the bottom of the window.

8.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 only show unique values in Excel list without altering the original list.

In this way you can easily only show unique values in Excel in the WPS excel/spreadsheet.

This article has covered the simplest ways on only show unique values in Excel. You can use these simple steps as explained above and easily only show unique values in Excel WPS Excel/Spreadsheet whenever required.

Hopefully you have learned How Excelonly show unique 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.