This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2007

Functions: Rank

The Rank function returns the rank of a value within a list. This function is useful when you need to determine a specific cell's numerical order out of a series of cells or columns. Rank can be determined in either ascending or descending order. For more information about functions, refer to Calculating with Functions.

This document will use the example of a gradebook, featuring six students' grades from a test of twenty points. In the example, we will find the ranking of Bernhardt, B.'s score.
Gradebook example

  1. Open the desired worksheet

  2. Select the cell where you would like the result of the Rank function to appear

  3. From the Formulas command tab, click FUNCTION WIZARDFunction Wizard button
    The Insert Function dialog box appears.
    Insert Function dialog box

  4. In the Search for a function text box, type Rank
    From the Or select a category pull-down list, select All or Statistical

  5. From the Select a function scroll box, select RANK

  6. Click OK
    The Function Arguments dialog box appears.
    Function Arguments dialog box

  7. In the Number text box, type the value of which you want to know the rank
    EXAMPLE: To find the rank of Bernhardt, B.'s Test 1 score, type 18.

  8. In the Ref text box, type the cell range to be included
    EXAMPLE: To type the cell range of all the classmates, type B2:B6.
    To select the cell range,
    1. Click COLLAPSE DIALOG BOX Collapse Dialog Box button
    2. Select the cells to be included
    3. Click EXPAND DIALOG BOXExpand Dialog Box button
      Cells containing characters other than numbers are not eligible for ranking.
      Numbers with decimal points are eligible for ranking.
  1. To obtain the rank of a value where the list of values is sorted in descending order, in the Order text box, type 0 or leave the text box blank
    To obtain the rank of a value where the list of values is sorted in ascending order, in the Order text box, type any nonzero value
    NOTE: This action does not sort the information in your worksheet; it is used only as a reference from which to perform the ranking.

  2. Click OK
    The rank appears in the selected cell and the formula appears in the Formula Bar.
    Bernhardt, B.'s Test 1 rank in descending order (from 20 to 0) is 2.
    Bernhardt, B.'s Test 1 rank in ascending order (from 0 to 20) is 4.

Excellence. Our Measure. Our Motto. Our Goal.