Why the UNIQUE Function Is Missing in Excel 2019 & How to Fix It
Question details
The user is attempting to use the UNIQUE function to extract distinct values from a dataset, but the function is unavailable or returning an error.
- Product
- Microsoft Excel 2019
- Device & OS
- not provided
- Scenario
- Generating a distinct list of items from a column using a built-in formula.
- Observed behavior
- Excel 2019 returns a #NAME? error because the UNIQUE function and dynamic arrays are not supported in this perpetual version of Office.
Verify your exact Excel version by navigating to File > Account. This confirms whether you are running the perpetual Excel 2019 release or a Microsoft 365 subscription.
Use the Advanced Filter to Extract Unique Values
Since Excel 2019 does not support the UNIQUE function natively, you can use the built-in Advanced Filter tool to extract distinct records without relying on complex formulas.
The Advanced Filter is a legacy feature available in all versions of Excel. It allows you to quickly deduplicate a list and copy the unique values to a new location in just a few clicks.
Click and drag to highlight the column or specific data range from which you want to extract unique values.
Navigate to the 'Data' tab on the top ribbon and click on 'Advanced' located in the 'Sort & Filter' group.
In the Advanced Filter dialog box, select the 'Copy to another location' radio button. Click inside the 'Copy to' box and select a destination cell on your worksheet.
Check the box at the bottom that says 'Unique records only', then click 'OK'. Excel will immediately paste the deduplicated list into your chosen destination.
Create an Alternative Formula with INDEX and MATCH
If you need a dynamic solution that updates automatically when data changes, you can combine INDEX, MATCH, and COUNTIF to replicate the UNIQUE function in Excel 2019.
Use Excel for the Web
Microsoft offers a free web-based version of Excel that includes modern dynamic array functions, including the UNIQUE function.
Experience Modern Spreadsheet Features with WPS Office
Frustrated by missing functions in perpetual Office licenses like Excel 2019? WPS Office is a free, lightweight, and highly compatible alternative that provides regular feature updates without the need for expensive subscriptions.
- 1. Download the software: Visit the official WPS website and download the free WPS Office installation package.
- 2. Install WPS Office: Run the downloaded installer and follow the simple on-screen instructions to set it up on your device.
- 3. Open your spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx files to enjoy advanced features and seamless file compatibility.
Frequently Asked Questions
Which versions of Microsoft Excel support the UNIQUE function?
The UNIQUE function is a dynamic array formula available only in Microsoft 365 subscriptions, Excel 2021, Excel 2024, and the free Excel for the Web. It is not supported in Excel 2019 or earlier versions.
Why does my Excel 2019 show a #NAME? error when I type =UNIQUE()?
The #NAME? error occurs because Excel 2019's calculation engine does not recognize the function name. It lacks the internal architecture required to process dynamic arrays introduced in newer software builds.
Will Microsoft ever add dynamic array functions to Excel 2019 via an update?
No. Excel 2019 is a perpetual (one-time purchase) license. Microsoft only provides security patches and bug fixes for perpetual licenses; new features are reserved for Microsoft 365 subscribers and newer standalone releases like Excel 2021.
