Excel Formula to List Items in a Column

Formulas & functions · Updated November 22, 2026

The simplest Excel formula to list items in a column is =A1 entered in the first cell of the target column, then dragged down to copy each item from the source column. For a dynamic list that automatically updates when you change the source data, use =A1# (spill range) if you have Microsoft 365, or an array formula like =IFERROR(INDEX(A:A,ROW())) for older versions.

Why You Might Need This Formula

You often need to list items from one column into another location—perhaps to reorder them, to extract unique values, to combine data from multiple sheets, or to create a dropdown list. A simple reference formula copies the value exactly, but more advanced formulas let you filter, sort, or remove blanks automatically.

Basic Formula: Direct Cell Reference

If your items are in column A (say A1:A10) and you want them listed in column C starting at C1:

  • In C1, type =A1 and press Enter.
  • Select C1, then drag the fill handle (the small square at the bottom-right corner) down to C10.

This copies the values. If you later change A1, C1 updates automatically. However, if you insert or delete rows in column A, the references adjust accordingly—which can be helpful or annoying depending on your goal.

Dynamic Spill Formula (Microsoft 365)

In Excel for Microsoft 365, you can use a spill range to list a whole column dynamically. With items in A1:A10, in C1 type:

=A1:A10

Press Enter. Excel fills C1:C10 automatically with the values. If you add a value in A11, the spill expands to include it. To reference the entire column (including blanks), use =A:A, but that will spill zeros for empty cells—not ideal.

Listing Unique Items (Remove Duplicates)

To list only unique items from a column, use the UNIQUE function (Excel 365/2021). With items in A2:A20, in C2 type:

=UNIQUE(A2:A20)

This spills a list of unique values down column C, automatically ignoring blanks if you use =UNIQUE(A2:A20,TRUE) (the second argument TRUE means return only unique rows, but for a single column it’s the same). For older Excel, you’d need a complex array formula or a pivot table.

Filtering Out Blanks

If your source column has blank cells and you want a compact list without gaps, use the FILTER function (Excel 365). With data in A2:A20, in C2:

=FILTER(A2:A20,A2:A20<>"")

This spills only non-blank cells. To also remove duplicates, combine with UNIQUE: =UNIQUE(FILTER(A2:A20,A2:A20<>"")).

Using INDEX and ROW for Older Excel

If you don’t have Microsoft 365 or Excel 2021, you can use an array formula to list items while skipping blanks. With data in A2:A20, enter this in C2 and press Ctrl+Shift+Enter (CSE) in older Excel (in Excel 2021 and 365, it works as a dynamic array without CSE):

=IFERROR(INDEX($A$2:$A$20,SMALL(IF($A$2:$A$20<>"",ROW($A$2:$A$20)-ROW($A$2)+1),ROW(A1))),"")

Drag down until you see empty cells. This formula checks each cell, collects row numbers of non-blank cells, and returns the k-th smallest. It’s powerful but complex; for most users, the modern functions are easier.

Common Problems and Solutions

  • Spill range error (#SPILL!): If something blocks the spill area (like text in C2 when you try to spill from C1), clear the blocking cells. The error message tells you which cell is in the way.
  • #VALUE! error with INDEX formula: This usually means you forgot to press Ctrl+Shift+Enter in older Excel, or your range references are incorrect.
  • Blanks showing as 0: When referencing entire columns with =A:A, empty cells return 0. Use =IF(A:A="","",A:A) to show blanks, but that spills zeros for truly empty cells—better to use FILTER.
  • Dragging formula changes references: If you drag a simple =A1 down, it becomes =A2, etc. That’s usually what you want. If you need absolute references, use $ signs.

Alternatives to Formulas

Sometimes a formula isn’t the best tool. You can use Copy and Paste (Ctrl+C, Ctrl+V) for a static list, or Paste Special > Values to remove formulas. For unique lists, a PivotTable is a quick no-formula method: drag the field to Rows and it shows unique items. Power Query (Get & Transform) can also extract unique values and refresh automatically, but it’s more advanced.

Frequently Asked Questions

How do I list items from a column in reverse order?

Use the SORTBY function with a sequence: =SORTBY(A2:A20,ROW(A2:A20),-1) in Excel 365. This sorts by row number descending, effectively reversing the list.

Why does my spill formula show #SPILL! error?

The spill range is blocked. Check the cells below where you entered the formula—if any contain data, clear them. Also ensure you’re not using a table with structured references that might conflict.

Can I list items from multiple columns into one?

Yes, use VSTACK (Excel 365/2024) to combine columns: =VSTACK(A2:A10,B2:B10). For older Excel, use a complex INDEX formula or copy-paste manually.

How do I list items that meet a condition (like only “Yes” in another column)?

Use FILTER with a condition. With items in A2:A20 and criteria in B2:B20, =FILTER(A2:A20,B2:B20="Yes") lists only items where B is “Yes”.

What’s the difference between =A1 and =A1#?

=A1 returns a single value. =A1# is used to reference a spill range that starts at A1 (like if A1 contains a formula that spills). It’s not for referencing a column directly; you’d use =A1:A10 for a range.

Is there a way to list items without formulas?

Yes, use the Remove Duplicates command (Data > Remove Duplicates) for a one-time unique list, or a PivotTable for a dynamic unique list without formulas.

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.