UNIQUE Function Not Working in Excel [Fix]

The UNIQUE function in Microsoft Excel is one of the most useful dynamic array functions for extracting distinct values from a dataset. It helps users remove duplicates automatically without manually filtering data. However, many users run into problems where the UNIQUE function is not working in Excel. The formula may return an error, show incorrect results, fail to spill values into cells, or not work at all.

This guide explains every working solution so you can quickly restore the UNIQUE function in Excel and continue working without interruptions.

In This Guide View

Why the UNIQUE Function Stops Working in Excel

The UNIQUE function only works correctly in newer versions of Excel that support dynamic arrays. If you are using older Excel editions, the formula will either display an error or appear as plain text. In some situations, the function works partially but cannot expand results because nearby cells are occupied.

You may also face issues due to incorrect references, merged cells, hidden formatting problems, corrupted workbook data, or compatibility mode restrictions.

UNIQUE function not working in Excel [Fix]

If the UNIQUE function is not working in Excel, follow these quick fixes:

Check whether your Excel version supports dynamic array formulas.
Verify the UNIQUE formula syntax.
Clear blocked cells in the spill range.
Remove extra spaces and hidden characters from data.
Enable Automatic Calculation mode.
Save the workbook in modern Excel format.
Repair Microsoft Office if the issue continues.

1: Check Whether Your Excel Version Supports the UNIQUE Function

The UNIQUE function is only available in modern versions of Excel, including:

Windows Excel 365
Excel 2021
Excel for the web
Excel for Mac with a Microsoft 365 subscription

If you are using older versions such as Excel 2016 or Excel 2019 without dynamic array support, the function will not work.

To check your Excel version, open Excel > go to File > Account > check the information under About Excel.

If your version does not support the UNIQUE function, you have two options. You can either upgrade to Microsoft 365 or use older alternatives like Remove Duplicates, Advanced Filter, or Pivot Tables.

Many users discover that the formula problem is simply caused by an unsupported version.

2: Verify the UNIQUE Formula Syntax

Sometimes the UNIQUE function fails because of incorrect formula syntax. Even a small typing mistake can stop the formula from working properly.

The correct syntax is:

=UNIQUE(array,[by_col],[exactly_once])

Here is a simple working example:

=UNIQUE(A2:A20)

This formula extracts unique values from cells A2 through A20.

To verify your formula, open Excel > select the formula cell > click inside the formula bar > compare the formula carefully.

Common mistakes include:

Using incorrect brackets
Adding extra commas
Using invalid cell references
Typing UNIQUE incorrectly
Using semicolons instead of commas in some regional settings

If you copied the formula from a website, manually retype it to avoid hidden formatting issues.

After correcting the formula, press Enter and check whether the results spill properly.

3: Clear the Spill Range Blocking the Formula

The UNIQUE function uses dynamic arrays, meaning Excel automatically fills multiple cells with results. If another value blocks the output area, the formula displays a #SPILL! error.

This is one of the most common reasons why the UNIQUE function appears broken.

To fix this issue, open Excel > select the cell containing the UNIQUE formula > look for the highlighted spill range border.

If any cell inside the spill area already contains:

Text
Numbers
Spaces
Hidden characters
Merged cells

Excel cannot expand the results.

Delete all content inside the highlighted spill range. After clearing the cells, the UNIQUE formula should automatically populate the list.

If merged cells are causing the issue, go to Home > Merge & Center > select Unmerge Cells.

Always leave enough space below or beside the formula, depending on the direction of the spill.

4: Remove Extra Spaces and Hidden Characters from Data

The UNIQUE function may return duplicate-looking values because of hidden spaces or invisible characters in your dataset.

For example:

Apple
Apple, followed by a hidden space

Excel treats them as different values even though they appear identical.

To clean the data, open Excel > select an empty column > enter the following formula:

=TRIM(A2)

Press Enter and drag the formula down. The TRIM function removes extra spaces from the text. If your data contains hidden non-printable characters, use:

=CLEAN(A2)

You can also combine both functions:

=TRIM(CLEAN(A2))

After cleaning the data, copy the cleaned column > right click > choose Paste Special > select Values.

Now apply the UNIQUE function again. The duplicates should disappear correctly.

5: Enable Automatic Calculation in Excel

Sometimes Excel formulas stop updating because the calculation mode is set to Manual.

When this happens, the UNIQUE function may appear frozen or display outdated results.

To enable automatic calculations, open Excel > go to Formulas > select Calculation Options > choose Automatic. After enabling automatic calculation:

Press Ctrl + Alt + F9 to force a full workbook recalculation. This refreshes all formulas and dynamic arrays instantly.

If the UNIQUE results update correctly afterward, the issue was caused by disabled calculations.

6: Check Whether the Workbook Is in Compatibility Mode

Excel workbooks saved in older formats, such as .xls, may disable modern functions, including UNIQUE.

Compatibility mode limits newer Excel features to maintain support for older versions.

To check this issue, open Excel > look at the top title bar. If you see Compatibility Mode, convert the workbook to a newer format.

Go to File > Save As > choose a location > select Excel Workbook (*.xlsx) > click Save. After saving the file in modern format, close and reopen the workbook. Now test the UNIQUE function again.

Many users solve the issue immediately after converting the workbook.

7: Ensure Cells Are Formatted Correctly

If Excel treats your formula as plain text instead of calculating it, the cell format may be incorrect.

To fix this problem, open Excel > select the formula cell > go to Home > open the Number Format dropdown.

If the cell is formatted as Text, change it to General. After changing the format: Double-click the formula cell, Press Enter.

Excel should now process the UNIQUE function properly. You should also make sure there is no apostrophe before the formula. For example:

‘=UNIQUE(A2:A20)

The apostrophe forces Excel to display the formula as text. Remove it and press Enter again.

8: Disable Legacy Array Formula Behavior

Older Excel users sometimes press Ctrl + Shift + Enter when entering formulas. This creates legacy array formulas that may interfere with dynamic arrays like UNIQUE.

The UNIQUE function only requires a normal Enter key press.

To fix this issue, select the formula cell > click inside the formula bar > remove the formula completely. Now re-enter the formula manually and press only Enter.

Avoid using Ctrl + Shift + Enter with dynamic array functions.

9: Update Microsoft Excel

An outdated version of Excel can contain bugs affecting dynamic array functions.

Microsoft frequently releases updates that improve formula compatibility and fix calculation issues.

To update Excel, open Excel > go to File > Account > select Update Options > click Update Now. Allow Excel to download and install the latest updates.

After updating:

Restart the computer
Open the workbook again
Test the UNIQUE formula

Updated builds often resolve unexpected formula behavior.

10: Repair Microsoft Office Installation

If none of the solutions work, your Office installation may be corrupted.

Repairing Microsoft Office can fix damaged components affecting Excel formulas.

To repair Office, open Windows Settings > go to Apps > Installed Apps > locate Microsoft Office > click Modify. Choose Quick Repair first.

If the issue continues, repeat the process and select Online Repair.

After the repair completes:

Restart the computer
Launch Excel again
Check whether the UNIQUE function works properly

This process fixes many hidden Excel-related issues automatically.

Tips to Prevent UNIQUE Function Errors

Always keep enough empty cells around dynamic array formulas.

Avoid mixing old and new Excel formats in the same workbook.

Clean imported data before applying formulas.

Use structured tables whenever possible because they improve formula reliability.

Keep Excel updated regularly to maintain compatibility with modern functions.

FAQs

Why does Excel show #NAME? error for the UNIQUE function?

The #NAME? An error usually means your Excel version does not support the UNIQUE function. Upgrade to Excel 365 or Excel 2021 to use dynamic array formulas.

Why is the UNIQUE function showing duplicate values?

Hidden spaces or invisible characters inside cells can make values appear identical while Excel treats them differently. Use the TRIM and CLEAN functions to fix the dataset.

What causes the #SPILL! error in UNIQUE function?

The #SPILL! error appears when cells in the output range are blocked by existing content or merged cells. Clear the spill area to solve the issue.

Can the UNIQUE function work in Excel 2016?

No. Excel 2016 does not fully support dynamic array functions like UNIQUE unless specific Microsoft 365 updates are available.

Why is the UNIQUE formula not updating automatically?

Automatic calculation mode may be disabled. Enable it by going to Formulas > Calculation Options > Automatic.

How do I remove duplicates if the UNIQUE function is unavailable?

You can use Remove Duplicates, Advanced Filter, or Pivot Tables as alternatives in older Excel versions.