To transpose a vertical list from bottom to top into a horizontal row in Excel for Microsoft 365, enter =TRANSPOSE(SORTBY(A2:A10,ROW(A2:A10),-1)) in an empty cell, replacing A2:A10 with your list. If you only want to reverse the list vertically, use =SORTBY(A2:A10,ROW(A2:A10),-1). Transposing swaps rows and columns; reversing changes the order, so doing both requires both operations.
What bottom-to-top transpose means
People often use “transpose bottom to top” to describe one of two tasks. They may want to flip a column so its last item appears first, or they may want to turn that column into a row while placing the last item at the left. Excel’s Transpose command handles only the change in orientation: it turns a column into a row, or a row into a column. It does not reverse the sequence on its own.
For example, suppose A2:A4 contains North, Central, and South, in that order. A plain transpose produces North, Central, South across a row. Reversing the list vertically produces South, Central, North down a column. Reversing and transposing produces South, Central, North across a row. Decide which result you need before choosing a method, especially if the list is part of a larger table.
The formulas in this guide use a vertical source range. If your source is already horizontal, the same idea applies, but the row-numbering logic must be adapted. For many simple tasks, copying and using Paste Special is more convenient; formulas are better when the source may change and the result should update.
Use a formula to reverse and transpose
In Microsoft 365 and Excel 2021 or later, SORTBY can order the source range by its worksheet row numbers in descending order. TRANSPOSE then spills that reversed list horizontally. With the values to transform in A2:A10 and an empty destination cell such as C2, enter:
=TRANSPOSE(SORTBY(A2:A10,ROW(A2:A10),-1))
The result starts in C2 and spills to the right. The final item from A2:A10 appears in C2, the next-to-last item appears in D2, and so on, ending with the original first item. The destination cells must be empty; if any are occupied, Excel may show a spill error rather than overwrite them. Clear the cells in the expected output row and try again.
If you want the source reversed but still in a column, use the same layout, with A2:A10 as the source and an empty destination such as C2:
=SORTBY(A2:A10,ROW(A2:A10),-1)
This formula sorts by row position, not by the text or numbers in the cells. That distinction matters: sorting words Z to A or numbers largest to smallest would reorder by value, which is not necessarily the same as flipping the original sequence. SORTBY and dynamic arrays are available in Microsoft 365 and Excel 2021 or later. In Excel for the web, these functions can also be used in supported workbooks.
Transpose with Paste Special
Paste Special is useful when you need a fixed copy rather than a formula-linked result. First copy the source cells. Select the destination cell, then choose Home > Paste > Transpose. You can also open the Paste Special dialog from the Paste menu and select Transpose. The command changes a copied column into a row or a copied row into a column.
To get a bottom-to-top result with this method, reverse the source before transposing it. One practical approach for a short list is to copy the values into a helper column in reverse order, then copy that helper range and use Transpose. Paste Special generally creates a static copy: later edits to the original cells do not automatically update the pasted values. If the source contains formulas, choose the appropriate paste option for whether you want formulas or their displayed results.
For a simple list, you can also transpose first and then reverse the resulting row. The main caution is to select exactly the intended cells. If you copy a range containing extra blank cells, those blanks can appear in the transposed output. Check the selected range and the destination area before pasting, and avoid pasting over data you need to keep.
Reverse a list with a fill-down formula
If SORTBY is unavailable in your Excel version, INDEX can return items in reverse order. This method works in older Excel versions and creates a formula for each output cell. Assume the source list is A2:A10 and the first destination cell is C2. Enter this formula in C2:
=INDEX($A$2:$A$10,ROWS($A$2:$A$10)-ROWS($C$2:C2)+1)
Fill the formula down through C10. C2 returns the value from A10, C3 returns A9, and the last output cell returns A2. The first ROWS expression counts the source items; the second increases as the formula is filled down. The dollar signs keep the source range fixed while the destination reference expands. If the list has a different number of entries, adjust the source and destination ranges to match.
To make the reversed result horizontal instead, assume the source is still A2:A10 and the first destination cell is C2. Enter this formula in C2 and fill it right for nine cells:
=INDEX($A$2:$A$10,ROWS($A$2:$A$10)-COLUMNS($C2:C2)+1)
The expanding COLUMNS range counts how many output positions have been filled, so the first output uses the last source item and each next cell moves upward through the source. This formula returns values, not a special transposed link to the source. Like other formulas, it recalculates when the referenced cells change.
Keep related columns together
A frequent mistake is reversing just one column when each entry belongs to a row of related information. For example, a date in column A may correspond to a sales amount in column B and a category in column C. Reversing only the dates would mismatch the records. Treat each complete record as a unit and apply the same ordering to every associated column.
For a sortable table, add a temporary helper column containing each record’s original position, such as 1, 2, 3, and so on. Sort the entire data range—not just the helper or one data column—by that helper column in descending order. This reverses the records while preserving the relationships across columns. If you then need the table turned sideways, copy the reversed full range and use Paste Special > Transpose. Remove the helper column after checking the output if it is no longer needed.
Dynamic array formulas can also work with multi-column ranges, but the sort key must be designed for the intended record order and the output area must have room for the full array. For a multi-column table, do not independently sort each column: that can destroy row relationships. A helper index or a carefully constructed formula is usually easier to audit.
Choose the method that fits the job
- Formula and automatic updates: Use SORTBY with TRANSPOSE when you have Microsoft 365 or Excel 2021 or later and want the output to respond to changes in the source.
- One-time copy: Use Paste Special > Transpose when you want a static result and do not need it linked to the original.
- Older Excel compatibility: Use INDEX with ROWS or COLUMNS and fill the formula down or across.
- Multiple fields per record: Reverse complete rows using a helper index, then transpose the full range if needed.
Consider whether the source is likely to grow. A formula referencing A2:A10 will not automatically include new entries typed in A11 unless you update the range or use a suitable expanding source, such as an Excel table with an appropriately written formula. For a small, fixed list, a defined range is straightforward. For an evolving dataset, a table or a repeatable sort-and-paste process may be more manageable.
Common problems and how to fix them
- The order did not reverse: A plain TRANSPOSE changes orientation only. Add a reversal step, such as SORTBY with descending row numbers, or reverse the source before pasting.
- The formula returns an error: Check that the source range is valid and that the destination area is clear. For the dynamic formula, confirm that your Excel version supports SORTBY and spilling arrays.
- The output is in the wrong direction: Confirm whether you want the last source item at the left or at the right. Reversing a vertical range and then transposing places the original bottom item at the left of the result.
- Some rows no longer match: Undo if possible and repeat the operation on the entire table. Sort all related columns together, not one column in isolation.
- Blank cells appear in the result: Check whether the copied or referenced source range includes blank cells. A range-based formula includes the cells you specified, even when some are empty.
When a dynamic formula reports a spill problem, look across the full expected output area, not only the first destination cell. A value, another formula, or a merged cell in the spill range can prevent Excel from returning the array. Clear or move the obstruction, then recalculate. If the destination overlaps the source range, choose a separate empty area to avoid overwriting or circular-reference issues.
Excel on Mac and Excel for the web
The core formula approach is the same in current Excel for Windows and Mac when the relevant functions are available. Menu and ribbon layouts can vary slightly by platform or update, but Paste Special includes a Transpose option in current desktop Excel. Excel for the web also supports transpose pasting and many dynamic-array functions, although available commands and interface details can differ from the desktop app. If a workbook is opened in an older desktop version, newer functions such as SORTBY may not calculate there.
Keyboard shortcuts can also vary by operating system and Excel release, so the ribbon route is a dependable way to find the command: copy the cells, select the destination, and use Home > Paste > Transpose. On Mac, use the Paste menu or ribbon equivalent rather than assuming a Windows Paste Special shortcut applies. Always check that the resulting orientation and order match the intended output.
Frequently Asked Questions
Does Excel transpose reverse the order?
No. Transpose swaps rows and columns but preserves the order of items. To get bottom-to-top order, reverse the source separately or combine SORTBY and TRANSPOSE in a formula.
How do I flip a column upside down in Excel?
In Microsoft 365 or Excel 2021 and later, with the list in A2:A10 and an empty output cell, use =SORTBY(A2:A10,ROW(A2:A10),-1). In older Excel, use the INDEX and ROWS fill-down method described above.
Can I transpose and reverse without changing the original?
Yes. Put the formula in a separate empty area to create a reversed, horizontal result while leaving the source cells intact. Paste Special also leaves the source unchanged, but its pasted output is normally a static copy.
Why does my transposed formula show a spill error?
Excel needs an empty destination area large enough for every result. Clear cells blocking the spill range, unmerge cells there if necessary, and make sure the formula is not trying to spill inside an Excel table.
Will the result update when I edit the source?
A formula result updates when its referenced values change. A Paste Special result does not stay linked to the source, so you must paste it again to refresh it.
Can I reverse a table without mixing up its columns?
Yes. Sort the entire table by a helper column that records the original row order, in descending order. Do not sort only one field; keeping the full records together preserves each row’s associations.
Related
- Excel Shortcut for Inserting Rows Above or Below
- Excel Shortcut to Insert Another Row in Windows and Mac
- Excel Shortcut to Go to a Specific Column in Seconds
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Consolidate Function: How to Combine DataTo use Excel’s Consolidate function, select the top-left cell for the results, then choose Data > Consolidate, select…
- Excel Formula for the First 3 Digits: Text or Numbers?To return the first three characters from a value in cell A2, use =LEFT(A2,3). The result is text,…
- Round Up in Excel: Use ROUNDUP for Any Number or DecimalTo round a value in Excel away from zero, use =ROUNDUP(A2,0) when the number is in cell A2…
- 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…
- Excel Formula for Percentage of a NumberTo calculate a percentage of a number in Excel, multiply the number by the percentage: if A2 contains…
- How to Find Duplicate Entries in Excel with FormulasTo find duplicate entries in Excel, select the cells you want to check and choose Home > Conditional…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.