Transposing in Excel means turning rows into columns or columns into rows. You need it when a table arrives in the wrong orientation, when a chart expects data in the other direction or when you copy a list from a report and want it laid out sideways. Excel offers three main ways to do it: Paste Special, the TRANSPOSE function and Power Query. Each has strengths. This guide shows all three step by step, explains which one to use, covers the common errors such as the spill error and the array entry problem in older versions, and gives examples you can try in a few minutes.
Updated November 2026
In this guide
- What transposing does
- Method 1: Paste Special Transpose
- Method 2: the TRANSPOSE function
- Method 3: Power Query for repeatable work
- Comparison of the three methods
- Worked example: months across to months down
- Transposing a single column into several rows
- Common errors and fixes
- Practice exercise
- Transposing and charts
- Quick recap
- Tips for better results
What transposing does
Transposing rotates a block of cells so that the first row becomes the first column and the first column becomes the first row. A table with 3 rows and 5 columns becomes a table with 5 rows and 3 columns. The values do not change, only their position. For example, if A1:C1 holds Jan, Feb, Mar and A2:C2 holds 100, 120, 90, the transposed version has Jan, Feb and Mar down a column with the numbers next to them. Formulas and formatting may behave differently depending on the method, which is why it helps to choose carefully.
Method 1: Paste Special Transpose
This is the fastest way for a one-time change. The result is static, so it will not update if the source changes.
- Select the range you want to rotate, for example A1:C4.
- Press Ctrl+C to copy.
- Click an empty cell where the new table should start, such as E1. Make sure there is enough empty space, because Excel will overwrite whatever is there.
- Right-click, choose Paste Special, then tick Transpose and click OK. You can also use Home, Paste, Transpose.
The pasted data keeps its formats. If the source contained formulas, they are pasted as formulas whose references may shift, so check them. To avoid that, choose Paste Special, then Values, then Transpose. Delete the original range afterward if you no longer need it.
Method 2: the TRANSPOSE function
The TRANSPOSE function returns a rotated version of a range and keeps it linked to the source. In Excel 365 and Excel 2021, it is a dynamic array formula. Select one empty cell, type =TRANSPOSE(A1:C4) and press Enter. The result spills into the cells below and to the right. Change a value in the source and the transposed copy updates at once. For the full reference, see Microsoft’s page on the TRANSPOSE function.
In Excel 2019 and earlier, you must first select a range of the right size, which for a 3-by-4 source is 4 rows by 3 columns, then type the formula and press Ctrl+Shift+Enter. Excel adds curly braces around the formula to show that it is an array formula. If you press only Enter, the formula returns a single value or a #VALUE! error. To edit, select the whole range and press F2 again with Ctrl+Shift+Enter.
Method 3: Power Query for repeatable work
If you transpose the same report every week, use Power Query. Select your data, choose Data, then From Table/Range. In the Power Query editor, open the Transform tab and click Transpose. If you want the first row to be the header, click Use First Row as Headers. Finally, choose Close and Load. The next time the source data changes, click Data, then Refresh All, and the transposed table updates with the same steps. Power Query is also a good choice when you must clean the data at the same time, such as removing blank rows or changing data types, since it records every step.
Comparison of the three methods
The table below helps you choose.
| Method | Stays linked? | Best for | Watch out for |
|---|---|---|---|
| Paste Special | No | Quick one-time change | Overwrites cells; static values |
| TRANSPOSE function | Yes | Live reports that update | Spill errors; array entry in old Excel |
| Power Query | Yes (on refresh) | Weekly or monthly reports | Needs a refresh; extra setup |
Worked example: months across to months down
Suppose you have sales for four regions across the top in B1:E1 (North, South, East, West) and three months down the side in A2:A4, with numbers in B2:E4. You want regions down the side so that a chart reads better. Select A1:E4 and copy. Click G1, then Paste Special, Transpose. The new table has the heading Months at G1 and the months across the top, with regions down the side in G2:G5. If the figures in the original are 120, 135 and 150 for North, the transposed version has them in the row for North across the three month columns. You can now build a column chart with regions as categories and compare the months side by side.
Transposing a single column into several rows
Sometimes you need to turn one long column into a grid of several columns, for example a list of 12 names into 3 columns by 4 rows. TRANSPOSE alone will not do this. In Excel 365, use =WRAPROWS(A1:A12,4) to wrap the list into rows of 4 values, or =WRAPCOLS(A1:A12,4) to fill columns of 4. In older versions, use INDEX with ROW and COLUMN. These functions are new, so confirm that your version supports them. If you are on an older version, a manual copy and paste of every 4 values with Paste Special Transpose works for small lists.
Common errors and fixes
If you see #SPILL!, something is blocking the cells where the result should appear. Clear the cells or move the formula. If you see #VALUE! in an older version, you probably did not press Ctrl+Shift+Enter or the selected range is the wrong size. If the numbers appear as dates or text, check the cell formats before pasting. If Paste Special Transpose is greyed out, you may have copied a chart or a shape instead of cells, or the clipboard may contain data from another app. If the source range includes merged cells, transposing may fail, so unmerge them first. Finally, remember that you cannot transpose into the same area as the source because the ranges overlap.
Practice exercise
Try this in a blank workbook. In A1:D1 type Q1, Q2, Q3, Q4. In A2:D2 type 400, 450, 380, 520. Select A1:D2 and copy. Click F1, choose Paste Special, tick Transpose and click OK. You should see Q1 to Q4 in F1:F4 and the numbers in G1:G4. Now try the formula version: in I1 type =TRANSPOSE(A1:D2). In Microsoft 365 the result spills into I1:J4. Change the value in A2 from 400 to 410 and notice that the formula version updates and the pasted version does not. This quick test shows why you should pick the method based on whether the data will change.
Transposing and charts
Charts read data by rows or columns, and the wrong orientation gives a messy chart. Instead of transposing the data, you can also click the chart, choose Chart Design, then Switch Row/Column. If the series are still wrong, use Select Data to edit them. Transposing the source is better when you also need the table in a different orientation for printing or reporting. When a chart is based on a TRANSPOSE formula, the chart updates automatically as the source changes, so keep the formula in place and format the result like a normal table.
Quick recap
Use Paste Special Transpose for a fast, static result, the TRANSPOSE function when the data must stay linked and Power Query when the same report arrives every week. Leave enough empty space for the result, check formats and references after pasting and keep a backup of the original table. With these three tools you can flip any table in under a minute.
Tips for better results
Keep a copy of the original data on another sheet before you transpose. Name the new range with a clear heading so others can follow the logic. When using TRANSPOSE inside a larger formula, such as SUMPRODUCT, test the pieces separately. If you build dashboards, link the transposed result to your charts, not the raw data, so changes to the source flow through. For lookups, if a VLOOKUP fails after transposing, read our guide to fixing VLOOKUP problems, since changes in orientation often break references.
Frequently asked questions
How do I transpose data in Excel?
Copy the range, right-click the target cell, choose Paste Special and tick Transpose.
What is the TRANSPOSE function?
It returns a range rotated so rows become columns and columns become rows, and it stays linked to the source.
Why does my TRANSPOSE formula show #SPILL!?
Cells in the result area are not empty. Clear them or move the formula.
Does Paste Special Transpose update automatically?
No. The result is static. Use the TRANSPOSE function or Power Query for linked data.
Can I transpose a table with formulas?
Yes, but check the references after pasting, or paste values first.


