Reverse a List using Formulas [Using Excel INDEX() Formula]
Here is a simple yet novel use of formulas. Let us say you have a list of values in range A1:A5 and you want to reverse the list.
In an empty cell write
=INDEX($A$1:$A$5,6-ROWS($A$1:A1)) and copy down.
Boom, you get the reversed list.
Here is how the formula works:
- In the reversed list, first item is last item in the original list (ie 5th item in our case).
- INDEX() formula takes a list, a row number (and optional column number) and returns the value at intersection.
- In this case, $A$1:$A$5 is the list.
- 5 is the size of list.
- ROWS($A$1:A1) gives running numbers from 1 thru 5 when copied in any range of 5 cells. Read more on using ROWS() formula.
Do you want to be awesome in Excel?
Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:
Your email address is safe with us. Our policies
More awesome tips for you:
Leave a Reply
|Download Excel 2010 Beta today [Office 2010 Updates]||One Hundred Project Managers have chosen to be better, what about you?|