Transpose Data using Power Query in Excel

by

To turn rows into columns, use Paste Special → Transpose for a one-off, the TRANSPOSE function for a live copy, or Power Query for a version that refreshes and can be cleaned on the way.

One-offPaste Special → Transpose
Live formula=TRANSPOSE(B2:G4)
RefreshablePower Query → Transpose

The data

Data to transpose

Power Query

  1. Click in the data → Data → From Table/Range → OK. The first row becomes the headers.
  2. Transform → Use First Row as Headers ▾ → Use Headers as First Row, so the headers move back into the data.
  3. Transform → Transpose.
  4. Transform → Use First Row as Headers to promote the new first row.
  5. Home → Close & Load To → pick a cell.
Data loaded into Power Query
Use Headers as First Row
Transpose button on the Transform tab
Transposed data with first row promoted to headers
Import Data dialog
Transposed data loaded into Excel

Right-click the result → Refresh after changing the source.

Refreshing the transposed table

TRANSPOSE function

=TRANSPOSE(B2:G4)

In Excel 365 and 2021 it spills automatically. In older versions, select the target range first (the right size, rotated), type the formula and press Ctrl + Shift + Enter. See TRANSPOSE.

Paste Special

Copy the data, right-click the target cell → Paste Special → Transpose. The result is fixed values.

Use Power Query when the data changes regularly, and TRANSPOSE when you want the copy to follow edits immediately.

Related