Reverse / Flip Data in Excel Columns or Rows | 3 Solutions - Formula and Non-Formula Methods.

👍👍If you have found this content useful and want to show your appreciation, please use this link to buy me a beer 🍺.
Thank you! 👍👍

In this video I demonstrate how to flip or reverse the order of data in a column or row. I demonstrate the following three methods:

1) Number rows or columns and sort
2) Use the INDEX function in combination with the ROWS and COLUMNS functions
3) Use the SORTBY function in combination with the ROW or COLUMN function. SORTBY is available in Excel 365.
------------------------

[patrickschardt]

Great solutions, Chester.

I’m working on a LAMBDA/ LET called AFLIP. It takes an array and a flip direction. For instance, putting a zero or omitting the direction parameter could be flip horizontally, 1 could be flip the data vertically, and 2 could be flip both directions

With functions like INDEX, SEQUENCE, ROWS, and COLUMNS, this becomes very easy. It’s just a matter of checking the flip direction and changing the start and step arguments in SEQUENCE. You do this for the row index and columns sequence. Then use INDEX to return the data in the correct order

[IvanCortinas_ES]

Excellent! Thank you Chester for this video-tutorial.

[meniporat]

The simplest, easiest, shortest, neatest method to flip the data in a column is with the OFFSET function.
So, in order to flip the data in column A, rows 3:12, the formula would be:
=OFFSET($A$2, COUNTA(A3:A$12), )
and if you want to flip the data in the adjacent column (B):
=OFFSET($A$2, COUNTA(A3:A$12), 1)

[lesleykeddy]

Hi Chester - nice to "see" you again after so long. Remember me from Mouse Training? Anyway this was really useful and answered my questions. Best, Lesley

[davidsyes]

This could work in all versions of Excel (definitely works in my 1999 version of Lotus 1-2-3)

=INDEX(A$3:A$12, ROWS(A3:A$12)-1), 0)


No helper cols needed, but a helper range/ref is needed, as indicated above.

Drag the base/origin col as far as data being reversed is desired.

Alternatively, you can anchor/lock the A of the ROW array. Leave 3 unanchored/not locked. Lock the 12 in both the INDEX and the ROWS function/formula.


@@ and =INDIRECT can be used for more complex range shaping/reshaping. Interesting things might be done with COUNT to deal with blanks.

(More complex ways exist that Excel CAN do, but which GUI-level 1-2-3 simply cannot do.)

[homemademakers]

But honestly, What garbage are excel and Microsoft and Bill Gates when we dont have an Icon specified for this important operation.