How to Transpose a UNIQUE Formula Spill from Vertical to Horizontal in WPS Office Spreadsheets
Question details
The user wants to change the default vertical spill from a UNIQUE formula so unique values are listed horizontally across a row instead of down a column.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a vertical data range that contains duplicates and needs a horizontal unique list for a report layout or further row-based formulas.
- Observed behavior
- A UNIQUE formula normally spills results vertically, but the desired output is a single-row horizontal spill.
Open the workbook in WPS Office Spreadsheets and confirm the source column range, for example A2:A100. Also make sure the destination row has enough empty cells to the right for the expected unique values so the spill range can expand.
Use =TOROW(UNIQUE(range)) to display unique values horizontally
Use this if your WPS Office Spreadsheets version supports TOROW; it returns the unique values in one row from left to right with a single formula.
TOROW scans the array produced by UNIQUE and places all values into a single row. This is useful when the final layout is horizontal and you want the formula to remain dynamic.
Click the cell where the horizontal list should start, such as C1.
Enter =TOROW(UNIQUE(A2:A100)) and press Enter.
Confirm the unique values are displayed from left to right and that enough blank cells are available for the full spill.
Use =TRANSPOSE(UNIQUE(range)) when TOROW is unavailable
If your WPS version returns a #NAME? error for TOROW, use TRANSPOSE to switch the vertical unique array to horizontal.
Create a horizontal unique list with TOROW and UNIQUE in WPS Office Spreadsheets
Use the dynamic array formula =TOROW(UNIQUE(range)) in WPS Office Spreadsheets to turn a vertical spill into a clean, single-row list of unique values without Power Query, pivot tables, or manual copy-paste.
- 1. Open your workbook in WPS Office Spreadsheets: Start WPS Office and open the .xlsx file that contains the column data for UNIQUE.
- 2. Click the cell for the horizontal result: Select the first cell of the destination row, such as C1, and confirm there are blank cells to its right for the spill.
- 3. Enter the dynamic array formula: Type =TOROW(UNIQUE(A2:A100)) and press Enter, replacing A2:A100 with your actual source range.
- 4. Verify the horizontal spill: Check that unique values appear from left to right and update automatically when source values change.
Frequently Asked Questions
Why does UNIQUE spill vertically by default?
UNIQUE returns one value per row when the source data is in a vertical column, so WPS Office Spreadsheets and Excel spill the result downward. To display the result horizontally, wrap UNIQUE in TOROW or TRANSPOSE.
What is the difference between TOROW and TRANSPOSE for a UNIQUE formula?
TOROW scans the array row by row and returns a single row. TRANSPOSE swaps rows and columns. For a one-column UNIQUE result they often look similar, but TOROW is the direct horizontal-array function.
What can I do if WPS Office does not recognize TOROW?
Update WPS Office to a version that supports TOROW, or use =TRANSPOSE(UNIQUE(A2:A100)) as a compatibility formula. In non-dynamic versions, select enough destination cells and enter it with Ctrl+Shift+Enter if required.
How do I avoid a #SPILL! error when wrapping UNIQUE in TOROW?
Make sure the destination row has enough blank cells to the right for every unique value, and remove any merged cells or existing data that would block the spill range.
Does this formula work in Microsoft Excel format files?
Yes, WPS Office Spreadsheets can open and save .xlsx workbooks. The formula result can be shared with Microsoft Excel users, and it recalculates when the file is opened or data changes.
