Hi - Dave here.
Happy Friday!
An interesting problem in Excel is how to "reverse" the values in a range or list.
Your first instinct might be to sort the values in
descending order. However, this won't work if the values aren't sorted to begin with.
What we need is a way to sort the values by their
position, even when position is
not part of the data itself.
A nice way to do this is to use the SORTBY function with the SEQUENCE function, as seen below, where the formula in D5 is:
=SORTBY(B5:B14,SEQUENCE(ROWS(B5:B14)),-1)
[
Download the workbook and read the full explanation]
In brief, the SEQUENCE + ROWS combo generates an array of row numbers, which the SORTBY function uses to sort the range B5:B14 in
descending order. See the article above for details, plus more advanced examples that show how to reverse a comma-separated list of values. Then download the workbook and try it out yourself.
Note: SEQUENCE and SORTBY require Excel 2021 or later. For older versions of Excel, I've included a formula that will reverse a range with the INDEX function.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at a specific Excel formula or function. Sign up on our home page.