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

descending sequence.

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.