To transpose data in Excel with a formula, select a blank cell where the transposed result should begin and enter =TRANSPOSE(A1:C2), replacing A1:C2 with your source range. In Microsoft 365, press Enter: Excel spills the rows and columns into a new orientation, and the result updates when the source data changes. Before you start, make sure the destination area is empty and large enough for the result.
What You’ll Need
- An Excel workbook containing the data you want to turn from rows into columns, or columns into rows.
- A blank destination area. Its dimensions must be the reverse of the source range: a source with two rows and three columns needs a destination three rows high and two columns wide.
- Microsoft 365 or a recent Excel version with dynamic arrays is simplest. Older versions can use TRANSPOSE as a legacy array formula, as explained below.
Step-by-Step
- Identify the exact source range. Count the rows and columns you want to transpose, including any headings. For example, imagine A1:C2 contains two rows and three columns: the first row has Name, Department, and Score; the second has Ana, Sales, and 90. The result should have three rows and two columns. Avoid selecting unrelated notes or blank rows unless you want them in the output.
- Choose the top-left destination cell. Click a blank cell outside the source range, such as E1. Excel will put the first transposed value in this cell and fill the rest of the result to its right and below. Keep the destination separate from the source; overwriting source cells can prevent the formula from working or destroy data.
- Check that the full spill area is clear. With a two-row-by-three-column source, leave E1:F3 empty. Remove existing values, formulas, merged cells, or other obstacles from every cell in that area. A cell that looks blank but contains a space or an old formula can still block the output. If you are unsure, select the destination area and use Home > Clear > Clear Contents, taking care not to clear anything you need.
- Enter the TRANSPOSE formula. With the example data in A1:C2 and E1 as the destination cell, type
=TRANSPOSE(A1:C2)in E1. Press Enter in Microsoft 365 or another dynamic-array version of Excel. Excel returns the original first column as the first output row, the second source column as the second output row, and the third source column as the third output row. - Check the result and its dimensions. The example produces Name and Ana in E1:E2, Department and Sales in F1:F2, and Score and 90 in the final output row E3:F3. Put another way, E1:F3 is a three-row, two-column result. If the first output value appears in E1 but the rest do not appear, look for a spill error or check whether you are using an older Excel version.
- Confirm that the formula is linked to the source. Change a source value, such as Sales in B2, and check the corresponding output cell. A dynamic TRANSPOSE result recalculates when its source changes. The output is not a separate, independently editable copy: you cannot type over one of its spilled cells while the formula controls that area. Edit the original source instead.
- Adjust the source range if your data changes size. The formula refers only to the cells in the range you selected. If you add another source row below row 2, the existing formula will not automatically include it. Edit the range in the formula—for example, change its ending row—and check that the destination has enough empty cells for the enlarged result.
- Use the legacy array method only if needed. In an Excel version without dynamic arrays, first select a destination range with the reversed dimensions. For the example source A1:C2, select a three-row-by-two-column area such as E1:F3. Type the same TRANSPOSE formula, then confirm it with Ctrl+Shift+Enter on Windows rather than Enter alone. On Mac, the legacy array entry shortcut is Control+Shift+Return. Excel displays braces around a legacy array formula; do not type the braces yourself. In these older versions, the result range is fixed and must be selected again if you need a different size.
The formula approach is useful when you want a transposed view that follows edits to the original data. It does not make a one-time, independently editable copy. If you need to rearrange data once and then edit the result separately, Copy and Paste Special > Transpose is a different, non-formula method.
Common Mistakes
- Putting something in the spill area. In current Excel, TRANSPOSE returns an array. If any cell in the required output area is occupied, Excel cannot place the complete result and may show #SPILL!. Check the entire destination rectangle, not only the cell containing the formula.
- Selecting a destination with the wrong shape in an older version. The output dimensions reverse the source dimensions. Three source rows by four columns become four rows by three columns, not three rows by four columns.
- Trying to edit one returned value. In a dynamic-array version, the formula in the top-left cell controls the whole spill. Edit the source value or change the formula instead of typing into an individual result cell.
- Expecting the formula to copy appearance or structure. TRANSPOSE rearranges cell results; it does not reproduce source formatting, column widths, row heights, data validation, comments, or merged-cell layout. Format the output separately if needed.
- Assuming new rows are included automatically. A direct range reference has fixed start and end cells. Adding data outside that range does not expand the formula’s input by itself.
Tips
- Include a heading row in the source selection if you want the headings transposed along with the data. Leave it out if you want only the records.
- For the clearest result, put the formula on a separate worksheet or well away from the source. This makes it easier to preserve the input and spot the output boundaries.
- After entering the formula, select its top-left cell to see or edit the complete formula in the formula bar. In dynamic-array Excel, selecting a cell elsewhere in the result does not make that cell the formula’s starting point.
- Format the transposed output after it appears. You can apply number formats, borders, and column widths to the output without changing the source data, although the formatting will not necessarily follow later changes to the source layout.
- If source cells contain formulas, the transposed output reflects their calculated results and can recalculate when those source formulas change. Do not assume that the original formulas themselves have been copied into a new, independent set of cells.
- When you need the result to include future additions, plan how the source range will grow. Update the range reference when you add rows or columns, or use a deliberately larger range only if you are comfortable with the extra blank entries it may produce.
Troubleshooting
- #SPILL! appears: Click the error indicator or select the formula cell to inspect the blocked range. Clear contents from the obstructing cells, unmerge cells if necessary, and make sure the destination is not inside an Excel Table. Dynamic-array formulas cannot spill into cells occupied by a table. Then leave the formula in its top-left cell and let Excel recalculate.
- The result is only one cell or does not expand: Check the Excel version. Dynamic arrays are supported in Microsoft 365 and newer Excel releases, but older versions need the legacy array method. In an older version, select the full reversed-size destination range before entering the formula and confirm with the array shortcut.
- There are zeros where the source looked empty: A blank cell included in an array calculation may appear as 0 in the returned results. Check whether the source cell is truly empty or contains a formula returning an empty string, and decide whether a visible zero is appropriate for your report. If not, you can leave the source area out of the selected range or adjust the source data so blanks are represented as you want.
- The output does not show the newest source entries: Select the formula cell and inspect the range inside TRANSPOSE. Extend its end row or column to include the new data, then check that the spill area has room for the larger output. A formula cannot include cells that are outside its reference.
- The output looks different from the source: Check number formats and formatting separately; they are not transposed by the formula. Also verify that you selected the intended source cells, including or excluding headers as needed. If a value itself is unexpected, compare the corresponding source cell rather than editing the result directly.
Frequently Asked Questions
Does TRANSPOSE keep the output linked to the original data?
Yes. In dynamic-array Excel, the spilled result recalculates when the referenced source cells change. It is a formula-driven view, not a detached copy.
Can I transpose a column into a row?
Yes. Select the vertical source range and use TRANSPOSE in a blank cell. A source with five rows and one column returns one row with five columns, provided the destination cells are clear.
Can I edit just one cell in a transposed result?
Not in a dynamic spill range. Edit the corresponding source cell, or make a separate static copy if you need to change the transposed values independently.
Will TRANSPOSE copy cell colors and column widths?
No. It returns the data results in a different orientation, not the full cell formatting or dimensions. Apply the formatting you need to the destination after the formula returns its values.
Why does Excel say the formula cannot be changed in part of an array?
You may have selected one cell inside a legacy array formula. In older Excel, select the entire array range to edit or delete the formula. In Microsoft 365, change the formula in the top-left spill cell rather than an individual returned cell.
Can I use TRANSPOSE in Excel for the web or on a Mac?
Current Excel for the web and Microsoft 365 for Mac support dynamic arrays, so the usual process is to enter the formula in a blank cell and press Enter. Older Mac or Windows Excel versions may require selecting the full output range and confirming the formula as a legacy array formula.
Related
- Save an Excel Workbook as a Copy Without Replacing It
- How to Save As in Excel and Create a New Copy
- How to Save Excel Files to Google Drive
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP and HLOOKUP in Excel: Formulas and UsesIn Excel, VLOOKUP searches down the first column of a table and returns a value from a column…
- How to Concatenate in Excel and Keep Cell FormattingTo concatenate values and keep dates, currency, percentages, or leading zeros readable, convert those values to formatted text…
- SUMIF Excel Examples With Two Criteria to Match RowsTo total values that match two conditions in Excel, use SUMIFS rather than SUMIF. With regions in A2:A8,…
- Excel Formula for MID: Extract Text Like a ProThe Excel formula for extracting text from the middle of a string is =MID(text, start_num, num_chars). For example,…
- Excel Delete Table but Keep Data: The Right StepsTo delete an Excel table but keep its data, convert the table to a normal range: click a…
- Excel Rank Formula Without Duplicate Ranks: ExamplesTo give tied values different, consecutive ranks in Excel, enter =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1 beside the first value and fill down.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.