Excel: Reversing a List
Learn how to reverse a list in Excel without sorting. Use the OFFSET function and named ranges to flip your data order quickly and easily.
If it’s a list that can’t be sorted, like an itinerary, the usual trick is to add an adjacent column of sequential numbers and sort on that. But that’s work, isn’t it?
No problem. It’s easy enough using OFFSET:
The formula is:
=OFFSET($A$1,COUNTA(CTWynberg)-ROW(),0)
How it works
Here “CTWynberg” is a named range for the stations in Column A. OFFSET’s first parameter after the reference is a row. COUNTA returns 12, ROW() returns 1. Hang on … 12 less 1 is 11! Why doesn’t it return Kenilworth from row 11? The reason is that OFFSET is zero based, so what is being returned is the 11th row indeed, but of the list named CTWynberg, not the worksheet, and that list is numbered 0 to 11.
“And”, Telly Savalas would say, “like that”.
Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS can assist you with your Excel: enquiries@krs.co.za
Looking for a software partner?
Tell us about your project and we’ll show you how our team can help.
Found this useful? Share it.
