Sort a list of values without macro or "Sort" button: the table-style calculation

When working with tables in Excel, we often think of cell by cell calculation.
Sometimes, we use several cells to perform a calculation and get one result, like range sum, average, match ...

What if your need is to use 1 cell or a range of cells to fill several cells with results?
In case your range contains only numbers or dates and if there are no repeated values, then it is possible.

Example:
You need to sort a range of 25 different values from column A, range A1:A25 in ascending order, and store it in B1:B25.
What comes to mind first is the sort function called from a macro.
But now, you think that it would be better if there was no macro to run, and the dream come true would be to do it in one single step ...
So, you want:

Let's try:
In B1 we type: MIN(A1:A25)
In B2 we type: MIN(IF(A$1:A$25<=B1;MAX(A$1:A$25);A$1:A$25))
In theory, in B2 we are asking to change values below B1 to the max value of the column so we are sure it will not interfere with the minimum we want to retrieve. And we take the min of it.
But it won't work, it will perform the test on A1 only (test result is FALSE) and not on the whole range ... it won't work, unless you tell Excel that you're working on the whole table, by validating the formula of your cell B2 with the combination of keys Shift + Enter.
You'll notice it puts your formula in brackets "{ = MIN ... }"
Now, you can extend the formula down to cell B25, it works !

There are many applications of this range-style calculation, up to you to find how it can apply in your case !
Capture.PNG