Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook Twitter Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home»Software»Excel»How to Find Unique Values in Excel

How to Find Unique Values in Excel

Asmi DhakalBy Asmi DhakalJanuary 15, 2023
how to find unique values in

You may encounter several redundant values, especially when compiling data from multiple users. Excel offers numerous features you can use to find unique values from your data set.

You must use different methods depending on if you want to straight-up remove duplicate values or filter unique entries from the repeated values. 

Use Advance Filter

You can configure the Filter tool to show you only unique values. Excel lets you choose between pasting the unique entries in the same location or transferring them to a different location in the grid. This way, you can make a copy of your values with only unique values. 

  1. Open your workbook and select your data range.
  2. Head to the Data tab.
  3. In the Sort & Filter section, click on Advanced.
    Advanced Filter Tool
  4. Choose one of these options depending on your preferences:
  • Filter the list, in-place: To remove all duplicate values from the list.
  • Copy to another location: To paste the unique values in the location you specify in the Copy to section.
    Filter Duplicate Records Excel
  1. Select the box next to Unique records only > OK.

Using UNIQUE Function

Excel has a special function called the UNIQUE function you can use to only paste unique entries from the cell range. This function is, however, limited to Office 365 and the web version of MS Excel.

You can use the UNIQUE function in the following format to retrieve unique entries from your range:

=UNIQUE(range)

UNIQUE function Excel

For example, we’ve made six entries in the range A1:A6. We entered the =UNIQUE(A1:A6) formula in cell C3. When we hit enter, it returned the four unique values.

Using Power Query

You can use Power Query to only keep unique data on your Excel sheet. Power Query however does not clear repeated values in different cases. For example, if your range contains Apple and apple, Power Query will not eliminate any of these values as the tool view them as unique entries.

  1. Select your range in the Excel grid.
  2. Head to Insert > Table.
    Insert Table Excel
  3. Select Data from the menubar.
  4. In the Get & Transform section, click From Table.
    New Query from Table Excel
  5. Right-click on the header and select Remove Duplicates.
    Remove Duplicates Power Query Excel
  6. From the home tab, select the flyout menu for Close & Load > Close & Load To.
    Close and Load to Power Query
  7. Configure how you want to view your data and where you want to paste your new data.
  8. Click Load.
    Power Query Load to
how-to
Asmi Dhakal
  • Twitter
  • LinkedIn

Asmi Dhakal is a Tech Writer specializing in Microsoft Office-based programs at TechNewsToday. She aims to use her experience to help others learn their way around its programs. Asmi’s interest in Office products grew when she first entered the marketing industry. While she initially struggled to understand their application, she eventually understood her way around them. In her articles, she exhibits a strong understanding of related issues and attempts to push only relevant fixes. Over time, she has grown a particular fondness for MS Excel and spends her free time learning Visual Basic Analysis (VBA). She’s always on the lookout for productivity tools that can get the job done quicker in Excel. Her approach to related problems is very humane, helping new users not only fix the issue but learn more about its nature. Asmi also heavily indulges in personal writing. She is a disciplined journal writer and has maintained a regular journal for 8 years! When her schedule allows, she works to rehabilitate street animals. You can contact her at asmi@technewstoday.com

Related Posts

check mark in excel

How to Put Check Mark in Excel

January 20, 2023
best excel shortcuts

Best 52 Excel Shortcuts Only Experts Know

January 19, 2023
How to Find and edit Hyperlinks on Excel

How to Find and Edit Hyperlinks on Excel

January 23, 2023
how to find links in excel

How to Find and Edit External Links in Excel

January 13, 2023
reference isn't valid excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

January 6, 2023
how to set print area in excel

How to Set Print Area in Excel

January 5, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
Best-1200W-PSU

8 Best 1200W PSUs for Extreme PC Builds in 2023

May 16, 2023
Best-1000W-PSU

10 Best 1000W PSUs in 2023

May 16, 2023
best-700w-psu

10 Best 700W PSUs for Gaming in 2023

May 14, 2023
You may also like
antimalware service executable high memory

Fix: Antimalware Service Executable High Memory

May 18, 2023
printer-printing-pink

Why is My Printer Printing Pink? 6 Ways to Fix It

May 18, 2023
how to clean usb c port

How to Clean USB-C Port Safely

May 17, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook Twitter Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday, editor@technewstoday.com | Tech Central Pvt. Ltd.

Type above and press Enter to search. Press Esc to cancel.