Switch Excel Data From Horizontal to Vertical

How-to basics · Updated November 18, 2026

To switch Excel data from horizontal to vertical, copy the cells, select where the vertical version should begin, then choose Home > Paste > Transpose. For a result that updates when the source changes, enter the TRANSPOSE function in a blank cell instead.

What switching horizontal data to vertical means

Switching horizontal data to vertical is usually called transposing. It changes the orientation of a rectangular range: values arranged across columns in a row are placed down rows in a column, and values arranged down rows can be placed across columns. The values and their order are preserved; their positions change.

For example, a row containing the headings Product, Region, and Units can become a column containing those same three labels. A larger range also changes shape. A range with two rows and four columns becomes four rows and two columns. Transposing is different from sorting: it does not reorder values according to their contents.

The best approach depends on whether you need a one-time copy or a result that stays connected to the original data. Paste Special is straightforward for a fixed snapshot. TRANSPOSE creates a formula result that can update. Power Query is useful for repeatable transformations, especially when the source is imported or refreshed regularly.

Transpose a range with Paste Special

In Excel for Microsoft 365 on Windows, use these steps to turn a row into a column or rearrange a rectangular range:

  1. Select the source cells and press Ctrl+C.
  2. Select a blank destination cell. Leave enough room for the transposed range to spill or be pasted.
  3. On the Home tab, open the Paste menu and choose Transpose. In the standard ribbon, this is Home > Paste > Transpose.
  4. Check the result, then remove the original range only if you no longer need it.

For example, copying A1:D1 and choosing a destination at F1 places the four values in F1:F4. If the source is a 3-by-2 block, the destination will be a 2-by-3 block. Excel will not overwrite occupied destination cells without warning, so choose a clear area first.

Paste Special makes a separate copy rather than a live link. If you edit the original values later, the transposed copy does not automatically change. Formatting and other copied content may also come along depending on the paste option and source cells. If you want only values, use the relevant paste-values option separately; confirm the result because a values-only paste and a transpose choice may be presented as separate controls in the Paste menu.

On Excel for Mac, copy the range and use the Paste menu on the Home tab to choose Transpose; the precise ribbon layout can differ with window size and version. Windows keyboard instructions such as Ctrl+C are not Mac shortcuts: use Command+C to copy on Mac. The mouse-driven ribbon option is a reliable choice on either platform.

Use the TRANSPOSE function for a linked result

TRANSPOSE returns the source range in the opposite orientation. In Microsoft 365 and Excel 2021, enter the formula in a blank cell and press Enter; Excel places the results in the required spill range. With the labels January, February, March, and April in A1:D1, enter this formula in A3:

With the source labels in A1:D1 and the formula entered in A3, use =TRANSPOSE(A1:D1). The result spills down A3:A6 as January, February, March, and April, in that order.

Because the result is formula-driven, changing a source value changes the corresponding transposed result. The destination area must be clear. If a value, formula, or merged cell blocks the spill range, Excel displays a spill error rather than replacing the obstruction. Clear the affected cells or choose another starting cell.

In older Excel versions that do not support dynamic arrays, select the full destination range first, type the TRANSPOSE formula, then confirm it as an array formula with Ctrl+Shift+Enter. The destination must have the transposed dimensions. Do not press Ctrl+Shift+Enter in Microsoft 365 for an ordinary TRANSPOSE formula; Enter is the normal method for its dynamic-array result.

Formula results are not a substitute for a permanent copy if you intend to remove or move the source. A formula that refers to the original range depends on that range continuing to exist. If you need a fixed output later, copy the result and paste values in a separate location.

Choose the method that fits the task

There is no single best method for every workbook. Consider how often the source changes, how large it is, and whether you need a repeatable process.

  • Use Paste Special for a quick, one-time rearrangement or a static report. It is easy to inspect and does not leave a formula connection to the source.
  • Use TRANSPOSE when the source may change and you want the displayed result to follow those changes. It is convenient for a formula-based worksheet.
  • Use Power Query when you regularly import or refresh data and want the transformation steps saved for reuse.

Think about downstream formulas before changing the layout. A chart, named range, lookup formula, or other worksheet may refer to the original cells. Pasting a transposed copy elsewhere is usually less disruptive than moving the original data. If you replace the original range, inspect dependent formulas and charts afterward.

Also consider what the data represents. A row of monthly totals can be transposed into a column for a chart or a database-style list, but changing the layout does not make the data more meaningful by itself. If each column represents a different field and each row represents a record, the table may already be in a useful analysis format. Do not transpose merely to make a sheet look different.

Transpose with Power Query for repeatable work

Power Query can save transformation steps so you can apply them again when the source data is updated. In Excel for Microsoft 365 on Windows, first select the source range and choose Data > From Table/Range. If Excel asks whether the data has headers, answer according to the actual first row; incorrect header settings can change how the query interprets the data.

In the Power Query Editor, choose Transform > Transpose. Review the preview, then use Home > Close & Load to return the result to Excel. The exact commands available can vary by source and Excel build, but Transpose is a Power Query transformation. If the output needs a particular header row, review whether you should use the first row as headers after transposing.

Power Query is especially useful when a report arrives in the same horizontal layout every week or month. Instead of repeating the copy-and-paste steps, refresh the query after replacing or updating its source. The query applies its saved transformation steps to the refreshed data. Check the source connection and refresh behavior before relying on it for a recurring report.

Power Query output is loaded query data, not the same kind of live cell-by-cell formula link as TRANSPOSE. Editing the loaded output directly is generally not the way to change the transformation; make changes in the source or query steps, then refresh. On Mac and in Excel for the web, Power Query capabilities and the available editor features may differ from Windows, so check the commands in the version you use.

Check the result and avoid common problems

A transposed range can look correct at first glance while still causing problems in a workbook. Before deleting the source or sharing the file, check both the shape and the contents.

  • Confirm the dimensions. A source with three rows and five columns should become five rows and three columns.
  • Check the order. The first source row should become the first destination column, and values should retain their left-to-right or top-to-bottom order within the transformation.
  • Look for blanks and errors. Blank cells, error values, and formulas in the source can affect what appears in the output. Check cells that look empty, especially if later calculations depend on them.
  • Check formatting and formulas. A pasted result may carry formatting, while a formula result is calculated from references. Verify number formats, dates, and formulas rather than assuming the display tells the whole story.
  • Make room for the output. The destination must be large enough and, for a dynamic array, free of obstructions.
  • Protect the original until you verify the result. Keep a copy or use a separate destination sheet before replacing important data.

Merged cells can complicate copying and pasting because a merged area does not behave like an ordinary rectangular range. If Excel refuses the operation or the layout becomes confusing, unmerge the source or destination cells, transpose the underlying values, and then apply suitable formatting again. A destination table or structured range may also have restrictions; try a blank worksheet area if the paste is blocked.

Transpose a column into a row

The same tools work in reverse. To turn a vertical list into a horizontal row, copy the column and choose Home > Paste > Transpose, or use TRANSPOSE with the column as its source. A list in A1:A5 will produce five values across five columns when transposed.

This is useful when a worksheet has field names listed vertically but a report needs them as column headings, or when a series of values must be placed across a template. It does not combine or split values: each source cell remains a separate destination cell. If one cell contains text such as “North, South, East,” transposing moves that entire text string; it does not divide it into separate entries.

Transposition also does not automatically change data types or repair inconsistent records. For example, a date stored as text may remain text after the operation. If the goal is to prepare data for analysis, check that dates, numbers, and identifiers are represented consistently after changing orientation.

Frequently Asked Questions

Can I transpose data in Excel without copying and pasting?
Yes. Enter the TRANSPOSE function in a blank cell to create a formula result. In Microsoft 365 and Excel 2021, the result spills into the needed cells and can update when the referenced source changes.

Does Paste Special Transpose update when the original data changes?
No. Paste Special creates a separate copy. Use the TRANSPOSE function for a result linked to the source, or refresh a Power Query result when using a saved query.

Why does Excel show a spill error after I use TRANSPOSE?
A cell or other obstruction is occupying part of the output area, or the formula cannot place its full result there. Clear the cells in the expected spill range or move the formula to an empty area.

Can I transpose several rows and columns at once?
Yes. Select the complete rectangular range, not just one row or column, and use Paste Special > Transpose. Excel swaps the row and column counts while preserving the values’ relative arrangement.

Will transposing preserve formulas?
With a formula-based TRANSPOSE result, Excel returns values from the referenced range, and formula behavior depends on the source and the way it is referenced. A pasted transpose may copy formulas as part of the copied cells. Check the formula bar and test important references before replacing the source.

Can I undo a transpose?
Usually, press Ctrl+Z on Windows immediately after the operation, or Command+Z on Mac. If you have made further changes, undo may also reverse those later actions. For important work, keep the original data or a workbook copy until you have checked the result.

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.