Contents
A banal, at first glance, task that occurs periodically in the work of almost any Microsoft Excel user is to arrange the elements of a list in reverse order. For all its seeming simplicity, there are some “chips” here – let’s look at several options for solving it.
Method 1. Manual sorting by additional column
It’s usually the first thing that comes to mind. We add another column with serial numbers next to our list and sort by this column in descending order:
 |  |
The obvious advantage of this approach is its simplicity. The obvious disadvantage is that you need to do a certain number of operations with your hands. If this is a one-time task – OK, but if the data changes every day, then manually sorting the list all the time is already annoying. The solution is to use formulas.
Method 2. Reverse order by formula
Since formulas in Excel are recalculated automatically (if the manual recalculation mode is enabled), then the sorting implemented by the formulas will take place on the fly, without any user intervention.
The formula we need, placing the elements of the list in reverse order, might look like this:
The disadvantage of this formula is that the beginning and end of the list must be hard-coded in it (cells A2 and A9 in our case). If it is not known in advance exactly how many elements will be in the list, then it is better to use a different approach:
In this formula, the number of the last occupied cell is calculated using the function COUNT (COUNTA), i.e. the number of elements in the original list may subsequently change.
The disadvantage of this option is that there should not be empty cells in the original list, because function COUNT then it will incorrectly calculate the row number of the last element. The solution can be to use a dynamic named range with auto-sizing or a tricky array formula:
As you can easily see, this is a variation of the first method, where the range is taken “with a margin” right up to the hundredth line and the line number of the last filled cell is not set rigidly, but is calculated using the MAX(($A$2:$A$100<>“”) fragment *ROW($A$2:$A$100))
Each cell in the range A2:A100 is checked for fullness using the expression ($A$2:$A$100<>“”), which will return an array of TRUE and FALSE values. This array is then multiplied element by element by the array of line numbers obtained using the ROW($A$2:$A$100) function. Since Excel interprets the logical TRUE as 1, and FALSE as 0, after multiplication we will get an array of numbers of filled cells. And from it the function MAX (MAX) selects the largest number, i.e. number of the last filled line.
And, of course, do not forget after entering this formula to press not the usual Enterand the combination Ctrl + Shift + Enterto enter it as an array formula.
Method 3. Macro
If you want to implement the rearrangement of cell values in reverse order without an additional column with formulas, i.e. right in the source cells, you can not do without a simple macro.
Press combination Alt + F11 or the Visual Basic button on the tab developer (Developer). Insert new empty module via menu Insert – Module and copy the text of the macro there:
Sub Reverse() Dim arrData(), n As Long arrData = Selection For Each cell In Selection cell.Value = arrData(UBound(arrData) - n, 1) n = n + 1 Next cell End Sub
Now, if we select a list column with data and run our macro using the combination Alt + F8 or commands Developer – Macros (Developer — Macros), then the list will expand in reverse order right in the same cells, i.e. in place.
- How to Create a Dynamic Named Range with AutoSize in Excel
- What are array formulas and how to use them
- Sorting tricks in MS Excel