In Excel for Microsoft 365, the quickest way to reverse a column without changing the source is to enter =SORTBY(A2:A10,SEQUENCE(ROWS(A2:A10)),-1) in an empty cell, replacing A2:A10 with your data range. To change the original data in place, add a helper index and sort the entire relevant range from largest to smallest.
What reversing a column means
Reversing a column means turning its existing sequence upside down: the last item becomes first, the item before it becomes second, and so on. For example, if A2:A6 contains 12, 19, 24, 31, and 42 from top to bottom, the reversed sequence is 42, 31, 24, 19, and 12.
This is different from sorting values in descending order. A descending sort rearranges items according to their values, such as putting the largest number first or alphabetically sorting names from Z to A. Reversing preserves the original sequence and simply reads it from bottom to top. The distinction matters when the order itself carries meaning, such as a timeline, a manually arranged priority list, or records entered in a particular sequence.
Reverse a column with a dynamic formula
For a separate reversed copy, a dynamic-array formula is usually the simplest option. With the source values in A2:A6 and an empty output area starting at C2, enter this formula in C2:
=SORTBY(A2:A6,SEQUENCE(ROWS(A2:A6)),-1)
The result spills down automatically as 42, 31, 24, 19, and 12. SORTBY arranges the source range using a sort key, while SEQUENCE creates the position numbers 1 through 5. The final argument, -1, sorts those positions in descending order, so the item in position 5 appears first. Because the values are sorted by their positions rather than by their contents, the original sequence is reversed without alphabetizing or numerically sorting it.
- Choose an output cell with enough blank space below it. Do not place the formula inside the source range.
- Enter the formula, changing both references to match the same source range.
- Press Enter. Excel fills the cells below with the reversed result.
- If the source changes, the formula result updates automatically, provided the spill area remains clear.
SORTBY and SEQUENCE are available in Microsoft 365 and Excel 2021 or later. The formula works in current Excel for Windows and Mac versions that include those functions. Excel for the web also supports dynamic arrays, though its ribbon and interface may differ slightly. If Excel displays a #SPILL! error, check that the output cells are empty and are not merged with other cells.
Reverse complete rows while keeping records together
If each item in the column belongs to a larger record, reverse the full table rows rather than just the one column. For example, names in column A may have associated dates in B and order numbers in C. Sorting or rearranging A alone would attach names to the wrong dates and numbers.
With records in A2:C10, enter this formula in an empty cell such as E2:
=SORTBY(A2:C10,SEQUENCE(ROWS(A2:A10)),-1)
The formula returns all three columns, with the last record first and the first record last. The output range needs enough room for all nine rows and three columns. If the records have headers in row 1, keep the header outside the selected data range; the formula reverses the records, not the header. This approach creates a separate result, so it does not overwrite the source table.
Reverse the original data with a helper column
To rearrange the original cells, give each row a temporary position number and sort by that number in descending order. This method works in versions without dynamic-array functions and gives you a sortable result in the existing data area.
- Insert a blank helper column beside the data. Enter 1 beside the first data row, 2 beside the next, and continue until every row has a unique number.
- Select the helper numbers and the data they belong to. Include every related column so each full record moves as a unit.
- On Windows, choose Data > Sort. In the Sort dialog, choose the helper column under Sort by and select Largest to Smallest under Order. Confirm that the data has a header if you included its heading in the selection.
- Check that the last original row is now first. Delete the helper column when you are satisfied with the result.
On Excel for Mac, use the Data tab and its Sort command; the dialog provides the same basic choice of a sort column and descending order. If Excel presents a prompt asking whether to expand the selection, choose the option that expands it when the related columns must stay together. Sorting only the helper numbers would separate them from the values they are supposed to order.
Use an INDEX formula for older Excel versions
Older Excel versions may not include SORTBY or SEQUENCE. An INDEX formula can build a reversed copy one cell at a time. With source data in A2:A6, enter this in B2 and fill it down through B6:
=INDEX($A$2:$A$6,ROWS($A$2:$A$6)-ROWS($B$2:B2)+1)
In B2, the first ROWS calculation is 5 and the second is 1, so INDEX returns the fifth source value, 42. As you fill down, the second calculation increases to 2, 3, 4, and 5, producing the remaining values in reverse order. The dollar signs keep the source range fixed while the formula is copied. Change the source and output references to fit your sheet, and fill the formula for exactly as many rows as there are source values.
This formula returns a copy; it does not rearrange the source cells. Once you have checked the result, you can copy the output and use Paste Special > Values if you want fixed values rather than formulas. The exact Paste Special options can vary by Excel version, but the Home tab includes the Paste menu. Keeping the formula output is useful if you expect the source to change.
Choose the method that fits the task
- Use SORTBY and SEQUENCE when you want a live, separate reversed result and have Microsoft 365 or Excel 2021 or later.
- Use a helper column and Sort when you want to reverse the original rows, need a method that works in older versions, or want to control the sort explicitly.
- Use INDEX when you need a formula-based copy but your Excel version lacks the newer dynamic-array functions.
- Reverse the full data range whenever values in adjacent columns belong to the same record.
For a one-time cleanup, sorting with a helper column is often practical. For a report that should update when the source changes, a formula is generally more convenient. A formula result also leaves the original untouched, which is helpful if you need to compare the two orders or undo a decision later.
Common problems and how to avoid them
The values are sorted by size or alphabetically. A normal descending sort uses the values themselves as its sort key. To reverse the existing sequence, sort by a helper index that records row order, or use the positional key in the SORTBY formula.
Related information no longer matches. The sort selection may have included only the target column. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then sort the full range of related records. If other people are editing the workbook, make a copy before a substantial rearrangement.
The formula shows #SPILL!. Dynamic-array formulas need a clear output area. Remove content blocking the cells, unmerge cells in the intended spill area, or choose another starting cell. A formula cannot spill into occupied cells.
The formula returns the wrong number of items. Check that the source range starts and ends on the intended rows and that the row count used for the output matches it. Avoid including a header in the source range unless you deliberately want the header treated as a value and reversed too.
There are blank cells or duplicate values. Reversing by position preserves the rows’ positions even when values repeat; it does not need every value to be unique. Blanks within the source range are part of that range, so their positions reverse along with the other cells. If blanks should be excluded rather than moved, first define the desired set of rows; that is a different task from simply reversing an existing range.
The source is an Excel Table. A spilled formula needs a clear output area outside the table, because dynamic-array spill ranges do not expand inside table cells in the same way as ordinary formula columns. For an in-place rearrangement, use a helper column and sort the table so its records remain intact.
Frequently Asked Questions
Can I reverse a column without sorting it alphabetically?
Yes. Use the SORTBY formula with a sequence of row positions, or sort by a helper index. These methods reverse the current order rather than comparing the text or numbers in the cells.
How do I reverse a column in place?
Add a helper column containing consecutive numbers, select the helper and the full data range, and sort the helper from Largest to Smallest. The formula methods create a separate output and do not change the source cells.
Can I reverse rows in a table without separating the columns?
Yes. Select or return all columns in each record. With SORTBY, use the complete rectangular data range as the first argument; with a manual sort, include the entire table rather than one column.
Will the reversed formula update when I edit the source?
Yes. SORTBY recalculates when values in its source range change. Its result remains a separate spill range, so keep that output area clear and do not type over the spilled cells.
Why does sorting Largest to Smallest not reverse my list?
That command sorts by values, not by their original positions. A list that is already in an arbitrary order will be rearranged numerically or alphabetically. Sort a helper index in descending order to reverse the original sequence.
Before you finish
Check the first and last entries against the original: the original bottom item should now be at the top, and the original top item should be at the bottom. If you rearranged records, verify that the related columns still match. For a reusable result, keep the dynamic formula; for a one-time change, remove the helper column only after confirming that the sorted data is correct.
Related
- Excel EIN Number Format: Keep All 9 Digits Visible
- Excel Dependent Drop-Down Lists: Setup and Fixes
- Excel DOS Keyboard Shortcuts: What Actually Works
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 Add a Header in Word on Certain Pages: GuideTo add a header to only certain pages in Word, separate those pages into sections, then turn off…
- How to Make a Box and Whisker Plot in ExcelTo put a box and whisker plot in Excel for Microsoft 365, arrange each group of numeric observations…
- TEXT Function in Google Sheets: Format Numbers and DatesThe Google Sheets TEXT function converts a number, date, or time into text displayed in a format you…
- Find Excel Workbook Links in Microsoft ExcelTo find links to other Excel workbooks in Microsoft 365 for Windows, open Data > Workbook Links if…
- Excel Data Validation List With a Blank OptionTo add a blank choice to an Excel drop-down, include a genuinely empty cell in the list’s source…
- Excel Cell Phone Accessories in Jurong East: What to KnowIf you mean a shop specifically named Excel, I can’t reliably confirm that an Excel-branded phone-accessory store currently…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.