Today we are going to discuss about 3 methods in Excel to reverse the order of data.
Lot to learn, Let's dive in.
Method - 1:
Simple and Elegant method with shrinkable range,
Formula in C4 cell is,
=INDEX($B$4:$B$14,ROWS(B4:$B$14))
=INDEX(array,row_num,[column_num])
Array is simply the range of names from $B$4:$B$14, both row and column reference are locked.
In Row number argument,
ROWS function helps us to get the number of rows in given range or reference.
11 rows are there in B4:$B$14 range.
INDEX function extracts eleventh name in given range which is "Matt Allington".
But in the reference argument of ROWS function,
B4 cell reference is not locked, this is relative reference.
$B$14 cell reference is locked in both row and column. So, this is absolute reference.
When we drag the formula down to C5 cell,
=INDEX($B$4:$B$14,ROWS(B5:$B$14))
Array argument remains same as $B$4:$B$14, since it is an absolute reference.
But in row number argument, B4 becomes B5. This is because, it is a relative reference.
Number of rows in the range B5:$B$14 is 10 and so INDEX extracts 10th name from given range, which is "Ken Puls".
Now, Drag the formula all the way down to C14 cell.
Method - 2:
Simple but powerful with Expandable range.
Formula in D4 cell is,
=INDEX($B$4:$B$14,COUNTA($B$4:$B$14)-ROWS($D$4:D4)+1)
In row number argument,
COUNTA function counts non-blank cells in given range $B$4:$B$14.
In our case, this will deliver 11.
ROWS($D$4:D4) will throw 1 as output.
So, 11 - 1 is 10, if we add 1 then it becomes 11.
INDEX function extracts eleventh element in given range.
Drag this formula down to D5 cell,
COUNTA delivers same result 11,
but the reference in ROWS function will become $D$4:D5 and will deliver 2.
So, 11 - 2 + 1 is 10.
Now, INDEX function extracts tenth element in given range.
Method - 3:
Powerful Dynamic Array solution.
=LET(R,$B$4:$B$14,SORTBY(R,SEQUENCE(ROWS(R),,ROWS(R),-1)))
Dynamic array function SORTBY helps us to accomplish the flip.
Arguments of this function,
=SORTBY(array,by_array,[sort_order],...)
In 2nd argument, feed the sequence from 11 to 1 with decrement of 1.
Another dynamic array function can do this with ease, that is
=SEQUENCE(rows,[columns],[start],[step])
How many rows of sequence we need 11,
Since our sequence is one dimension, we can ignore columns argument.
Our starting position is 11, Step is (-1). Thats it, sequence of numbers from 11 to 1 is created.
Feed this in by_array argument and see the magic.
LET function helps us to reduce the length and to improve the speed and readability
Comments