How do you rank in Excel?

Answer 1

There are several options for ranking in Excel. I suspect the RANK function is most relevant for this question, so let's start and focus there:

----- RANK Function -----

There are three inputs to the RANK function: the cell you wish to rank, the range of cells you to which you are comparing it, and an optional specification for ascending or descending order.

EXAMPLE: Say you have ten test scores in column A, rows 1-10. You want to show the the rank for each score in column B in descending order (i.e., highest score = 1.) The formula for B1 would be =RANK(A1,A$1:A$10,0). Copy the formula to B2 through B10.

A few tips for entering the formula:

- Be sure to add dollar signs before the row references so that the formula always references A1:A10 even as you copy it down. (To add dollar signs quickly, press F4 twice after you select the range A1:A10. F4 toggles through various combinations of dollar signs.)

- The third argument (ascending or descending) is optional. If omitted, the function defaults to descending. Typing 0 also causes descending; 1 causes ascending.

- If you discover that you need guidance while writing any function, press and hold SHIFT and tap F3. That will bring up the guided entry dialog box, which includes definitions and references, like 0 = descending. It's very handy when you know the formula you want but have forgotten a detail of the syntax.

- To copy the formula to B2:B10 quickly, click in cell B1, then double click on the little box in the bottom right of the green cell border. The formula will automatically populate through B10.

----- OTHER FUNCTIONS ----

There are several other rank functions, including options for controlling how ties are handled, or displaying percent rank. To discover these or any other formula, click on the fx button to the left of the formula bar, or press and hold SHIFT and tap F3, and then type a description of what you want to do.

----- PIVOT TABLE -----

To rank a column or row in a pivot table, right click on a value of interest and select "Show Values As > Rank..."

----- QUICK & DIRTY------

If you don't need to record a numeric rank, but instead just want a better feel for the data, try these options:

- For a quick, visual representation of rank, try conditional formatting via Home > Styles > Conditional Formatting. Pick from color scales, bars, icons, etc.

- To rank by sorting, use the Sort & Filter function via Home > Editting.

- For a quick overview of a set of data, try the descriptive statistics function via Data > Data Analysis > Descriptive Statistics. (Analysis Toolpak installation required). In one step, it will return the min and max values, average, standard deviation, range, sum, count, and more. While it doesn't return a rank for each value (other than the min and max), it does provide a rich assessment of the data quickly.

I hope that helps with this task and in building Excel skills in general. Wishing you all the best in your Excel journey!