Excel Formula for Listing Items: 5 Real Methods

Formulas & functions · Updated November 22, 2026

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 TRUE to compare columns instead of rows (default is FALSE).
  • exactly_once – optional; use TRUE to 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 =A1 references.
  • Unique items – use UNIQUE.
  • Filter by condition – use FILTER.
  • Sort – use SORT or SORTBY.
  • Combine multiple ranges – use VSTACK (or HSTACK for 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 – FILTER returns no results. Add an if_empty argument like "No items".
  • #NAME? error – Your Excel version doesn’t support the function. Use older alternatives like INDEX and MATCH or a pivot table.
  • Blanks appearing – UNIQUE ignores empty cells by default, but if you have empty strings (like "" from formulas), they appear. Use FILTER to 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.