Excel Copy and Paste Rows Into Columns: 3 Easy Methods

To copy a row into a column in Excel for Microsoft 365 on Windows, select the row cells, press Ctrl+C, click a blank destination cell, then choose Home > Paste > Transpose. This turns the horizontal sequence into a vertical one; on a Mac, press Command+C and use Paste > Transpose.

How to transpose a row with Paste Special

For a one-time conversion, Transpose is usually the quickest method. It swaps rows and columns as it pastes: a row of cells becomes a column, while a column becomes a row. You do not need to enter a formula or change the source data.

Select only the cells you want to turn into a column, such as A1:D1, rather than selecting the entire worksheet row.
Copy the selection with Ctrl+C on Windows or Command+C on Mac. A moving outline around the copied cells indicates that the copy is ready.
Click the top cell where the new column should begin. Choose a blank area outside the source cells so you do not overwrite the data you are copying.
On the Home tab, open the Paste menu and choose Transpose. In versions that show paste-option icons, the Transpose icon depicts the row and column orientation swapped.

The result starts at the destination cell and extends downward for as many cells as the source selection contained. For example, copying A1:D1 into F1 puts the original A1 value in F1, B1 in F2, C1 in F3, and D1 in F4. The source row remains in place.

Excel may keep the copied cells available after pasting, so you can paste the same selection elsewhere if needed. Press Esc when you are finished to clear the moving copy outline. The menu layout can vary slightly between Excel builds, but the Transpose option is in the paste options.

What happens to the data when you transpose it

Transpose changes the orientation of a copied range, not the sequence of its cells. The first source cell goes into the first destination cell, the second goes into the next cell down, and so on. If the selection includes several rows and columns, Excel swaps the dimensions: a range that is two rows by three columns becomes three rows by two columns.

Blank cells in the selection occupy corresponding blank positions in the result. This matters when a row has gaps: Excel does not automatically close those gaps or collect only the nonblank entries. If you want a compact list without blanks, that is a separate data-cleaning task.

By default, a regular transpose paste can carry cell formatting along with the cell contents. Depending on the selection, pasted content can also include formulas. If you need only the displayed results, use Paste Special and choose Values together with Transpose where those options are available, or paste the transposed results and then convert them to values. Formatting and formulas are separate concerns: values-only paste will not preserve the original formulas.

Transposing formulas deserves particular care. When a formula is copied to a different position, Excel can adjust relative cell references, just as it does in an ordinary copy. If the formula must continue referring to the same cells, inspect the result or use absolute references in the original formula, such as $A$1, before copying. Do not assume that a transposed formula will behave like a fixed text value.

Choose the method that fits your task

There are three useful approaches, and the best one depends on whether the result should be a snapshot, a live link, or a repeatable data transformation.

Paste Special > Transpose: Best for a quick, one-time copy. The destination is a pasted result, so it does not automatically update when the source changes.
TRANSPOSE formula: Best when the destination should reflect changes to the source cells. The formula result is linked to its source and, in current Excel, spills into the required cells.
Power Query: Best for a repeatable process involving imported or regularly refreshed data. It takes more setup than a simple paste, but can be refreshed after the source changes.

For a single row that will not change, Paste Special is generally simplest. If the row is a set of values that another part of the workbook must keep in sync, use a formula. If you repeatedly reshape data from the same source, consider Power Query. These methods do not produce identical results: pasted values are a snapshot, formulas recalculate, and a query produces a refreshed output table.

Use the TRANSPOSE formula for a linked column

In Excel for Microsoft 365, enter TRANSPOSE in the first cell of an empty destination area. With the source values 12, 18, 25, and 40 in A1:D1, and the destination beginning at F1, the formula is:

=TRANSPOSE(A1:D1)

The formula returns 12, 18, 25, and 40 vertically in F1:F4, in that order. Change a source value and the corresponding result updates. The formula refers to the original cells, so it does not create an independent copy.

In versions with dynamic arrays, one formula in F1 spills into the cells below it. Keep the spill area clear: if F2:F4 contains data, Excel cannot place all four results and may show a #SPILL! error. Clear the obstructing cells or move the formula to an empty area. If the source range itself changes size, adjust the formula range; a reference to A1:D1 covers four cells, not an automatically expanding row.

TRANSPOSE is available in Excel 2021 and Microsoft 365. In older Excel versions without dynamic arrays, select the four destination cells first, type the formula, and confirm it as an array formula with Ctrl+Shift+Enter on Windows. Excel then returns the four results together. Do not use that older entry method in current Microsoft 365 for this simple case; enter the formula in the top cell and let it spill.

On Mac, the formula syntax is the same. Dynamic-array behavior depends on the Excel version, and the legacy array-formula shortcut may differ by keyboard and build. Excel for the web supports dynamic-array formulas, though the ribbon and selection experience may look different from the Windows desktop app.

Transpose repeatedly with Power Query

Power Query is useful when the row comes from a recurring report or an external data source and the same reshaping needs to be repeated. Rather than manually pasting each time, you create a query that transposes the imported range and then refresh the query when appropriate.

Make sure the source is a well-defined range or Excel table. Select a cell in it and choose Data > From Table/Range. If Excel asks to create a table, check that the selected range is correct before confirming.
In the Power Query Editor, choose Transform > Transpose.
Review the preview to confirm that the cells are in the intended order. Choose Home > Close & Load to return the result to Excel.
When the source data changes, refresh the query from Excel. Confirm that the query’s source includes the new or changed cells; a query cannot import data outside its defined source.

Power Query is not necessary for a one-off row conversion. It also does not behave like a live cell-by-cell formula: the loaded result changes when the query is refreshed, not immediately whenever a source cell is edited. If you add columns to a horizontal source, check that the source range and query still include them. Power Query availability and authoring features can vary between desktop, web, and Mac builds, so use the desktop editor if the described commands are not present in your version.

Keep the result readable and reliable

Before pasting, choose a destination with enough empty cells below it. A row with 20 selected cells needs 20 destination cells vertically. If those cells are occupied, Excel may replace data, or a dynamic formula may be blocked. For a valuable workbook, check the destination first and consider saving a copy before making a large change.

Decide whether the output should include the source’s formatting. A transpose paste can bring formatting such as number formats, fills, and borders, which may be useful for labels but awkward for a list. If the destination already has formatting you want to keep, use Paste Special options that limit what is pasted. If the output must be fixed values, choose a values-only paste rather than relying on a formula link.

Check dates, percentages, and numbers after pasting. A cell may contain a numeric value with a date or percentage format, so the displayed result can depend on the destination formatting. If a date appears as a number or a value displays unexpectedly, inspect the cell’s number format rather than assuming the underlying data changed. Also review formulas and references if the source contained calculations.

Common problems and their fixes

Transpose is missing or unavailable: Copy the range first, then open the Home tab’s Paste menu. The transpose option is a paste operation and may not appear as an active choice if there is no copied selection.
The output overwrote something: Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then choose a clear destination. Undo may not be available after some later actions, so inspect the destination before pasting.
A formula shows #SPILL!: Clear cells in the output area or move the formula. Merged cells or other obstructions in the spill range can also prevent the results from appearing.
The results do not update: A normal transpose paste is a snapshot. Use TRANSPOSE if the cells should recalculate with the source, or refresh a Power Query output if that is the method you chose.
Only some cells appear: Check the size of the selected source range and whether the destination has room. Hidden rows or filtered data can also make a selection behave differently than expected; verify what cells are actually selected before copying.

A common misconception is that transpose means “turn a row into a list of values with no gaps.” It only swaps the orientation of the selected rectangle. To remove blanks, combine the conversion with an appropriate filtering or cleanup step rather than expecting Paste Special to reorganize the contents.

Frequently Asked Questions

Can I copy an entire Excel row and paste it as a column?
Yes, but for most tasks select only the cells containing the data, copy them, and choose Home > Paste > Transpose at the destination. Selecting an entire worksheet row can involve more than a million cells and is usually unnecessary.

How do I paste a row vertically without changing the original?
Copy the row cells and use Transpose in a blank destination area. The original remains unchanged. Avoid Cut if you want to keep the source in place.

Will a transposed column update when I edit the source row?
A regular Paste Special result does not update automatically. A TRANSPOSE formula in current Excel links the output to the source; a Power Query result updates when you refresh the query.

Can I transpose more than one row at a time?
Yes. Copy a rectangular range and use Transpose; Excel swaps its row and column dimensions. For example, a two-row-by-three-column selection becomes three rows by two columns. Check the destination area because it will need space for the new dimensions.

Can I do this in Excel for the web or on a Mac?
On a Mac, use Command+C and the Paste menu’s Transpose option; formula syntax is unchanged. Excel for the web has paste and formula capabilities, but menu details can differ. For Power Query authoring, available commands depend on the web or desktop version and account.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.