Excel Reverse Order (Table of Contents)
Introduction to Excel Reverse Order
Reversing 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 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. Besides, it is really very hard to use the traditional Sort function as it only can 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 be used to reverse the column order. Suppose we have data as shown below:
Column A consists of data as you can see is not sorted. Here, we wanted the order to be reversed (column flip) in a way that, Dan should appear first in a Reversed column and then Bob and likewise Alvin should come as 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 column named Order (column B) and then drag it down until B11 to get the sequential numbers from 1 to 10 across B1:B11.
4.5 (303 ratings)
View Course
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 Sort Z to A (Highest to Lowest) button.
Step 4: As soon as you click on 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 could 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 second and going on, 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 starting from Alvin up-to Dan. Now, the column B named Reversed is something where we will be adding a generic formula that can look up and add the values as flipped from column A. Which 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 keyboard F4 button once to convert the reference into an absolute one).
Step 2: Now use the ROWS function to add the relative row references. Use the same data reference inside ROWS function (A2:A$11) however, this time use F4 to make A11 as absolute.
Step 3: Add two closing parentheses for ROWS as well as INDEX function and this formula is complete. You can press the Enter key as well as drag this formula across B2:B11 to get the desired result.
Things to Remember About Excel Reverse Order
- Excel doesn’t have any 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, simple yet faster way to achieve the order reversal or order flipping of a column.
- However, at the same time using a combination of INDEX and ROWS function is more generic since it doesn’t alter the original order preference and you still can have your values in original order if you changed 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 along with practical examples and downloadable excel template. You can also go through our other suggested articles –
