To reverse rows in Excel, add a temporary helper column with a number for each row, then sort the entire data range by that column from largest to smallest. This reverses the row order while keeping the values in each record together; in Microsoft 365 or Excel 2021, you can also use a formula to return a reversed copy.
The quickest method: add a helper column
A helper column gives each row a value that records its current position. Sorting those values in descending order puts the last record first, the next-to-last record second, and so on. This method works with text, numbers, dates, and mixed records, and it does not require you to rearrange the contents of individual cells by hand.
- Identify the complete data range, including every column that belongs to each record. For example, a list with names in A, departments in B, and start dates in C might occupy A1:C6, with headers in row 1.
- Add a new column beside the data and give it a clear header, such as Original order.
- Enter a sequence of numbers in the rows of the data. If the data has five records, use 1 through 5. You can type the first two numbers, select both cells, and drag the fill handle down to continue the sequence.
- Select a cell in the data range and choose Data > Sort. If Excel asks whether to expand the selection, choose Expand the selection so the values in all columns move together.
- In the Sort dialog, select the helper column as Sort by, and choose Largest to Smallest. If the dialog offers My data has headers, ensure it is selected when your range includes a header row.
The row marked 5 now appears first, followed by 4, 3, 2, and 1. Check the first and last records to confirm the direction is right, then delete the helper column if you no longer need it. Removing that column does not undo the sort: the records stay in their new order.
Using a formula for the helper numbers
Typing a sequence is convenient for a short list. For a longer range, a formula can generate the helper numbers consistently. With data in A2:D6 and the helper column in E, enter this in E2, then fill it down through E6:
=ROWS($A$2:A2)
The formula returns 1 in E2, 2 in E3, and continues to 5 in E6. Sort the whole data range by column E from Largest to Smallest. The records from rows 2 through 6 will then appear in the order 6, 5, 4, 3, 2. The formula counts how many rows are in the expanding range, so each row gets its own position number as you fill down.
Make sure the helper column is included in the range being sorted. Sorting only the numbers in E would change their order without moving the corresponding records, which would disconnect the helper values from the data they are meant to track. If the range has headers, give the helper column a header too, rather than sorting its header as if it were a record.
Return reversed rows with a dynamic-array formula
If you want a reversed result without rearranging the original data, use SORTBY. It returns a sorted copy in a separate area of the worksheet. This is useful when the source order needs to remain unchanged, or when you want to compare the original list with its reverse.
With records in A2:C5 and no header row included in that range, enter this formula in an empty cell such as E2:
=SORTBY(A2:C5,ROW(A2:A5),-1)
The result spills across three columns and four rows, showing source row 5 first, then rows 4, 3, and 2. ROW supplies the source row numbers as the sort keys, and -1 requests descending order. Keep the cells where the result will appear empty; otherwise Excel may display a spill error. The formula does not move or overwrite the original records. SORTBY and dynamic arrays are available in Microsoft 365 and Excel 2021 or later.
For a source range that changes size often, adjust the range references to include the current records before relying on the result. A formula tied to A2:C5 will not automatically include new rows added below row 5 unless the source is set up to expand, for example as an Excel table. The formula result is also not a substitute for a sorted source list if another process expects the original cells themselves to be in reverse order.
Reversing rows in an Excel table
Excel tables have built-in filter and sort controls in their header cells. To reverse a table, add a column named Original order, number the records from top to bottom, and use the helper column’s header arrow to sort Largest to Smallest. Sorting by a table column rearranges the table’s records, keeping values in each row together.
When you add a calculated formula column to a table, Excel will commonly fill the formula down the column. Check that every record has a distinct position number before sorting. If the table has a total row, it is not a data record; use the table’s sort control rather than including the total row in a separately selected range. The total row should remain at the bottom of the table.
If you need to restore the original order later, keep the helper column instead of deleting it. Sorting that column from Smallest to Largest returns the records to their previous sequence, provided the helper values were not changed. This can be helpful for a working table that users sort in different ways.
Choose the method that fits the task
The helper-column method is usually the most dependable choice when you need to reverse the existing records in place. It works in older Excel versions as well as current ones, and it makes the intended order visible. A dynamic-array formula is a better fit when you need a separate, automatically calculated display and have a supported Excel version.
- Use a helper column to change the order of the actual data, share a reordered list, or work in a version without dynamic-array functions.
- Use SORTBY to show a reversed copy while preserving the source list, or to build a result that updates when its source values change.
- Use the table sort controls when your information is already formatted as an Excel table and you want the sort to stay within that table.
For a tiny range, manually cutting and inserting rows can seem faster, but it is easy to skip a row or move only part of a record. A numbered helper column is more reliable and makes the desired order explicit. Excel does not have a single universal Reverse Rows command on the ribbon, so sorting by a sequence is the common general-purpose approach.
Common sorting problems and how to avoid them
The most serious mistake is sorting just one column of a multi-column list. If you sort names but leave their departments and dates in place, the values no longer describe the same people. Select the whole range or choose Expand the selection when Excel prompts you. Before confirming, check that each column that belongs to a record is included.
A second source of confusion is the header row. If Excel treats a header as a record, it may move into the sorted data. Include the header and identify it in the Sort dialog, or select only the data rows and exclude the header. The helper column also needs a header if you are sorting a range that has headers.
- Duplicate helper numbers: Two rows with the same position do not have a uniquely defined order. Check that the sequence increases by one for every record.
- Blank rows inside the list: A blank line can make the selected range or filled sequence stop early. Remove or account for blank rows before sorting if the whole dataset must reverse as one block.
- Active filters: A filter can hide records from view. Clear filters and review hidden rows before sorting if you need to account for the entire dataset, not just the visible subset.
- Partially selected data: Confirm the selection covers every relevant column and every record. If you are unsure, click inside a well-formed Excel table and sort using its header control.
Sorting is normally reversible with Undo immediately afterward: press Ctrl+Z on Windows or Command+Z on Mac. Undo can also reverse other recent workbook changes, so use it promptly and check the result. A helper column is a more deliberate way to return to the original sequence after other edits have taken place.
Excel for Mac and Excel for the web
The helper-column method is also suitable for Excel for Mac. The ribbon labels and Sort dialog are broadly similar; select the complete range, use Data > Sort, and choose the helper column with Largest to Smallest. Mac keyboard shortcuts differ from Windows shortcuts, but no keyboard shortcut is required for this procedure. For undo, use Command+Z on Mac; on Windows, use Ctrl+Z.
Excel for the web also supports sorting ranges and tables using the Data tab or table header controls. The exact prompts can vary with the selection, so check that the complete range is being sorted and that headers are recognized. The SORTBY formula works in current Microsoft 365 versions of Excel for the web, but formula availability can depend on the version and account. If a function is not recognized, use the helper-column method instead.
Frequently Asked Questions
Can I reverse rows without changing the original data?
Yes. Enter the SORTBY formula in a blank area to return a separate reversed copy. The original range stays in its existing order, while the formula output changes when its source values change.
How do I reverse rows without a formula?
Add a helper column and type a sequence such as 1, 2, 3, and 4 beside the records. Sort the complete range by that column from Largest to Smallest. This does not require a worksheet formula.
Will sorting by a helper column keep each row together?
Yes, as long as you sort the complete data range or choose Expand the selection. If you sort only the helper column, the other values will not follow their records.
Can I reverse the order of rows in an Excel table?
Yes. Add a position-number column to the table, then use its header dropdown to sort Largest to Smallest. The table sort keeps the columns in each record together.
How do I reverse the sort and restore the original order?
If you kept the helper numbers, sort them from Smallest to Largest. If you just performed the sort and made no intervening changes, use Undo: Ctrl+Z on Windows or Command+Z on Mac.
Related
- Excel Dependent Drop-Down Lists With Spaces: Setup Guide
- Excel Dependent Drop-Down List: 3 Levels
- Excel Dependent Drop-Down Lists with Dynamic Ranges
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Hanging Indent in Word Online: A Step-by-Step GuideTo add a hanging indent in Word Online, select the paragraph or paragraphs, choose View > Ruler, then…
- Add Data Validation in Excel: Lists, Rules, and FixesTo add data validation in Excel for Microsoft 365 on Windows, select the cells, then choose Data >…
- Excel Keyboard Shortcut to Select a Row in Any WorksheetIn Excel for Microsoft 365 on Windows, press Shift+Space to select the entire worksheet row containing the active…
- Create a Project Schedule in Google Sheets: A Practical GuideCreate a project schedule in Google Sheets by building a task table with owners, start and finish dates,…
- Excel Cell Size in CM: What the Measurements MeanExcel does not give every cell a single size in centimeters. A cell’s width comes from its column-width…
- Excel Mac: How to Insert a Row Above or Below a TableTo insert a row in Excel for Mac, select the worksheet row header where the new row should…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.