Use UNIQUE Results as Horizontal Column Headers in Excel
Question details
The user needs unique values from a source range displayed horizontally as column headers.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A list of values needs to supply unique headings across columns rather than down rows.
- Observed behavior
- The UNIQUE function returns a vertical list, while the desired result is a horizontal row of unique values.
Identify the source range and choose a blank cell with enough empty columns to its right for the results. Make sure your Excel version supports UNIQUE and dynamic array formulas.
Combine TRANSPOSE and UNIQUE in Excel
Use one formula to spill distinct values from a vertical range across columns.
Click the blank cell where you want the first unique header to appear, outside any Excel table.
In the formula bar, enter =TRANSPOSE(UNIQUE(A2:A100)) and press Enter. Replace A2:A100 with your source range.
Look across the row to confirm the unique values have filled adjacent cells. If Excel shows #SPILL!, clear any cells blocking the output.

Create horizontal unique headers in WPS Spreadsheet
Use Remove Duplicates and Paste Special in WPS Spreadsheet to turn a source list into a row of unique headers. This manual method creates static values; repeat it when the source data changes.
- 1. Open the spreadsheet: Open WPS Spreadsheet and select File > Open to load your workbook.
- 2. Copy the source values: Select the source cells, copy them, and paste them into an empty temporary column.
- 3. Remove duplicate values: Select the temporary list and use Data > Remove Duplicates to keep one instance of each value.
- 4. Paste the headers horizontally: Copy the resulting list, select the first destination header cell, and use Paste Special > Transpose to paste the values across the row.

Frequently Asked Questions
What formula displays unique values horizontally in Excel?
Enter =TRANSPOSE(UNIQUE(A2:A100)) in the first destination cell, replacing A2:A100 with your source range.
Will the horizontal results update when the source values change?
Yes. In a supported version of Excel, the formula recalculates when values in the referenced range change. Values added outside that range are not included unless you expand the reference.
Why does my formula show a #SPILL! error?
The destination cells may contain data or the formula may be inside an Excel table. Clear the cells to the right and place the formula outside the table.
Can the spilled values become actual Excel table headers automatically?
A spilled formula cannot be placed inside an Excel table. To use its results as actual table headers, copy the spilled cells and paste them as values into the table header row.




