To switch an Excel table’s rows and columns, transpose its cell range: in Excel for Microsoft 365 on Windows, convert the table to a range if necessary, copy the range including its headers, and use Home > Paste > Transpose in an empty area. The result is a regular cell range; convert it to a new Excel table afterward if you need table features.
What switching rows and columns means
Switching rows and columns is also called transposing. It turns each row into a column and each column into a row. A range that is three rows tall and four columns wide becomes four rows tall and three columns wide. The values move with their corresponding row and column positions.
For example, a sales table with products down the left and months across the top can be turned around so months appear down the left and products become column headings. This changes the worksheet data itself. It is different from changing how a chart displays the same data: chart controls can switch plotted series without rearranging the cells.
An important distinction is that Excel’s structured table object cannot simply be rotated in place while remaining a table. The transpose command produces a cell range, not a table with the same name, filters, and structured references. Plan for that change if formulas or other workbook features depend on the original table.
Prepare the table before transposing
Make a copy of the workbook or preserve the original data on another worksheet before making a structural change. Transposing changes the layout, and converting a table to a range removes its table behavior. Keeping the original makes it easier to check the result or return to the starting layout.
Check the size and shape of the data. If the original range has a header row, include it when copying so that its labels move along with the data. If it has a totals row, decide whether that row belongs in the transposed output; including it turns the totals into another row of values in the new orientation. Also check for merged cells, blank headings, and formulas that refer to the original table.
- Choose a blank destination area large enough for the reversed dimensions. A three-row by four-column source needs four rows by three columns.
- Keep the destination outside the source range and outside any other Excel table.
- Check whether the first column contains record names that will become new column headings. Excel table headings must be usable and unique, so you may need to rename them after transposing.
- Decide whether you need a one-time copy or an output that updates when the source changes. Paste Special and a formula serve different purposes.
Transpose a table with Paste Special
In Microsoft 365 for Windows, a reliable approach is to convert the source table to a normal range, then transpose that range. Click a cell in the table to show the contextual Table Design tab. On that tab, choose Convert to Range and confirm. The cells and their formatting remain, but the range no longer has table filters, automatic expansion, or structured-reference behavior.
- Select the full range, including the headings and any rows you deliberately want to include, then press Ctrl+C.
- Click a blank destination cell outside the source range and any existing table.
- Choose Home > Paste > Transpose. If the Transpose command is not visible in the Paste menu, open Paste Special and select Transpose.
- Inspect the result, including the new first row and first column. Rename headings as needed and check formulas and number formats.
- If you want table features on the result, select the transposed range and choose Insert > Table, or press Ctrl+T. Confirm whether the first row contains headers.
Paste Special is useful when you want a separate copy and do not need it to stay linked to the source. The transpose option can carry cell contents and formatting, but it does not preserve the original table as a table. Because the original was converted to a range, the resulting data is not automatically connected to the old table’s name or structured references.
In Excel for Mac, the same general method applies: copy the range, select the blank destination, and use the Transpose option in the Paste Special controls. Menu and ribbon layouts can differ by build, so look for Transpose under the Home tab’s Paste options or the Edit menu’s Paste Special choices. In Excel for the web, Paste Special may offer Transpose; if that option is unavailable in your browser or workbook, use a TRANSPOSE formula or open the workbook in the desktop app.
Use a formula for a linked transpose
A formula is a better fit when the transposed view should reflect later changes to the source. In Microsoft 365, enter the TRANSPOSE function in one empty cell and Excel spills the results into the required rectangular area. The source remains unchanged, and the output recalculates when source values change.
With the example data in A1:D3—Quarter, North, South, West in the first row; Sales, 120, 95, 110 in the second; and Returns, 4, 2, 3 in the third—enter =TRANSPOSE(A1:D3) in F1. The result fills F1:H4: the first row is Quarter, Sales, Returns; the next rows are North, 120, 4; South, 95, 2; and West, 110, 3. The original three-by-four range therefore produces a four-by-three result.
The formula output is a dynamic array. Do not type into cells inside its spill area, because those cells are controlled by the formula. If an occupied cell blocks the output, Excel shows a spill error; clear the obstruction or move the formula. A spilled formula also cannot be entered inside an Excel table. If you need a table, copy the completed output and paste values into a separate range, then create a table from those values.
TRANSPOSE is available in older Excel versions too, but versions without dynamic arrays require selecting the full destination area first and confirming the formula as an array formula with Ctrl+Shift+Enter. In current Microsoft 365 for Windows, enter it in the top-left destination cell and press Enter. On a Mac, dynamic-array entry also uses Enter; legacy array-formula shortcuts can vary by keyboard and Excel version.
What happens to headings, formulas, and formatting
Transposing moves cells by position; it does not infer the meaning of the data or redesign the dataset. If a header row becomes a column, the first column’s values may become the new header row. For example, a list whose first column contains people’s names may yield one column heading per person. That can be appropriate for a small report, but it may produce duplicate or unsuitable table headers. Rename headings before making the output a table.
For a Paste Special transpose, formulas are copied as formulas, and Excel may adjust relative references based on the new location. Check formulas rather than assuming their references still express the intended calculation. Absolute references behave differently from relative references, and references to a table that you converted to a range may no longer work as structured references.
A TRANSPOSE formula returns the source values as an array result; it does not rotate the source formatting into a fully formatted table. Apply number formats, borders, widths, and styles to the output as needed. Neither method automatically rebuilds data validation, conditional-formatting rules, named ranges, charts, or formulas elsewhere in the workbook to suit the new orientation. Review dependent workbook elements separately.
Choose between Paste Special, a formula, and Power Query
For a one-time rearrangement, Paste Special is usually the most direct option. The pasted output is independent of later edits to the source, which is useful for a report or a handoff. Use a TRANSPOSE formula when you want a live view that follows source changes and can accept the limits of a spill range.
For a repeatable transformation of imported data, Power Query can be a better alternative. In Excel for Microsoft 365 on Windows, choose Data > From Table/Range to open the query editor. In Power Query Editor, use Transform > Transpose. If the first row of the transformed data should be headings, use the command to promote the first row to headers, then choose Home > Close & Load to return the result to Excel.
Power Query stores the steps as a query, so you can refresh the output after the source changes. It is not the same as a live worksheet formula: the output updates when the query is refreshed, and edits made directly to loaded output are not a substitute for changing the source or query. Power Query is especially helpful when the same imported file needs the same layout change each time.
If the question is about a chart rather than worksheet cells, select the chart and use Chart Design > Switch Row/Column. That changes which data the chart treats as series or categories; it does not transpose or rewrite the table. This is often the right answer when the worksheet layout is already useful but a chart has its series and categories reversed.
Common problems and fixes
- Transpose is unavailable: The selected data may still be an Excel table, or the destination may be inside a table. Convert the source to a range if needed and choose a blank destination outside tables.
- The result has too many or too few rows: Recheck the copied range. Including a totals row or extra blank columns changes the dimensions of the result.
- Excel reports a spill error: A cell in the required output area is occupied, merged, or otherwise prevents the dynamic array from filling. Clear the cells or move the formula.
- The new table has awkward headings: Values from the original first column became headings. Rename them, and make sure each heading is distinct before creating the new table.
- Formulas show unexpected results: Inspect adjusted cell references and any references that depended on the original table name. Transposing is a layout operation, not an automatic rewrite of workbook logic.
One common misconception is that sorting a table or using a chart’s Switch Row/Column command will rotate the worksheet data. Sorting changes the order of records, while the chart command changes only the chart’s interpretation. To make the actual cells exchange row and column positions, use a transpose operation.
Frequently Asked Questions
Can I transpose an Excel table without converting it to a range?
Not as a table in place. A formula can display transposed values outside the table, but the spill result is not itself an Excel table. For a pasted result, convert the source to a range if required, transpose into blank cells, and create a new table from the output if needed.
Will the transposed table update when I edit the original?
A Paste Special result is a separate copy and does not automatically update. A TRANSPOSE formula updates when its referenced cells change. A Power Query result updates when you refresh the query.
How do I transpose without changing the original data?
Use a TRANSPOSE formula in an empty area, or copy the source before using Paste Special. The formula leaves the source intact; copying also preserves the original while creating a separate transposed result.
Can I switch rows and columns in an Excel chart only?
Yes. Select the chart and choose Chart Design > Switch Row/Column. This changes the chart’s series and category orientation without rearranging the worksheet cells.
Why can’t I type into the transposed formula’s cells?
In Microsoft 365, TRANSPOSE returns a spilled array controlled by the formula in its top-left cell. Edit or remove that formula to change the output, or copy the output and paste values elsewhere if you need individually editable cells.
Related
- Power Query Reference: What It Does and When to Use It
- Excel Data Validation: Can You Select Multiple Items?
- Protect Sheet in Excel: Allow Editing Specific Cells
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Split Cells: Did Microsoft Remove the Feature?No—Excel did not get rid of a command that splits one cell into smaller cells; Excel has never…
- How to View Protected Ranges in Google SheetsTo view protected ranges in Google Sheets, open the spreadsheet in a web browser, choose Data > Protect…
- Microsoft Excel Consolidate Data: A Practical GuideTo consolidate data in Microsoft Excel for Microsoft 365 on Windows, select the destination cell, then choose Data…
- Can You Automatically Alphabetize Tabs in Google Sheets?Yes, you can alphabetize sheet tabs in Google Sheets, but there is no built-in command that sorts them…
- Excel Dynamic Title on Box Plot: Link It to a CellTo make an Excel box plot title dynamic, put the title text or a formula in a worksheet…
- Excel Find and Replace Asterisk: Find * Literally in CellsTo find a literal asterisk in Excel, press Ctrl+H, enter ~* in Find what, and choose Find All…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.