Use Paste Transpose for a one-time layout change. Use TRANSPOSE when the result should update with the original values. Check the reversed dimensions and leave the destination empty before you start.
Desktop Excel walkthrough. Microsoft 365 and Excel 2021/2024 spill formulas use different entry steps from legacy array formulas in Excel 2016/2019.
Understand the change in dimensions
Open the sample CSV. It places this small table in A1:D3:
| Item | Jan | Feb | Mar |
|---|---|---|---|
| Notebooks | 120 | 160 | 140 |
| Pens | 80 | 90 | 120 |
A three-row, four-column source becomes four rows and three columns. Starting the result at F1 therefore uses F1:H4. Pick an empty destination that does not overlap the original table.
| Item | Jan | Feb | Mar |
|---|---|---|---|
| Notebooks | 120 | 160 | 140 |
| Pens | 80 | 90 | 120 |
| Item | Notebooks | Pens |
|---|---|---|
| Jan | 120 | 80 |
| Feb | 160 | 90 |
| Mar | 140 | 120 |
Make a one-time transposed copy
- Select A1:D3, including the headings, and copy it with Ctrl+C. Use Copy for this operation.
- Select the empty destination cell F1.
- Open Home → Paste and choose Transpose. Some versions also offer it in the right-click Paste Options.
- Check that the result occupies F1:H4. On Mac, copy with Command+C and use the transpose option in the Home tab’s Paste menu.
This example contains values only, so its pasted copy does not update when those source values change. When copying formulas, inspect the adjusted references and results after pasting.
Keep results linked with TRANSPOSE
In Microsoft 365 or Excel 2021/2024, leave the output area empty, enter this formula in F1, and press Enter:
=TRANSPOSE(A1:D3)The result spills into F1:H4. For example, changing B2 from 120 to 125 updates G2 to 125. Edit the original data or the formula’s top-left cell; the individual cells in a spill result are not independent input cells.
Use the legacy array-entry method when needed
In an Excel version without dynamic-array spilling, preselect the complete destination. For this example, that is F1:H4.
- Select the four-row, three-column destination F1:H4.
- Keep the selection and type =TRANSPOSE(A1:D3).
- On Windows, confirm with Ctrl+Shift+Enter to enter a legacy array formula.
The selected output dimensions must fit the transposed source. Choose the method for your Excel version rather than combining the legacy selection steps with modern spill instructions.
Troubleshoot #SPILL! and unavailable paste options
| Problem | What to check |
|---|---|
| #SPILL! | Look for existing values, formulas, or merged cells in F1:H4. Inspect existing data before removing it, or move the output to a different empty area. |
| Paste Transpose is unavailable | The copied source may be in an Excel table. Consider using TRANSPOSE in a separate destination. |
| A spill formula is inside an Excel table | Enter the formula in an empty area outside the table. |
| An empty source cell becomes zero | TRANSPOSE can display source blanks as zero. Decide how missing values should differ from genuine zeros before relying on the result. |
Transposing changes the table’s orientation. For a left-to-right rearrangement of fields such as Name and Amount, use the column-moving guide.
Check the finished table
- Confirm that the 3 × 4 source became a 4 × 3 result.
- Check the headings Item, Notebooks, and Pens, and compare the numbers for each month.
- Change a sample value to confirm whether your output is linked or independent, as intended. Restore any temporary test changes before saving.
If you are preparing source data for a chart, the stacked-chart total-label guide provides a next practice example.
Common questions
Can I delete the original data?
A TRANSPOSE formula depends on its source range. Check those references first. If you need an independent table, you can paste the resulting values into a separate range.
Will adding source rows or columns automatically extend this formula?
The example explicitly references A1:D3. Data outside that rectangle is not included. Review the reference and the available destination space when extending the source.
Are the phone-app steps identical?
Menus and array-formula support depend on the app and version. The desktop menu names and keyboard sequences here should not be assumed to apply to mobile Excel.
Download the example
Example for this guideFree download · .csvUTF-8 CSV data. Import with Data → From Text/CSV. Set identifier columns to Text when leading zeros matter. Charts and formulas are added using the steps in this guide.
Menus and available features can differ by Excel version and platform. Check the example on a copy before applying it to important workbooks.