To turn a vertical range into a horizontal row in Excel, copy the cells, select an empty destination, then choose Home > Paste > Transpose. This makes a rotated copy and leaves the original in place; use a TRANSPOSE formula instead if the new row should update when the source changes.
What Transposing Does
Excel calls rotating data from columns to rows, or rows to columns, transposing. A vertical list of four cells becomes four cells across one row. If you transpose a rectangular block, its rows and columns switch places: a range that is three columns wide and two rows tall becomes two columns wide and three rows tall.
Transposing changes the layout, not the underlying values. Text such as names, dates, and labels remains in the corresponding cells, but moves to a new position. This is useful when a report, chart, import template, or form expects records or headings to run in a different direction.
There is an important distinction between transposing cells and combining text. If A1 contains a first name and B1 contains a last name, transposing puts those values in separate cells down a column. It does not join them into one cell. The right method depends on whether you want the values rearranged or joined together.
Transpose a Column into a Row with Paste Special
For a one-time conversion, Paste Special is usually the quickest option in Excel for Microsoft 365 on Windows. It creates a separate result that you can edit independently of the source.
- Select the cells in the column that you want to turn into a row. Include only the data you need; if you include a heading or blank cells, those are copied too.
- Press Ctrl+C to copy the selection. A moving border appears around the copied cells.
- Select a blank cell where the new row should begin. Leave enough empty cells to the right for the whole result.
- Choose Home > Paste > Transpose. In the Paste menu, the Transpose command may appear as an icon; point to an icon to see its label if you are unsure.
- Check the result before removing or changing the original. The source is still there because this operation copies rather than moves.
You can also right-click the destination and choose the Transpose paste option, if it is shown in the context menu. Avoid choosing a normal paste command: that keeps the original vertical orientation. If the Transpose option is unavailable, copy the source again and make sure you have selected a single empty destination cell rather than entering edit mode inside a cell.
On Mac, the same basic operation is available from Excel’s Paste options after copying; the labels and placement can differ slightly from the Windows ribbon. In Excel for the web, look in the Home tab’s Paste menu for Transpose. If a particular interface does not expose that paste option, use the TRANSPOSE formula described below.
Plan the Destination Size
Before pasting, work out how large the rotated range will be. A source range with four rows and three columns becomes a result with three rows and four columns. Select a single starting cell, not an entire destination range; Excel fills the required area from that point.
For example, suppose A1:A5 contains five department names. Starting at D2 and transposing produces values in D2:H2. If A1:C4 contains a four-row by three-column block, transposing from E1 fills E1:H3. This dimension swap helps prevent overwriting existing information.
Excel will not safely place the result over cells that contain data. Choose an empty area or a new worksheet, particularly when the source and destination are on the same sheet. To use another sheet, copy the source, switch sheets, select the destination cell, and paste with Transpose. Keep the source until you have confirmed that the new layout is complete.
Use a TRANSPOSE Formula for a Linked Result
A formula is preferable when the transposed result should reflect later edits to the source. With the source values in A1:A4 and an empty destination starting at C1, enter:
=TRANSPOSE(A1:A4)
In current Microsoft 365, the formula spills across four cells, so C1:F1 displays the values from A1:A4 in order. The formula remains linked to the source: changing A2 changes the value shown in D1. Do not type into a cell occupied by the spilled result; edit the source or the formula instead.
For a rectangular source, the same function swaps the dimensions. With the source block A1:C2 and an empty destination beginning at E1, =TRANSPOSE(A1:C2) returns a two-column by three-row result in E1:F3. The function returns cell values, not a full copy of the source’s formatting, column widths, or row heights.
If Excel reports a spill error, check whether cells in the intended result area contain data, merged cells, or other obstacles. Clear the obstructing cells or choose a different starting point. Dynamic array formulas need room for the full result, and their spill range cannot be placed inside an Excel table. In older Excel versions that do not support dynamic arrays, select the destination range with the transposed dimensions, enter the formula, and confirm it with Ctrl+Shift+Enter. Microsoft 365 users normally press Enter.
Join Text from Several Columns Instead
If by “move text from columns to row” you mean put several column values together in one cell, transposing is not the operation you want. Use a joining formula to combine the values, then copy the result elsewhere if needed. This is common when first and last names, address parts, or words in separate cells need to become one text string.
With a first name in A2, a last name in B2, and an optional middle-name cell in C2, this formula joins the nonblank cells with spaces:
=TEXTJOIN(" ",TRUE,A2:C2)
If A2 contains Maya, B2 contains Chen, and C2 is blank, the result is Maya Chen. The TRUE argument tells TEXTJOIN to ignore empty cells, avoiding an extra separator for a blank middle name. To join values with commas, change the separator from a space to a comma and a space. TEXTJOIN is available in Microsoft 365 and newer Excel releases; for older versions, concatenation with the ampersand operator is another option, though handling optional blanks takes more care.
Joining values changes their contents into a single string; it does not preserve each item as a separate cell. If you need both a horizontal layout and joined text, first decide whether the final output should be multiple cells across a row or one combined cell. Those are different results and call for different tools.
Transpose with Power Query for Repeatable Work
Power Query can transpose a dataset as part of a repeatable import and cleanup process. It is useful when the source is refreshed periodically and you want to apply the same transformation again, rather than copying and pasting by hand each time.
On Windows, select a cell in the source range and choose Data > From Table/Range. If Excel asks to create a table, confirm the range and whether it has headers. In the Power Query Editor, choose Transform > Transpose, then use Home > Close & Load to return the transformed result to Excel. The exact placement of commands may vary slightly with the window size.
Power Query is more than a live cell formula: it records data-shaping steps and loads a result that can be refreshed from its source. After changing the source, use Data > Refresh All to update the query output. Transpose can make the first row of a result contain values that were previously in a column, so inspect the output and promote headers only if the first row really represents field names. For a quick one-off change, Paste Special is simpler.
Choose the Method That Fits the Job
- Paste Special > Transpose: Best for a quick, independent copy. It is straightforward, but later source edits do not automatically update the pasted result.
- TRANSPOSE: Best for a formula-driven result that should follow changes in the source. It needs a clear spill area and does not reproduce all source formatting.
- Power Query: Best for a repeatable transformation in an import or refresh workflow. It takes more setup than a one-time paste.
- TEXTJOIN or concatenation: Best when separate pieces of text must become one cell. It combines contents rather than rotating the cell arrangement.
For example, a short list of survey questions that needs to run across a report can usually be pasted with Transpose. A dashboard where headings should follow a changing source list may be better served by a TRANSPOSE formula. A monthly file delivered in the same layout may justify a Power Query workflow.
Common Problems and What to Check
- The result will not paste: Confirm the destination has enough empty cells and is not blocked by existing content. Copy the source again if the clipboard selection has been lost.
- The output is the wrong size: Count the source rows and columns. The transposed result has those dimensions reversed; it does not simply turn every selected cell into one row if the selection is a larger block.
- Formatting looks different: Transpose focuses on rearranging cell contents. Review number formats, borders, widths, and heights afterward. You may need to adjust the destination layout separately.
- Dates or numbers look unexpected: Check the cell’s number format and the underlying value. A displayed date may be stored as a number with a date format; changing orientation does not convert it into text.
- A formula result does not update: A pasted transpose is a snapshot, while a TRANSPOSE formula links to its source. Check which method you used and whether the source cells themselves contain formulas.
- The formula shows a spill error: Clear cells in the output footprint, unmerge any obstructing cells, or move the formula to an open area. The formula needs the full space required by the transposed dimensions.
Also check whether the source contains formulas. A normal transpose paste may carry formulas rather than just their displayed results, and references can behave differently when moved to a new location. If you need a fixed snapshot, use Paste Special options to paste values after transposing, then confirm the output. The exact sequence depends on which paste options your Excel version exposes.
Frequently Asked Questions
Can I transpose data without changing the original?
Yes. Copy the source and use Paste > Transpose at a separate destination. The original remains in place. A TRANSPOSE formula also leaves the source untouched, but its displayed result stays linked to it.
Can I move rather than copy a column into a row?
Transpose paste creates a copy, not a cut-and-move operation. After checking the result, you can remove the original if you no longer need it. Keeping the source until verification reduces the risk of losing data.
Does Transpose keep formatting?
Do not assume it will reproduce every layout detail exactly. Check number formats, borders, widths, heights, and other presentation choices after pasting. A formula-based transpose returns values but does not mirror the source’s full formatting.
How do I turn a row back into a column?
Use the same operation in reverse: copy the horizontal range, select an empty starting cell, and choose Paste > Transpose. A TRANSPOSE formula also swaps the row into a column.
Can I transpose a table?
You can transpose a selected table’s data, but the result may not retain the same structured table behavior. For a formula result, place the TRANSPOSE formula outside an Excel table so its spilled range has room. Review headings and formatting after the conversion.
Check the Result Before Replacing the Source
Before deleting the original range, compare the first and last values and confirm the order of the entries. For a larger block, check a few cells from different corners: transposition swaps row and column positions, so a misplaced starting cell or an incorrectly selected range can be easy to miss.
Once the result is correct, you can format the new row, resize columns, or copy it into another workbook. If this is a recurring task, keep a clear source area and use a formula or Power Query so the process is easier to update. For a one-time layout change, Paste Special > Transpose remains the simplest route.
Related
- How to Split a Cell in Excel: Text, Columns, and More
- How to Run Scenario Manager in Excel
- How to Run Goal Seek in Excel to Find a Target Value
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Filter: How to Create and Use OneTo create a filter in Google Sheets in a web browser, select your data range, including its header…
- Cannot Find Data Validation in Excel? Here’s WhereIn Excel for Microsoft 365 on Windows, select a cell and open Data > Data Tools > Data…
- How to Flip a List of Numbers in Excel: 10 Easy MethodsTo reverse a vertical list while keeping its original sequence intact, add a helper column numbered from 1…
- How to Print in Microsoft Excel: A Step-by-Step GuideTo print in Microsoft Excel for Microsoft 365 on Windows, open the workbook, choose File > Print, review…
- Add and Share a Calendar in OutlookTo add a calendar in Outlook, open Calendar and choose Add Calendar; to share one, select the calendar…
- Excel Row Height Shortcut: Set, Adjust, and AutoFit RowsIn Excel for Microsoft 365 on Windows, press Alt, H, O, H in sequence to open the Row…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.