How to Rank in Excel: A Step-by-Step Guide

Are you tired of staring at your Excel spreadsheet and wondering how to make sense of it all? Do you want to impress your boss or colleagues with your data analysis skills? Then buckle up, because I'm about to take you on a journey of Excel ranking.

What is Excel Ranking?

Excel ranking is a method of ordering data from highest to lowest or vice-versa. It is a useful tool for finding the top performers in a group, or for identifying the biggest problem areas. By ranking data, we can easily visualize patterns, trends, and outliers.

Step 1: Sort Your Data

The first thing you need to do is sort your data. This means putting it in order based on the value you want to rank. To sort data, select the entire column or row you want to sort by clicking any cell in that column/row. Then, click on “Home” tab and select “Sort & Filter” and then “Sort A to Z” or “Sort Z to A” based on whether you want to rank from highest to lowest or vice versa.

Step 2: Add a Rank Column

Next, you need to add a new column that will contain the rank values. You can do this by clicking on the column to the right of your data, then right-clicking and selecting “Insert.” Name this column “Rank” or any name you prefer.

Step 3: Calculate the Rank

Now comes the fun part. In the first cell of your “Rank” column, type the following formula:


Replace "cell" with the specific cell you want to rank, and "reference" with the range of cells you want to rank. For example, if you want to rank cell A2 against a range of data in cells A3 through A10, your formula might look like this:


The “0” at the end of the formula indicates that you want to rank from highest to lowest. If you want to rank from lowest to highest, change the “0” to a “1”.

Step 4: Copy the Formula

Copy the formula from the first cell to the rest of the cells in your “Rank” column. To do this, click on the cell with the formula, then hover your cursor over the bottom-right corner until it turns into a plus sign. Drag the plus sign down to copy the formula to the rest of the cells.

Step 5: Format Your Data

Congratulations! You've ranked your data. But before you celebrate, let’s format the rank column to make it more visually appealing. You can do this by selecting the entire "Rank" column, right-clicking, and selecting “Format Cells.” In the “Number” tab, select “Number” and choose how many decimal places you want. Then, click “OK.”

Step 6: Analyze Your Data

Now that you've ranked your data, it's time to analyze it. Use conditional formatting to highlight the top performers or problem areas in your data. You can also create charts to visualize the patterns and trends in your ranked data.


In conclusion, Excel ranking is a simple but powerful tool for data analysis. By following these steps, you can easily rank your data and gain insights that weren't visible before. So go ahead, give it a try, and impress your boss and colleagues with your new Excel skills!

By clicking “Accept”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.