To flip a list upside down in Excel, add a numbered helper column, sort the whole list by that column from largest to smallest, then remove the helper. For a formula-based result instead, use INDEX or, in Microsoft 365 and Excel 2021 or later, SORTBY. If your list has related information in other columns, keep each full row together while reversing it.
What You’ll Need
- An Excel workbook with the list you want to reverse. These directions are for Microsoft Excel for Microsoft 365 on Windows.
- A blank column for a temporary helper or formula results, if you choose either of those methods. Leave enough empty space below and beside a formula that spills into multiple cells.
- If the list has names, dates, or other details in adjoining columns, identify the full data range first. Sorting only one column can disconnect values from their related records.
You do not need an add-in or a special setting. Save a copy first if the workbook contains important data, especially before sorting a large table. The helper-column method changes the order of the original rows; formula methods create a separate reversed result and leave the source list in place.
Step-by-Step
- Choose the right method and identify the list. Use the helper-column method when you want to reverse the original list in place, or when you need to keep several columns together. Use a formula when you want a separate reversed copy that updates when the source changes. Check whether your range has a header row, and note the first and last data rows. For example, a list in A2:A6 has five data entries and no header within that range.
- Reverse the original list with a helper column. Insert or use an empty column beside the list, and label its top cell something recognizable, such as Original order. Enter 1 next to the first data item, 2 next to the second, and continue until every item has a number. If there are many entries, enter 1 and 2 in the first two helper cells, select both, then drag the fill handle down; check that Excel has created a continuous sequence. This sequence records the current order so a sort can reverse it reliably.
- Select the complete range before sorting. Select all columns that belong to the records, including the helper column, from the header row through the last data row. If there is no header, select all data rows. Do not select just the helper numbers or just the list column when other columns contain related details. On the ribbon, choose Data > Sort. In the Sort dialog, select the helper-column heading in Sort by, choose Largest to Smallest for Order, and confirm that My data has headers is checked only if the first selected row really contains headings. Select OK. The item with the highest sequence number should now be first, and the item numbered 1 should be last.
- Check the result, then remove the helper. Compare the first and last entries with the original list to confirm the direction. If the order is correct, delete the temporary helper column or clear its contents. If you delete a worksheet column, make sure it is only the temporary helper and not a column containing useful information. The reversed list will remain in its new order after the helper is removed.
- Create a reversed copy with INDEX. This option works in older Excel versions as well as Microsoft 365. Suppose the source entries are in A2:A6 and you want the reversed copy in B2:B6. With those cell ranges, enter this formula in B2:
=INDEX($A$2:$A$6,ROWS($A$2:$A$6)-ROWS($B$2:B2)+1). Press Enter, then drag the fill handle from B2 down through B6. The first result will be the value from A6, and the last result will be the value from A2. In this example, the formula counts backward through the five source positions. Adjust both source ranges and the output starting cell if your list is in different rows; keep the first range fixed with dollar signs, and let the second ROWS range expand as you fill down. - Use a dynamic formula in Microsoft 365 or Excel 2021 and later. If you want a result that spills automatically and recalculates when source values change, use SORTBY with SEQUENCE. With the source list in A2:A6, click an empty cell such as B2 and enter:
=SORTBY(A2:A6,SEQUENCE(ROWS(A2:A6)),-1). Press Enter; Excel returns five results vertically, in reverse order. The -1 sorts the generated row numbers from largest to smallest. Do not fill this formula down: it is a dynamic-array formula that supplies the whole result at once. To reverse whole records, with the related data in A2:C6, enter this formula in an empty cell:=SORTBY(A2:C6,SEQUENCE(ROWS(A2:A6)),-1). It spills all three columns while keeping each row intact. Both examples assume the source data has no header row inside the stated ranges.
The ribbon path for sorting is Data > Sort in current Excel for Windows and Mac. In Excel for the web, select the complete range and use the Data tab’s Sort commands; if Excel asks whether to expand the selection, choose the option that expands it so related columns stay together. Dynamic-array formulas are available in Excel for the web when supported by the workbook and account.
Common Mistakes
- Sorting only the visible list column. If other columns describe the same records, sorting one column alone can pair names with the wrong dates, amounts, or notes. Select the entire table before sorting, or use the multi-column SORTBY example.
- Including a heading as if it were an entry. A text header may sort separately from the numbered data or appear in the wrong place. Include the header in the selected range and mark My data has headers in the Sort dialog, or exclude it from the data range and sort the data only.
- Using an incomplete helper sequence. Repeated or skipped numbers can make the order unexpected. Check that the helper begins at 1 and increases by exactly 1 for every data row before sorting.
- Overwriting the source with a formula. If you put the output formula inside the source range, it may replace data or create a circular reference. Place formula results in a separate empty area, then copy and paste values only if you need a permanent reversed copy.
- Expecting formulas to include new rows automatically. A formula written for A2:A6 only refers to those five cells. Expand the range when the source grows, or format the source as an Excel table and revise the formula range as needed. A fixed helper-column sort also needs the new rows included in the next sort.
Tips
- Use a table for related data. If your records are already an Excel table, include the helper field in the table and sort using that field. Excel tables are designed to keep row information together. Remove the helper field afterward if it is no longer useful.
- Keep the original order when it may matter later. Before sorting, make a copy of the sequence in a spare column or duplicate the worksheet. The helper method can restore the original order if you keep the original sequence numbers, but deleting that column removes the easy restoration key.
- Use Paste Special when you need fixed values. A formula result changes if its source changes. To preserve the reversed result as a snapshot, copy the output range, then use Home > Paste > Paste Values. Check that you are pasting into the intended cells.
- Handle blanks deliberately. Decide whether blank cells are genuine entries or just unused space. A trailing blank included in a formula range can produce an empty result at the top after reversal. Tighten the source range to the actual entries when that is not what you want.
- Make the direction easy to verify. Note the original first and last items before sorting. Afterward, the original last item should be first and the original first item should be last. This simple check catches a sort that ran in ascending order by mistake.
Troubleshooting
The rows are in the wrong direction. In the helper method, reopen Data > Sort and make sure the helper field is sorted Largest to Smallest, not Smallest to Largest. If the output formula starts with the original first item, check the formula’s ranges and the -1 argument in SORTBY. Undo the sort immediately with Ctrl+Z on Windows or Command+Z on Mac if the source rows were changed incorrectly; undo may not be available after later workbook changes or after closing the file.
Excel says the formula has a problem. Confirm that you copied the formula exactly, including its equals sign, parentheses, commas, and cell references. Excel installations configured for a different regional format may use semicolons instead of commas as argument separators; use the separator expected by your installation. Also confirm that your Excel version supports SORTBY and SEQUENCE: they are available in Microsoft 365 and Excel 2021 or later. For an older version, use the INDEX method.
The dynamic formula shows #SPILL!. Excel cannot place the results because one or more cells in the expected output area are occupied or merged. Clear the cells below the formula and to its right for a multi-column result, or move the formula to a larger empty area. A spill area must be clear, even if the cells contain spaces or formulas returning an empty string.
The sort separates details from their records. Undo right away, then select the full data block and repeat the sort. If Excel displays an Expand the selection prompt, choose that option. Avoid merged cells within the range because they can prevent sorting or cause a warning; unmerge them before sorting if appropriate, and check the layout afterward.
Blank rows or filters make the result confusing. Remove accidental empty rows within the data block before sorting, or include them intentionally and account for them in the helper sequence. If a filter is active, clear or review it so you know which records you are seeing. Sorting changes the row order; it does not delete filtered-out records.
Frequently Asked Questions
Can I flip a list upside down without changing the original?
Yes. Put an INDEX or SORTBY formula in a separate empty area. The source stays as it is, while the formula displays the entries in reverse order. Use Paste Values if you later need the displayed result to stop updating.
How do I reverse a list and keep each row’s information together?
Use a helper column and sort the full data range, or use SORTBY on the full rectangular range, such as A2:C6. Do not reverse just the names or just one field if other columns belong to the same records.
Can I flip a list with blank cells?
Yes, but decide whether those blanks belong to the list. A blank included in the source range is treated as one of its positions and may appear at the opposite end. For a cleaner result, use a range that includes only actual entries.
Does this work in Excel for Mac?
Yes. The Data > Sort dialog and the formulas described here are available in current Excel for Mac. The keyboard shortcut for undo is Command+Z on Mac; Ctrl+Z is the Windows shortcut.
Will the reversed list update when I change the source?
The INDEX and SORTBY formula results recalculate when referenced source cells change. A sorted list created with a helper column is a one-time rearrangement; editing source values later does not automatically reverse or resort the rows.
Which method should I choose?
Use the helper column to rearrange existing records in place and keep whole rows together. Choose INDEX for a separate reversed result that also works in older Excel releases. Choose SORTBY for a compact, automatically spilling result in Microsoft 365 or Excel 2021 and later.
Related
- Export Tab Names in Excel: Two Practical Ways to Save Them
- How to Export an Excel Table to Word Without Losing Format
- MT5 History to Excel: Export Trades and Account Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete Duplicate Names in Excel Without Losing DataTo delete duplicate names in Excel, select the entire list or table, choose Data > Remove Duplicates, select…
- Hide Excel Columns Based on Cell Values AutomaticallyTo hide Excel columns when a cell contains a chosen value, use a short VBA macro that checks…
- Excel Import Multiple CSV Files: Methods and TipsTo import multiple CSV files into Excel for Microsoft 365 on Windows, put the files in one folder,…
- How to Import a CSV File From Linux to ExcelTo import a CSV file created on Linux, first copy it to a folder Windows can access, then…
- Excel ODS vs CSV: Which Format Should You Use?ODS and CSV are different spreadsheet file formats: use ODS when you need to keep a workbook’s sheets,…
- How to Insert a Continuous Horizontal Line in WordTo insert a continuous horizontal line in Word, place the cursor in a blank paragraph and type three…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.