To transpose a row or column in Excel, copy the range, select the destination cell, then choose Home > Paste > Transpose. For a live result, use =TRANSPOSE(A2:A6) in the destination cell; if you mean arranging a long list into several columns, use =WRAPROWS(A2:A10,3,"") instead.
What “transpose a list into a table” can mean
Transpose has a specific meaning in Excel: it switches rows and columns. A vertical list of five entries becomes a horizontal row of five entries, while a horizontal row becomes a vertical list. A rectangular range is turned on its side: its row count and column count swap, and the relative positions of its cells are reversed accordingly.
People also use “transpose” to mean putting one long list into a grid with several columns. That is a different reshaping operation. For example, turning nine names in one column into three rows of three names does not simply swap rows and columns; it wraps the sequence across a chosen number of columns. Excel’s Paste Special Transpose command will not do that wrapping.
There is a third meaning of “table” in Excel. An Excel Table is a structured range with header filters and features such as banded rows. A horizontal row of transposed values is not automatically a useful database-style table. If the values are field names, for example, they may become column headers for records you add later; if they are just names, the result is only a row until you add meaningful headers and records.
Transpose with Paste Special
Paste Special is a good choice when you need a one-time copy, especially if you want to preserve the source’s cell formatting. In Excel for Microsoft 365 on Windows:
- Select the source cells and press Ctrl+C.
- Select a blank destination cell. Leave enough room for the transposed range so it will not overwrite existing information.
- On the ribbon, choose Home > Paste > Transpose. You can also open the Paste dropdown and select its Transpose option.
- Check the result, then remove the original range only if you no longer need it.
For instance, copying A2:A6 and transposing at C2 places the five source entries across C2:G2. A source range with three rows and two columns becomes two rows and three columns. Transpose paste produces a separate pasted result; it does not keep the new cells linked to later changes in the original range.
Excel for Mac has a Transpose option in the Home tab’s Paste controls as well, though the ribbon layout can vary by release. Excel for the web also provides paste options, but the exact menus can differ from the desktop ribbon. If you cannot find the option, open Paste Special or its dropdown after copying the source range and look for Transpose.
Use TRANSPOSE for a live result
A formula is preferable when the output should reflect edits to the source. In Excel for Microsoft 365, enter the formula in the top-left cell where the output should begin; Excel returns a dynamic array that spills into neighboring cells. With the source list in A2:A6 and the output starting at C2, enter =TRANSPOSE(A2:A6). The result is a one-row range from C2 through G2, and changes to A2:A6 update the displayed results.
In current Microsoft 365 Excel, you normally enter this formula once and press Enter. You do not need to select the entire destination range first. The older array-formula method applies to earlier Excel versions: select a destination range of the required dimensions, type the formula, and confirm it with Ctrl+Shift+Enter. In Microsoft 365, that key combination is generally unnecessary for TRANSPOSE.
Formula-based transpose does not copy the source formatting. Format the output separately if you need number formats, borders, or colors. Also, the formula itself occupies only its top-left cell; the other displayed results belong to the spilled array and cannot be edited one by one. To change the output, edit or remove the formula in its anchor cell.
Turn one long list into multiple columns
To fill a grid from a single sequence, use WRAPROWS rather than TRANSPOSE. WRAPROWS is available in Microsoft 365 and Excel 2024. It takes an array, the number of items per row, and optionally a value to use for any unfinished final row. With nine entries in A2:A10 and a three-column layout, enter =WRAPROWS(A2:A10,3,"") in an empty cell. The result is a three-by-three grid, with the first three entries on row one, the next three on row two, and the final three on row three.
The width you choose determines the layout. If the number of source items is not divisible by that width, the last row is incomplete. The formula above uses an empty string as the optional padding value, so any unused positions appear blank rather than showing an error. For example, ten items wrapped into three columns create four rows, with one item in the last row and two blank positions. Make sure those spill cells are clear.
If you want to fill down columns instead of across rows, Excel also has WRAPCOLS in Microsoft 365 and Excel 2024. These functions reorganize a sequence; they do not sort it or infer which values belong together. Decide whether you want to read across each row or down each column before choosing the function.
Make the result an Excel Table
Once the data is arranged in rows and columns, you can convert an appropriate range into an Excel Table. A useful table normally has one header row, with a different field name in each column, followed by records arranged in rows. For example, a list of labels such as “Name,” “Department,” and “Start date” could be transposed into headers, but you would still need one employee’s information per row beneath them.
Select the complete range, including its headers, and press Ctrl+T on Windows. Confirm the range in the Create Table dialog and select My table has headers if the first row contains field names. You can also use Insert > Table. On Mac, use Insert > Table if the Windows shortcut is not applicable to your keyboard setup. Excel for the web has table creation through its Insert tab.
Dynamic-array formulas such as TRANSPOSE and WRAPROWS need room to spill, and spilled array formulas are not supported inside Excel Tables. Put the formula in a normal worksheet range outside the table. If you need a fixed table, copy the finished results and use Paste Special > Values in the destination range, then create the table from those values. That produces a snapshot, so later source changes will not update it automatically.
Choose the method that fits the job
These approaches solve related but different problems. Pick based on the desired shape and whether the output must stay connected to the source:
- Paste Special > Transpose: best for a quick, static row-to-column or column-to-row conversion. It can carry over formatting, but the pasted cells are not a live link to the source.
- TRANSPOSE: best when the output should update as the source range changes. It spills into the dimensions required by the transposed range and does not bring across formatting.
- WRAPROWS: best when a single sequence should fill a set number of columns, such as laying out a list of nine values in three columns. It is a reshaping function, not a row-column swap.
- WRAPCOLS: best when the same kind of sequence should fill down a chosen number of rows per column.
For a one-off report, Paste Special is often straightforward. For a workbook that is refreshed or edited, a formula can reduce repeated manual work. If the final destination needs to be a formal Excel Table, plan for a fixed-value copy or another data-preparation step rather than trying to put a spill formula inside the table.
Common problems and how to fix them
- The formula shows #SPILL!: one or more cells in the required output area are occupied, merged, or otherwise preventing the array from expanding. Clear the obstructing cells and check that the output range is not inside a table.
- The result is still vertical: confirm that you used TRANSPOSE for a row-column swap. If you used WRAPROWS, check its second argument: it specifies how many values go across each row.
- The last row shows an error: a WRAPROWS result may have leftover positions when the input count does not divide evenly by the selected width. Supply a padding value such as an empty string, or accept the default padding behavior.
- Formatting did not move: a formula returns values or calculated results, not the source cell’s visual formatting. Apply formatting to the output, or use Paste Special Transpose when a copied, static range is appropriate.
- References changed unexpectedly: transposing pasted formulas can adjust relative cell references, just as copying formulas can. Inspect formulas in the output before relying on them; consider using absolute references where appropriate.
- The destination overwrote other data: check the dimensions before pasting. A five-cell vertical list needs five horizontal destination cells, and a rectangular source needs a destination area with its row and column counts exchanged.
One misconception is that clicking Transpose converts a list into a structured table with headers and filters. It only changes the orientation of the copied cells. Creating an Excel Table is a separate step, and the data still needs a sensible header row and record layout.
Frequently Asked Questions
Can I transpose a column into a row without copying and pasting?
Yes. With the source in A2:A6, enter =TRANSPOSE(A2:A6) in a blank cell in Microsoft 365. The result spills horizontally and stays linked to the source.
How do I transpose a row into a column in Excel?
Copy the row and use Home > Paste > Transpose at a blank destination, or use TRANSPOSE with the horizontal source range. The output will have one row for each source column.
Can I turn a list into three columns?
Yes. In Microsoft 365 or Excel 2024, use WRAPROWS with 3 as its second argument. For example, with nine items in A2:A10, =WRAPROWS(A2:A10,3,"") returns three rows and three columns.
Does Transpose keep the original list connected?
Paste Special Transpose creates a static pasted result. TRANSPOSE creates a formula result that updates when its referenced source changes, provided the output can still spill.
Why can’t I use TRANSPOSE inside an Excel Table?
Dynamic-array formulas cannot spill within Excel Tables. Put the formula in a regular worksheet range; if you need a table, copy the result as values into a separate range and create the table there.
Related
- Excel Shortcut for Filtering a Table: Keys and Tips
- Excel Shortcut for Inserting a Row on Windows and Mac
- Excel Column Selection Shortcut: Ctrl+Space and More
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Can You Split a Column in Google Sheets? Here’s HowYes. In Google Sheets, select the cells you want to divide and choose Data > Split text to…
- Excel Table Multiple Header Rows: What WorksNo. An Excel Table can have only one header row: the first row of the table range supplies…
- Excel Conditional Formatting RGB Colors ExplainedYes. In Excel for Microsoft 365 on Windows, you can use a custom RGB color in a conditional…
- How to Change Attachment View in OutlookTo change how you view an attachment in Outlook, open the message and select the attachment to preview…
- Multiply Hours by Money in Excel: A Practical GuideTo calculate pay from decimal hours, put the hours in B2 and the hourly rate in C2, then…
- Excel Table Formatting Tips for Clearer, Easier DataFor clear, reliable Excel table formatting, convert your data range into a table with Ctrl+T, confirm that the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.