Updated August 22, 2023
Excel Reverse Order (Table of Contents)
Introduction to Excel Reverse Order
Reversing the order is nothing but flipping the column values; this means the last value in your column should be the first value in reversed order, and the second last should be the second value and going on, the first value should be the one that gets entered at last in a reversed column. Well, it seems to be a pretty simple one-click task when you hear it out at first. However, it is not. Because there is no one-click button in Excel that does this task for you, it is very hard to use the traditional Sort function as it can only sort the values based on the sorting order alphabetically or numerically and not with the flipping preferences. However, these are not the only, and you may come up with some creativity, your own ways to get the task done.
Let us see different methods that can use to reverse the column order. Suppose we have data as shown below:
Column A consists of data, as you can see, that is not sorted. Here, we wanted the order to be reversed (column flip) so that Dan should appear first in a Reversed column and then Bob, and likewise, Alvin should come last in a Reversed column.
Methods to Reverse Column Order
Let’s understand the techniques to Reverse Column Order with a few methods.
Methods #1 – Conventional Sort Method
Step 1: Add a column named Order after column A (Data) and try typing the numbers starting from 1,2. These numbers will be the order references for data values under column A.
Step 2: Now, select the first two cells in the Order (column B) column and then drag it down until B11 to get the sequential numbers from 1 to 10 across B1:B11.
Step 3: Select the entire column B Data (B1:B11) and navigate to the Data tab in the Excel Ribbon. Within the Data tab, under the Sort & Filter group, click on the Sort Z to A (Highest to Lowest) button.
Step 4: When you click the Sort Z to A button, you’ll get a warning message from the function. As shown below. Don’t change anything in the default selection in that message box, and click on the Sort button.
As soon as you click on the Sort button, you can see that the data has been sorted based on column B (named Order) values as Largest to Smallest, and accordingly, column A values have also changed their places. Now, you can see Dan as the first entry in column A, Bob as the second and going on, and Alvin as the last entry.
This is one way to reverse the order of a column or flip the column.
Methods #2 – Using Excel Formula
You can see that column A consists of all the names from Alvin to Dan. Now, column B, named Reversed, is something where we will add a generic formula to look up and add the values as flipped from column A. This means the sequence will be Dan as first, Bob as second, and going so on, Alvin will be the last entry under column B.
Step 1: In cell B2, start typing the INDEX formula and use $A2:$A$11 as an absolute reference to it (hit the keyboard F4 button once to convert the reference into an absolute one).
Step 2: Add the relative row references using the ROWS function. Use the same data reference inside the ROWS function (A2:A$11); however, use F4 to make A11 absolute this time.
Step 3: Add two closing parentheses for ROWS and the INDEX function; this formula is complete. Press the Enter key and drag this formula across B2:B11 for the desired result.
Things to Remember About Excel Reverse Order
- Excel has no built-in function that reverses/flips the column order. It can be achieved with different formulae and their combinations.
- Excel SORT seems to be the best yet fastest way to achieve a column’s order reversal or order flipping.
- However, at the same time, using a combination of INDEX and ROWS functions is more generic since it doesn’t alter the original order preference, and you still can have your values in the original order if you change your mind from flipping the column order.
Recommended Articles
This is a guide to Excel Reverse Order. Here we discuss How to Reverse Column Order in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
