Copy Horizontal to Vertical in Excel: Transpose Data

How-to basics · Updated November 20, 2026

To copy a horizontal row into a vertical column in Excel, select and copy the row, choose the first destination cell, then use Home > Paste > Paste Special, select Transpose, and click OK. This switches the copied cells from left-to-right to top-to-bottom. For a result that updates when the source changes, use the TRANSPOSE formula instead.

Transposing is useful when a report, table, chart source, or form needs the same values arranged in the opposite direction. You can use Paste Special to create a separate, static copy, or a formula to link the new column to the original row. The steps below are for Excel for Microsoft 365 on Windows.

What You’ll Need

  • An Excel workbook with the horizontal range you want to turn into a column.
  • A clear destination area with enough rows for every cell in the source range. For example, a row with six cells needs six vertical destination cells.
  • For the formula method, Excel for Microsoft 365 or Excel 2021, and a clear spill area beneath the cell where you enter the formula.

Step-by-Step

  1. Check the source row. Find the first and last cells you want to transpose, and make sure the selection contains only the intended range. For example, if the values are in A1:F1, select A1:F1. You can select a range by clicking its first cell, holding the mouse button, and dragging to the last cell, or by clicking the Name Box to the left of the formula bar, typing A1:F1, and pressing Enter. The Name Box approach is handy for long ranges.
  2. Copy the horizontal range. With the cells selected, press Ctrl+C on Windows. A moving dashed outline appears around the copied cells. Copy rather than cut if you want to keep the original row. If you use Excel for Mac, press Command+C. Keep the source selection copied until you complete the paste.
  3. Choose the first destination cell. Click the cell where the top of the new column should begin. To put the six values from A1:F1 in a vertical list, for example, choose H1; Excel will fill H1:H6. The destination can be on the same worksheet or another worksheet in the workbook. Check that the destination cells are empty and do not overlap the source range, because transposed cells need to occupy a different shape.
  4. Open Paste Special. On Windows, go to Home > Paste > Paste Special. You can also right-click the destination cell and choose Paste Special. If Excel shows a Paste Special dialog, continue there. The ribbon or right-click menu may also show a Transpose paste icon directly; choosing that option performs the same basic operation without opening the dialog.
  5. Turn on Transpose and paste. In the Paste Special dialog, select the Transpose checkbox, then click OK. Excel places the first source cell in the destination cell, the second source cell directly below it, and continues downward. If you selected A1:F1 and began in H1, the result occupies H1:H6. Check the first and last values to confirm that the order is correct.
  6. Choose how to handle formatting. Paste Special normally carries over the copied cell formatting along with the contents. If you want to transpose values but use the destination’s existing formatting, choose a values-only paste option before applying Transpose, when available in the dialog or paste options. You can also paste normally and then use Home > Paste > Values to replace formulas with their displayed results, though that follow-up paste does not transpose again. If exact formatting matters, inspect number formats, borders, and alignment after pasting.
  7. Use a formula if the column should stay linked. With the source values in A1:F1, click an empty cell such as H1 and enter =TRANSPOSE(A1:F1), then press Enter. In Microsoft 365 or Excel 2021, the formula spills the six values into H1:H6; the first result is the value from A1 and the last is the value from F1. Changes to the source range update the spilled results. Leave the cells beneath H1 clear so the formula has room to spill. This method returns the transposed contents, but it does not copy the source formatting.

After either method, check whether the destination should be sorted, formatted, or converted into a table. Transpose changes the layout; it does not automatically add headings, remove duplicates, or reinterpret the data. If you plan to use the result in a chart or another calculation, verify that the labels and values still line up as intended.

Common Mistakes

  • Using regular Paste instead of Transpose. A normal Ctrl+V paste keeps the row horizontal. Open Paste Special and select Transpose, or choose the Transpose option in the paste menu.
  • Selecting the wrong source cells. A single extra blank cell or heading can produce an unexpected extra row. Review the selected range before copying, especially when using the Name Box.
  • Choosing a destination that is too small or occupied. Transposing a row of 20 cells requires 20 vertical cells. For a formula, every cell in the spill range must be clear; existing content can prevent the results from appearing.
  • Expecting a formula to behave like a static copy. A TRANSPOSE formula remains linked to its source, and its result is controlled by the formula cell. You cannot edit just one value inside a spilled result. Use Paste Special if you want an independent copy you can change cell by cell.
  • Assuming formulas and formatting will behave exactly like values. Pasted formulas can adjust relative references to suit their new location. A formula-based transpose also does not bring across the source’s font, fill, borders, or number format. Check formulas and presentation if either is important.
  • Trying to transpose over the original range. The output has a different shape, so using a destination that intersects the source can fail or overwrite data. Use an empty area or a separate worksheet, confirm the result, and remove the old row only if you no longer need it.

Tips

  • Use Paste Special for a one-time conversion. It is straightforward when you want the column to stand on its own and not change when the row changes. This is usually the best choice for preparing a report or sharing a fixed snapshot.
  • Use TRANSPOSE for a live connection. A formula is useful when the source is regularly updated and you want the vertical version to reflect those edits. If you need to break the connection later, copy the spilled results and use Home > Paste > Values in a different clear area.
  • Check whether you need values, formulas, or formatting. Pasting a copied range can carry formulas and formatting; a formula transpose returns calculated values from the source references. Decide what the destination should preserve before you paste, then inspect a few representative cells.
  • Keep a clear output area. Put the result somewhere with enough empty rows and away from other data. This avoids overwriting cells and makes it easier to compare the column with the original row.
  • For a very long row, use the Name Box. Type the precise source range there instead of dragging across many columns. This also makes it easier to verify the range before copying.
  • Use the same basic workflow on Mac, with different keys. In Excel for Mac, use Command+C to copy. The Paste Special and Transpose options are available from the ribbon or context menu, though their placement can vary slightly by Excel release. Excel for the web also offers paste options, but menu placement can vary; if the dialog is not available, use the ribbon or context-menu Transpose option when shown.

Troubleshooting

The data stayed horizontal. The copy likely used a regular paste option. Undo with Ctrl+Z on Windows (Command+Z on Mac), select the copied row again, and choose Home > Paste > Paste Special, then select Transpose. If a Transpose option appears in the paste menu, use that instead of the standard Paste button.

The formula shows #SPILL! or will not return a column. With the source row in A1:F1 and the formula in H1, Excel needs H1:H6 available for the six results. Clear any text, numbers, formulas, or merged cells in the way, then select the formula cell and check the spill outline. Do not type into an individual cell within the spilled range; change the formula in H1 or clear the obstructing cell. If you see a different error, confirm that the source reference is valid and that the formula begins with an equals sign.

The Paste Special dialog does not show Transpose. Confirm that you copied a cell range in Excel before opening the dialog. If you copied text from another application or selected a non-cell object, the available paste options can differ. Press Esc, copy the worksheet cells with Ctrl+C, click a regular worksheet cell, and reopen Paste Special. You can also check the Home tab’s Paste menu for a Transpose icon.

The pasted values are correct, but formulas changed. When copied formulas move to a new location, relative references may be adjusted. Review the formulas in the destination, especially if they refer to cells outside the selected row. If you need fixed references, edit the source formula to use absolute references where appropriate before copying, or paste values when you only need the current displayed results.

The result looks different from the source. Inspect number formats and cell formatting. A date or percentage may display differently if its format did not carry over as expected, even when the underlying value is present. Select the destination cells and use Home > Number Format to choose the appropriate display. If the destination is a protected worksheet, you may also need permission to edit it before pasting.

Frequently Asked Questions

Can I transpose a row into a column without copying it?
Yes. Enter a TRANSPOSE formula in the first destination cell. With A1:F1 as the source range, =TRANSPOSE(A1:F1) entered in H1 spills the values down H1:H6 in Microsoft 365 or Excel 2021. Use Paste Special > Transpose instead when you want a separate, static copy.

Will the transposed column update when I change the original row?
A Paste Special transpose is a copy, so later edits to the source do not update the destination. A TRANSPOSE formula does update when its referenced cells change. If you later want fixed values, copy the formula results and paste them as values in a separate area.

Does Transpose copy cell formatting?
Paste Special can transpose copied cell contents and formatting. The exact result depends on the paste option you select, so check the destination’s number formats, borders, and fills. A TRANSPOSE formula returns results but does not transfer the source formatting.

Can I transpose more than one row?
Yes. Select a rectangular range rather than just one row, copy it, and choose Transpose. Excel swaps rows and columns: for example, a 2-row by 3-column range becomes a 3-row by 2-column range. Make sure the destination has enough empty cells for the changed shape.

Can I transpose data in an Excel table?
You can copy cells from a table and transpose them into a clear worksheet area, but the result is not automatically a new Excel table. After checking the pasted data, select the output range and use Insert > Table if you want table features such as filter buttons and structured references.

Can I undo a transpose?
Immediately after pasting, press Ctrl+Z on Windows or Command+Z on Mac. If you have made other changes since then, undo may also reverse those later actions. For that reason, check the destination before deleting or replacing the source data.

Related

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.