How to How to Use UNIQUE with SORT Functions in Excel in Excel - Practical Guide

Learn to combine UNIQUE and SORT functions to create dynamic lists that automatically remove duplicates and organize data in ascending or descending order. This powerful combination eliminates manual sorting and deduplication, saving time on data cleanup tasks.

Why This Matters

Automating data cleanup and sorting increases efficiency and reduces manual errors in data analysis workflows. This skill is essential for professionals managing large datasets across finance, marketing, and operations.

Prerequisites

•Understanding of basic Excel functions and cell references
•Familiarity with the UNIQUE function
•Knowledge of the SORT function basics
•Excel 365 or Excel 2021+ subscription

Step-by-Step Instructions

Open your workbook and prepare your data

Open Excel and load your dataset containing duplicate entries that need sorting. Ensure data is in a contiguous range (e.g., A2:A100) without blank rows in the middle.

Click the target cell for your formula

Select an empty cell where you want the unique, sorted results to appear (e.g., D2). This cell will display your cleaned and organized data.

Enter the combined UNIQUE and SORT formula

Type the formula: =UNIQUE(SORT(A2:A100)) and press Enter. The SORT function organizes data first, then UNIQUE removes all duplicate entries from the sorted list.

Adjust sort order if needed

To sort descending instead of ascending, modify the formula to: =UNIQUE(SORT(A2:A100,1,-1)) where -1 indicates descending order. Press Ctrl+Shift+Enter for array formula confirmation.

Verify results and apply formatting

Review the output to confirm duplicates are removed and data is sorted correctly. Apply borders via Home > Borders and conditional formatting if needed to highlight results.

Alternative Methods

Using SORT with FILTER for conditional uniqueness

Combine SORT with FILTER to remove duplicates based on specific criteria rather than removing all duplicates. Formula example: =SORT(FILTER(A:A,A:A<>""))

Manual Data > Remove Duplicates method

Navigate to Data > Remove Duplicates and Data > Sort A to Z for older Excel versions without UNIQUE/SORT functions. This approach is slower but works in Excel 2016 and earlier.

Tips & Tricks

✓Always reference your entire data range including headers if applicable; exclude headers from the formula range to prevent them being treated as data
✓Use Ctrl+Shift+Enter in older Excel versions to confirm array formulas, though Excel 365 handles this automatically
✓Test your formula with a small dataset first to verify the output before applying it to large datasets
✓Combine with IFERROR to handle empty ranges gracefully: =IFERROR(UNIQUE(SORT(A2:A100)),"No data")

Pro Tips

★Use the third parameter in SORT to specify sort direction by column number: =UNIQUE(SORT(A2:A100,1,1)) for ascending (1) or descending (-1)
★Nest multiple columns with SORT by adding column references: =UNIQUE(SORT(A2:C100,1,1,2,1)) sorts by column A then column B
★Combine with LET for readability in complex formulas: =LET(data,A2:A100,LET(sorted,SORT(data),UNIQUE(sorted)))
★Use absolute references ($A$2:$A$100) to prevent range changes when copying formulas to other cells

Troubleshooting

Formula returns #NAME? error

This occurs in Excel versions prior to 2021. Upgrade to Excel 365 or use alternative manual methods (Data > Remove Duplicates). Verify your Excel version supports dynamic array functions.

Duplicate values still appear in results

Ensure UNIQUE is the outer function: =UNIQUE(SORT(...)). If duplicates persist, check for extra spaces or formatting differences using TRIM function within UNIQUE.

Formula spills into unwanted cells or causes overflow errors

Delete any content in cells below/right of your formula cell before running it. Excel needs empty space to spill results; alternatively, use a range large enough to accommodate all unique sorted values.

Results show in wrong sort order

Verify your sort parameter: use 1 for ascending (default), -1 for descending in the SORT function's third parameter. Example: =UNIQUE(SORT(A2:A100,1,-1)) for descending.

Related Excel Formulas

SORT UNIQUE FILTER TRIM IFERROR LET

Frequently Asked Questions

Can I use UNIQUE and SORT with multiple columns?

Yes, you can sort by multiple columns by specifying additional column references in SORT. Example: =UNIQUE(SORT(A2:C100,1,1,2,1)) sorts by column A ascending, then column B ascending. UNIQUE will remove duplicate rows based on all columns combined.

What Excel versions support UNIQUE and SORT functions?

UNIQUE and SORT functions are available in Excel 365, Excel 2021, and Excel 2019 (with latest updates). Excel 2016 and earlier do not support these functions; use manual methods or upgrade your version.

How do I handle blank cells in my data range?

Use FILTER to exclude blanks before sorting: =UNIQUE(SORT(FILTER(A2:A100,A2:A100<>""))). This removes empty cells from processing before applying UNIQUE and SORT, ensuring clean results.

Can the formula automatically update when new data is added?

Yes, if you use a dynamic range reference like A2:A1000 or use Table references, the formula will automatically update when new data is added within that range. This makes it ideal for live datasets.

What's the difference between nesting UNIQUE inside SORT vs. outside?

You must nest UNIQUE outside SORT: =UNIQUE(SORT(...)). Nesting SORT inside UNIQUE first sorts the data, then removes duplicates from the sorted list. The reverse order would attempt to sort already-deduplicated data.