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

Published on
9,819 Points
2 Endorsements
Last Modified:
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.

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:
in B1 the min value of A1:A25
in B2 the min value of A1:A25 but without taking into account values <= B1
in B3 the min value of A1:A25 but without taking into account values <= B2 ... and so on

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 !

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Join & Write a Comment

This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month