The fastest way to list items in Excel is to use the =UNIQUE(range) formula to extract distinct values, or simply type your items into a column and use =A1 to copy them elsewhere. For a dynamic list that updates automatically, combine UNIQUE with FILTER or SORT. This article explains the most practical formulas for listing items, how to choose between them, and how to fix common problems.
Why You Need a Formula to List Items
Typing items manually works for a handful of entries, but real spreadsheets grow. You might need to:
- Extract a list of unique customers from a column of thousands of orders.
- Create a drop-down list that automatically includes new entries.
- Pull a list of items that meet a condition (e.g., all products with stock below 10).
- Combine lists from multiple sheets into one master list.
Formulas make these tasks repeatable and error-free. Instead of copying and pasting, you write one formula and Excel recalculates the list whenever the source data changes.
Basic Listing: The Simplest Formula
If you just need to copy a list from one place to another, the equals sign is your friend. For example, with items in A2:A10, put =A2 in cell C2 and drag down. This creates a live link—update A2 and C2 updates. However, this doesn’t remove blanks or duplicates. For a clean list of unique items, use UNIQUE.
Example: With items in A2:A20 (including repeats and blanks), enter =UNIQUE(A2:A20) in cell C2. Excel spills the unique values down automatically. The result is a vertical list of each distinct item, ignoring blanks (by default). This works in Excel 365 and Excel 2021. In older versions, you’d need an array formula or a pivot table.
Listing Unique Items (The UNIQUE Function)
The UNIQUE function is the modern way to list distinct items. Its syntax is:
=UNIQUE(array, [by_col], [exactly_once])
- array – the range or array to extract from (e.g., A2:A100).
- by_col – optional; use
TRUEto compare columns instead of rows (default is FALSE). - exactly_once – optional; use
TRUEto return only items that appear exactly once.
Example: To list items that appear only once in A2:A20, use =UNIQUE(A2:A20,,TRUE). This returns items with a count of 1. If you want the list sorted alphabetically, wrap it: =SORT(UNIQUE(A2:A20)).
Listing Items That Meet a Condition (FILTER)
Sometimes you need a list of items that match criteria, like all products in category “Electronics”. The FILTER function does this:
=FILTER(array, include, [if_empty])
Example: With products in A2:A20 and categories in B2:B20, to list products where B column equals “Electronics”, use =FILTER(A2:A20, B2:B20="Electronics", "None"). This spills the matching items down. The third argument is optional; if omitted, Excel returns a #CALC! error when no matches exist. Use "" to return nothing.
Combine FILTER with UNIQUE to get a distinct list of matching items: =UNIQUE(FILTER(A2:A20, B2:B20="Electronics")).
Listing Items in a Specific Order (SORT and SORTBY)
To list items alphabetically or by another column, use SORT or SORTBY.
=SORT(array, [sort_index], [sort_order], [by_col])– sorts by a column in the same array.=SORTBY(array, by_array1, [sort_order1], ...)– sorts by an external range.
Example: To list unique items in A2:A20 in descending order, use =SORT(UNIQUE(A2:A20), 1, -1). The -1 means descending (largest to smallest for numbers, Z to A for text). To sort a list of products by their sales numbers in C2:C20, use =SORTBY(A2:A20, C2:C20, -1).
Listing Items from Multiple Sheets or Ranges
If your items are scattered across columns or sheets, use VSTACK (Excel 365/2024) to combine them vertically, then apply UNIQUE.
Example: With items in Sheet1!A2:A10 and Sheet2!A2:A10, use =UNIQUE(VSTACK(Sheet1!A2:A10, Sheet2!A2:A10)). This stacks the two ranges and then extracts unique values. For older Excel versions, you’d need to manually combine ranges or use a helper column with IFERROR and INDEX—a more complex approach.
How to Choose the Right Formula
Your choice depends on what you need:
- Just copy a list – use
=A1references. - Unique items – use
UNIQUE. - Filter by condition – use
FILTER. - Sort – use
SORTorSORTBY. - Combine multiple ranges – use
VSTACK(orHSTACKfor side-by-side).
For a list that updates automatically when new items are added, use a table (Insert > Table) and reference the table column. For example, with a table named Table1, =UNIQUE(Table1[Items]) will automatically include new rows added to the table.
Common Problems and How to Fix Them
- #SPILL! error – The result would overwrite existing cells. Clear the cells below the formula or move the formula to a column with empty space.
- #CALC! error –
FILTERreturns no results. Add anif_emptyargument like"No items". - #NAME? error – Your Excel version doesn’t support the function. Use older alternatives like
INDEXandMATCHor a pivot table. - Blanks appearing –
UNIQUEignores empty cells by default, but if you have empty strings (like""from formulas), they appear. UseFILTERto remove them:=FILTER(UNIQUE(A2:A20), UNIQUE(A2:A20)<>"/")(replace “/” with an empty string, but note that""is a valid empty string in Excel). - List not updating – Formulas recalculate automatically, but if calculations are set to manual (Formulas > Calculation Options > Automatic), press F9 to recalculate.
Frequently Asked Questions
How do I create a drop-down list from a formula?
Use the UNIQUE formula to create a dynamic list in a helper column, then go to Data > Data Validation > List and select that range. For example, with =UNIQUE(A2:A20) in C2, set Data Validation to allow a List with source =$C$2# (the # refers to the entire spill range).
Can I list items without duplicates in older Excel?
Yes, but it’s more complex. Use an array formula like =IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)), "") entered with Ctrl+Shift+Enter (in Excel 2019 and earlier). Or use a pivot table: drag the field to Rows, then change the report layout to Tabular and remove subtotals.
How do I list items that appear more than once?
Use UNIQUE with exactly_once set to FALSE (default) to get all distinct items, then use COUNTIF to filter. For example, to list items with count > 1, use =FILTER(UNIQUE(A2:A20), COUNTIF(A2:A20, UNIQUE(A2:A20))>1).
Why does my formula return a #SPILL! error?
Because the result would occupy multiple cells and something is blocking them. Check that the cells below and to the right are empty. Also, if you have a table, ensure the formula is not inside the table—tables don’t support dynamic arrays in the same way.
Can I use these formulas in Google Sheets?
Yes, UNIQUE, FILTER, SORT, and SORTBY exist in Google Sheets with similar syntax. However, VSTACK and HSTACK are not available; use {range1; range2} with semicolons to stack vertically, or {range1, range2} for horizontal.
Conclusion
Listing items in Excel is straightforward with modern functions. Start with UNIQUE for distinct values, add FILTER for conditions, and wrap with SORT for order. For older versions, fall back on pivot tables or array formulas. Always test your formulas with a small sample to ensure they behave as expected.
Related
- Excel Flexi Timesheet Template UK: How to Build One
- Excel First Letter Uppercase Rest Lowercase?
- Excel Eye Center Provo: Services, Doctors & What to Know
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Rank Formula Without Duplicate Ranks: ExamplesTo give tied values different, consecutive ranks in Excel, enter =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1 beside the first value and fill down.…
- How to Merge Two Sheets in Excel with VLOOKUP FormulasTo merge information from two Excel sheets by a shared ID, add a column to the sheet you…
- Excel IF ISBLANK Examples: Formulas and FixesTo make Excel display one result when a cell is empty and another when it is not, combine…
- Excel Date Difference in Days: Formulas and ExamplesTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- Excel Percentage of Cells with Text: 2 FormulasThe quickest way to find the percentage of cells containing text in Excel is to use =COUNTIF(range,"*")/COUNTA(range) for…
- Excel CLEAN Function: Remove Nonprinting CharactersTo remove nonprinting characters from text in Excel, enter =CLEAN(A2) in a helper cell when the source text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.