Automatically Fill Adjacent Columns from an Excel UNIQUE Formula
Question details
The user wants formulas in an adjacent column to automatically expand and contract when an Excel UNIQUE formula produces a different number of results.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Column H contains source symbols. Column A displays a sorted list of unique, nonblank symbols, and column B should generate a corresponding result for every symbol without using an Excel Table or named range.
- Observed behavior
- Adding a new symbol, such as Z, expands the UNIQUE results in column A, but ordinary formulas referencing individual cells in column A do not automatically extend into new rows in column B.
Use an Excel version that supports dynamic arrays and UNIQUE, such as Microsoft 365, Excel 2021, or Excel 2024. Keep the output areas below A2 and B2 empty and outside an Excel Table so both formulas can spill.
Reference the entire UNIQUE spill range in the adjacent formula
Use the # spill range operator to make one formula in B2 generate a result for every item returned by A2.
A reference such as A2 points to one cell. A2# points to the entire dynamic array currently spilling from A2, so a compatible array calculation using that reference can resize automatically.
The example below repeats each unique symbol with a space between the two copies. You can replace that calculation with another array-compatible expression that uses A2#.
Click A1 and B1 and enter your preferred headers, such as Unique Symbol and Repeated Symbol. Keep these headers separate from the formulas.
Click A2, enter =SORT(UNIQUE(FILTER(H2:H1000,H2:H1000<>""))), and press Enter. Excel filters out blank source cells, removes duplicates, and sorts the remaining values.
Clear any existing copied formulas or values below B2 in the intended output area. Click B2, enter =A2#&" "&A2#, and press Enter. This single formula spills one result for each item in A2#.
Enter a new symbol, such as Z, in an unused cell within H2:H1000. If Z is not already present, column A gains a unique result and column B expands to match. Remove every occurrence of that symbol to confirm that both output ranges contract.
Work with Excel Workbooks in WPS Spreadsheet
WPS Office is a free, lightweight, easy-to-use alternative for everyday spreadsheet work. Its familiar interface and Excel-format compatibility help you move existing workbooks into WPS Spreadsheet. This solution uses Excel’s dynamic-array behavior, so verify support for UNIQUE and spill references in your WPS version before relying on automatic resizing.
Frequently Asked Questions
What does the # after A2 mean in an Excel formula?
A2# is a spill range reference. It refers to every cell in the dynamic array produced by the formula in A2, rather than only A2 itself. Microsoft documents this feature as the spill range operator; it does not require a named range or an Excel Table.
Why doesn't a formula referencing A2 automatically fill new rows?
A formula such as =A2&" "&A2 references one cell and returns one result. Excel does not automatically copy that ordinary formula when a neighboring spill range grows. Enter =A2#&" "&A2# in B2 to calculate over the entire spilled list instead.
Why does B2 show a #SPILL! error?
Select B2 and check the intended spill area for existing values, formulas, or merged cells. Clear obstructing content or unmerge those cells. If B2 is inside an Excel Table, move the spill formula outside the table.
Why doesn't a new source value appear in the unique list?
Check whether the new value is inside H2:H1000, the source range used in A2. Values below row 1000 are not included unless you extend both source references. A value that already exists also will not add another row because UNIQUE removes duplicates.
What happens if all the source cells are blank?
The supplied FILTER formula returns #CALC! when it finds no nonblank values. To display a blank instead, enter =SORT(UNIQUE(FILTER(H2:H1000,H2:H1000<>"",""))) in A2. This returns a blank result rather than a zero-row array, so B2 may still display the separator space.
