The simplest Excel formula for transposing data is =TRANSPOSE(array), entered as an array formula by pressing Ctrl+Shift+Enter (or just Enter in Excel 365). For example, to turn a column of names in A1:A5 into a row, select B1:F1, type =TRANSPOSE(A1:A5), and press Ctrl+Shift+Enter. This article explains that formula, the older copy-paste method, and how to choose between them.
What Does Transpose Mean in Excel?
Transposing swaps rows and columns. If your data is arranged vertically (down a column), transposing makes it horizontal (across a row), and vice versa. This is common when you receive data from another system or person and need to reorient it for a report, chart, or formula. Excel offers two main ways: the Paste Special command (a static copy) and the TRANSPOSE function (a dynamic link).
The TRANSPOSE Function: How It Works
The TRANSPOSE function is a built-in array function. Its syntax is simple: =TRANSPOSE(array). The array can be a range of cells, a named range, or an array returned by another function. Because it is an array formula, you must select the entire output range before entering it. In Excel 365 and Excel 2021, you can just press Enter because dynamic arrays spill results automatically. In older versions (Excel 2019 and earlier), you must press Ctrl+Shift+Enter to create a legacy array formula, which shows curly braces {} around the formula in the formula bar.
Example: Suppose you have sales figures in A2:A10 (months) and B2:B10 (amounts). To transpose both columns into rows, select D1:E1 (for headers) and D2:E2 (for data) – actually, you need a range of 2 rows by 9 columns. Select D1:L2, type =TRANSPOSE(A1:B10), and press Ctrl+Shift+Enter (or Enter in 365). The result will place the months across row 1 and amounts across row 2.
How to Use Paste Special to Transpose (Static Copy)
The most common method for a one-time transpose is Paste Special. It creates a static copy – if you change the original data, the transposed copy does not update. Steps:
- Select the range you want to transpose (e.g., A1:C5).
- Press Ctrl+C to copy.
- Click on the cell where you want the transposed data to start (e.g., E1).
- Go to Home > Paste > Transpose (or right-click and choose Paste Special, then check the Transpose box).
- Press Enter.
On a Mac, the ribbon path is the same (Home tab > Paste > Transpose). This method works in all versions of Excel, including Excel for the web.
TRANSPOSE vs. Paste Special: Which to Choose?
Choose Paste Special when you need a one-time conversion and the original data won’t change. It is simpler and does not require array formula knowledge. Choose the TRANSPOSE function when you want the transposed data to update automatically if the original data changes. This is useful for dashboards or reports linked to live data. However, note that the TRANSPOSE function cannot be edited partially – you must edit the entire array. Also, if you delete the original data, the transposed result will show a #REF! error.
Common Problems and How to Fix Them
- #VALUE! error: This often happens when you select the wrong number of cells for the output range. The output range must have the same number of rows as the original has columns, and the same number of columns as the original has rows. For example, transposing a 3×4 range requires a 4×3 output range.
- #SPILL! error: In Excel 365, this occurs when the spill range is blocked by existing data. Clear the cells where the result would spill, or move the formula to an empty area.
- Can’t edit part of an array: If you try to change a single cell in a legacy array formula, Excel warns you. You must select the entire range, edit the formula, and press Ctrl+Shift+Enter again.
- Transpose a table with formulas: Paste Special Transpose copies the results, not the underlying formulas. If you need the formulas transposed, use the TRANSPOSE function, but be aware that relative references may change unexpectedly.
Alternatives: Power Query and OFFSET
For large or complex data sets, Power Query (Get & Transform) offers a robust transpose. Go to Data > From Table/Range, then in the Power Query editor, use Transform > Transpose. This is ideal when you need to automate the process or combine multiple files. Another formula-based alternative is using OFFSET with row and column indices, but that is more complicated and rarely needed. For example, =OFFSET($A$1, COLUMN()-1, ROW()-1) can transpose a range, but it is volatile and error-prone.
Frequently Asked Questions
How do I transpose without using a formula?
Use the Paste Special method: copy the range, right-click the destination, choose Paste Special, check Transpose, and click OK. This creates a static copy that does not update.
Can I transpose a range with blank cells?
Yes, blank cells are transposed as blank cells. Both Paste Special and the TRANSPOSE function handle blanks without errors.
Does TRANSPOSE work in Google Sheets?
Yes, Google Sheets has a TRANSPOSE function with the same syntax, but you don’t need Ctrl+Shift+Enter – just press Enter. The Paste Special method is also available under Edit > Paste Special > Transpose.
Why does my TRANSPOSE formula show #VALUE! when I edit it?
If you edit a legacy array formula (created with Ctrl+Shift+Enter) and press Enter instead of Ctrl+Shift+Enter, Excel treats it as a regular formula, causing #VALUE!. Always press Ctrl+Shift+Enter after editing.
How do I transpose a row into a column?
Select a vertical range of cells (e.g., A1:A5), copy, then right-click the first cell of the horizontal range (e.g., B1), choose Paste Special > Transpose. The row will become a column.
Keyboard Shortcuts for Transpose
There is no single keyboard shortcut for transpose, but you can use the Alt key sequence: Alt > H > V > T (Home tab, Paste, Transpose) on Windows. On a Mac, use Cmd+C to copy, then Ctrl+Cmd+V to open Paste Special, and check Transpose. For the TRANSPOSE function, the shortcut is just typing the formula and pressing Ctrl+Shift+Enter (or Enter in 365).
Related
- Excel Audit Process: Step-by-Step Guide
- Excel Audit Log Template: How to Build One
- Excel Auto Loan Amortization Template: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Filter Out Strikethrough Text in Excel: A Practical GuideExcel for Microsoft 365 does not have a built-in filter condition for strikethrough formatting. To filter those rows,…
- Excel Formula for Checking Account RegisterThe core formula for a checking account register in Excel is a running balance: =SUM($C$2:C2) (or =C2 for…
- AVERAGEIF Formula in Excel: Syntax and Practical ExamplesIn Excel, use =AVERAGEIF(range, criteria, [average_range]) to average numbers that meet one condition. For example, with departments in…
- Excel Formula for Weekend vs. Weekday DatesIn Excel, use WEEKDAY to tell a weekend from a weekday: if the date is in A2, =WEEKDAY(A2,2)>5…
- Excel Julian Date Formula: Day of Year and MoreFor a date in A2, use =INT(A2)-DATE(YEAR(A2),1,0) to return its day number within the year: January 1 is…
- Excel Filter Shortcut: Turn AutoFilter On or Off FastIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off. To filter the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.