To turn a vertical list into a horizontal row in Excel for Microsoft 365, enter =TRANSPOSE(A2:A6) in the cell where the row should begin, replacing A2:A6 with your source range. Excel spills the values across adjacent cells, keeping them linked to the original list.
The quickest way to transpose a list
Suppose your values are in A2:A6 and you want the horizontal result to start in B2. Select B2, type the formula, and press Enter. In current Microsoft 365, the five results spill from B2 through F2 automatically. The source cells remain vertical; the formula creates a horizontal view of them elsewhere in the worksheet.
TRANSPOSE changes the orientation of a range: rows become columns, and columns become rows. It does not sort, rearrange, or otherwise change the order of the values. If A2:A6 contains Red, Blue, Green, Yellow, and Purple, the result across B2:F2 is Red, Blue, Green, Yellow, and Purple in that same order.
Choose an empty area for the result. A spilled formula needs enough unoccupied cells to display every output, and the formula itself goes only in the top-left destination cell. Do not copy the formula across five cells in a current version of Excel; entering it once is enough.
How the TRANSPOSE formula works
The syntax is TRANSPOSE(array), where array is the range or array of values to rotate. A vertical range with five rows and one column becomes one row with five columns. Conversely, a range with three rows and two columns becomes two rows and three columns. The number of cells stays the same; only their arrangement changes.
With a dynamic array version of Excel, the formula returns a spilled array. The cell containing the formula is the anchor, and the other results appear in the cells to its right or below it as needed. Excel outlines the spill range when you select the anchor. The output cells are controlled by that single formula rather than by separate formulas in each cell.
Because the formula refers to the original range, edits to source values update the displayed row. If you change A4, the corresponding transposed result changes too. This is useful for summaries and layouts that need to stay synchronized, but it also means the result is not a separate, fixed copy.
Worked example with a vertical list
Assume A2:A6 contains five product names, one per cell: Notebook, Pen, Folder, Ruler, and Marker. To display those names in order from left to right beginning in C2, enter the following in C2:
With product names in A2:A6 and the destination beginning in C2, use =TRANSPOSE(A2:A6). The result is Notebook in C2, Pen in D2, Folder in E2, Ruler in F2, and Marker in G2.
There is no need to include the destination range in the formula. The source range determines the number of output cells. If the list later grows, however, the formula does not automatically guess that you meant to include newly added rows outside A2:A6. You can extend the reference to include them, or use an Excel Table and refer to its data column when you want a reference that expands with the table.
For example, if the source range should include rows 2 through 20, select a clear destination cell and use =TRANSPOSE(A2:A20). This returns 19 values across a row. Check the source boundaries carefully: including a header or an unwanted total will transpose it too.
Transpose a list in older Excel versions
Excel versions without dynamic array behavior require you to select the full output range before entering TRANSPOSE. For a five-cell vertical source such as A2:A6, first select five horizontal cells, such as B2:F2. Type the formula while that whole range is selected, then confirm it as an array formula with Ctrl+Shift+Enter on Windows. In older Mac editions, the equivalent array-entry shortcut is Control+Shift+Return.
With the source in A2:A6 and B2:F2 selected as the five-cell destination, enter =TRANSPOSE(A2:A6) and confirm it using the array-entry shortcut for your Excel version. Excel places the result in the selected cells. In legacy versions, the formula bar may show braces around the formula to indicate an array formula; do not type those braces yourself.
If you select too few output cells, Excel cannot show the complete result; if you select too many, the extra cells may display errors or blanks depending on the version. The source and destination must have matching cell counts. If you are unsure which Excel generation is installed, try entering TRANSPOSE in one destination cell. If it spills automatically, use the modern method; if it does not, consult the array-formula method for that version.
Use Paste Special for a fixed, unlinked row
A formula is not always the right choice. If you only need a one-time conversion and do not want the horizontal copy to change when the source changes, use Paste Special. Select and copy the vertical range, click the upper-left destination cell, then choose Home > Paste > Transpose. Excel pastes the values horizontally without leaving a TRANSPOSE formula linked to the source.
Paste Special is useful when preparing a final report, moving data between sheets, or sending a workbook in which the original source range will be deleted. It generally copies the selected cells rather than creating a live relationship. If you need to retain formulas or specific formatting, inspect the result: paste behavior can depend on what the source cells contain and which paste option you choose.
Use the formula when you expect the source to change and want the row to stay current. Use Paste Special when you want a snapshot. Both methods preserve the order of the selected cells, so neither will sort the list or remove duplicates.
Handle blanks and choose the source range
TRANSPOSE carries blank cells in the source range into corresponding positions in the output. That is often desirable because it preserves the source layout. For instance, if A2:A6 contains a value, a blank, then three more values, the transposed row retains an empty position in the second spot rather than shifting later values left.
If your aim is to omit empty cells and pack the remaining values together, Microsoft 365 can combine FILTER with TRANSPOSE. With the list in A2:A10 and the output beginning in B2, use =TRANSPOSE(FILTER(A2:A10,A2:A10<>"")). FILTER keeps entries that are not empty, and TRANSPOSE turns that filtered result into a row. If every source cell is empty, FILTER may return an error unless you provide an optional fallback value.
Be clear about what counts as blank. A cell containing a formula that returns an empty text value is not necessarily the same as a truly empty cell for every Excel operation. The not-equal-to-empty-text test shown above excludes both many visually empty formula results and genuinely empty cells. If you need to preserve positions or distinguish these cases, transpose the original range without filtering.
Common problems and how to fix them
- #SPILL!: One or more cells in the required output area are occupied, merged, or otherwise preventing the result from expanding. Clear the obstruction or move the formula to an area with enough room. Selecting the error indicator can help identify the blocking cells.
- The formula appears as text: The destination may be formatted as Text, or the entry may have a leading apostrophe. Change the cell format to General, then re-enter the formula. Changing the format alone may not recalculate text already entered as a string.
- Only one result appears: This can happen when using a version that does not support spilled results, or when the formula is entered as a legacy array formula incorrectly. Use the older-version steps and select the full output range if needed.
- The results are in the wrong direction: TRANSPOSE is symmetrical: a vertical range turns horizontal, while a horizontal range turns vertical. Check that the formula references the intended source and that the destination begins in the correct place.
- Some values are missing: Verify that the referenced range includes every source row. A formula referencing A2:A6 will not include a new value entered in A7. Also check whether a FILTER condition is intentionally excluding blanks or other entries.
Another frequent misconception is that TRANSPOSE sorts data. It does not. It retains the source order and content, changing only the orientation. To alphabetize a list, sort the source separately or use a suitable sorting approach before or alongside transposing it.
Which method should you choose?
For most Microsoft 365 workbooks, the formula is the simplest choice when the output should update as the source changes. Paste Special is better for a fixed copy. In an older Excel version, the formula still works, but the array-entry procedure differs. The right option depends less on the shape of the list than on whether you need a live link, a static result, or compatibility with an older workbook.
- Live, updating output: Use TRANSPOSE in a clear cell and let the result spill.
- One-time values: Copy the source and use Home > Paste > Transpose.
- Older Excel: Select the destination cells first and enter TRANSPOSE as an array formula.
- Skip empty entries: In Microsoft 365, filter the range before transposing it.
Excel for the web also supports dynamic arrays, but the available interface and behavior can differ slightly from the Windows desktop app. Excel for Mac has the same general TRANSPOSE approach; the legacy array-entry shortcut may differ as noted above. If a workbook is shared with people using older Excel, consider whether a spill formula will be compatible with their version.
Frequently Asked Questions
What is the Excel formula to change a column into a row?
Use TRANSPOSE. If the column is in A2:A6, enter =TRANSPOSE(A2:A6) in the first destination cell in a current version of Excel. It spills the values across a row in their original order.
Does TRANSPOSE keep the cells linked to the original list?
Yes. A TRANSPOSE formula refers to the source range, so changes in that range update the transposed output. Use Paste Special > Transpose instead if you want a fixed copy that is not formula-linked.
Can I transpose a row back into a column?
Yes. The same function works in either direction. Refer to the horizontal source row, enter TRANSPOSE in the first destination cell, and Excel returns the values vertically. The number of source cells determines the height of the output.
Why does Excel show #SPILL! after I enter TRANSPOSE?
Excel cannot place one or more results because the required spill area is blocked. Clear the cells in the output path, unmerge any interfering cells, or move the formula to a larger empty area. The source range itself can remain unchanged.
Can I transpose without a formula?
Yes. Copy the vertical cells, select the destination’s upper-left cell, and choose Home > Paste > Transpose. This is convenient for a one-time conversion; use a formula when you want the horizontal result to reflect later source edits.
Will transposing remove blank cells or duplicates?
No. Basic TRANSPOSE preserves blank positions, repeated values, and the order of the source. To omit blanks, apply a filter before transposing; duplicates remain unless you separately remove or filter them.
Related
- Excel Find and Delete Text: Replace, Clear, or Remove
- Excel Flip Data Upside Down: Reverse Rows in Excel
- Excel Find Table Names: Where to Look and How
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter in Table Format: How to Set Up and Use ItTo filter data in an Excel table, click a cell in the table and use the drop-down arrow…
- Excel Sort Entire Row by One Column Without Splitting DataYes. To sort entire rows by one column in Excel for Microsoft 365 on Windows, select the full…
- Excel Time Plus Minutes: Formulas and FixesTo add minutes to an Excel time, add the minutes divided by 1,440: with a time in A2…
- Sort an Entire Excel Spreadsheet by One ColumnTo sort an entire Excel spreadsheet by one column, click a cell in your data, choose Data >…
- Excel Formula for Years of Service From Hire DateTo calculate completed years of service from a hire date in Excel, use =DATEDIF(A2,TODAY(),"Y"), assuming the hire date…
- Excel Table Filter by Multiple Colors: What WorksIn Excel for Microsoft 365 on Windows, a table’s color filter can show rows for one selected fill…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.