Function Problems

Use UNIQUE Results as Horizontal Column Headers in Excel

Natalie TaylorNatalie Taylor Nov 18, 2026 868 views

Question details

The user needs unique values from a source range displayed horizontally as column headers.

Use UNIQUE Results as Horizontal Column Headers in Excel
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.
Before you start

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.

Solution 1Recommended

Combine TRANSPOSE and UNIQUE in Excel

Use one formula to spill distinct values from a vertical range across columns.

1
Select the header cell

Click the blank cell where you want the first unique header to appear, outside any Excel table.

2
Enter the formula

In the formula bar, enter =TRANSPOSE(UNIQUE(A2:A100)) and press Enter. Replace A2:A100 with your source range.

3
Check the spill range

Look across the row to confirm the unique values have filled adjacent cells. If Excel shows #SPILL!, clear any cells blocking the output.

Combine TRANSPOSE and UNIQUE in Excel
Using an Excel table: Spilled array formulas cannot reside inside an Excel table. Place the formula outside the table; if you need actual table headers, copy the results and paste them as values into the header row.
WPS Spreadsheet

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. 1. Open the spreadsheet: Open WPS Spreadsheet and select File > Open to load your workbook.
  2. 2. Copy the source values: Select the source cells, copy them, and paste them into an empty temporary column.
  3. 3. Remove duplicate values: Select the temporary list and use Data > Remove Duplicates to keep one instance of each value.
  4. 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.
Create a horizontal row of unique headers without a formulaOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet editing toolsWork with a free, lightweight office suite
microsoft office alternative - wps office

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.