Microsoft Office Tutorials and References
In Depth Information
SORT WITH A FORMULA
In Figure 54, the SMALL formulas in column F sort the data in ascending
sequence. The formula =LARGE($D$2:$D$11,ROW(A1)) in H3 and copied
down sorts the numbers in descending sequence.
Figure 54. Formulas in F and H sort the data from D in ascending or
Note: Excel properly handles the duplicate values. Cells F4 and F5 show
both of the 10s from the original data set. Excel returns 10 as the answer for
the second- and third-smallest items in the range.
In Figure 55, notice how the ROW(A1) argument changes as you copy it
down the range.
Figure 55. Using ROW(A1) is an easy way to make the second
argument change from 1 to 2, 3, 4, etc. as it copied down.
Summary: You can use the SMALL or LARGE function to sort with a formula.