How to Create a Ranking Graph in Excel (5 Methods)

This article illustrates how to create a ranking graph in excel. A ranking graph can be very helpful to keep track of the performances of your employees, the demand for different products, sales made by different stores that you own, and many other areas like these. The following picture highlights the purpose of this article. Have a quick look to learn how to do that.

How to Create a Ranking Graph in Excel


Download Practice Workbook

You can download the practice workbook from the download button below.


5 Ways to Create a Ranking Graph in Excel

1. Create a Ranking Graph with Sort Command in Excel

Imagine you have the following dataset. It contains a list of the wealthiest persons in the USA.

  • Now, select the entire dataset (B4:C14). Then, select Insert >> 2-D Column as shown in the picture below.

  • After that, you will see the graph below. But, the graph is not showing the data based on highest to lowest ranking or vice versa.

  • Now, to solve this problem, select the Net Worth column. Then select Sort & Filter >> Sort Largest to Smallest from the Home tab as shown below. A warning will appear after that. Choose Expand the Selection in the Sort Warning window. Then hit the Sort button.

Create a Ranking Graph with Sort Command in Excel

  • After that, the graph will look like the one below.

  • You can also sort the data from Smallest to Largest to get the following result instead.

Read More: Ranking Data in Excel with Sorting (3 Quick Methods)


2. Construct a Ranking Graph with Excel LARGE Function

You can use the LARGE function in excel to create a ranking graph with the top-ranked values only. Follow the steps below to be able to do that.

📌 Steps

  • First, enter the numbers 1 to 5 in cells E5 to E9 respectively. Then, enter the following formula in cell G5. After that, use the Fill Handle icon to apply the formula to the cells below.
=LARGE($C$5:$C$14,E5)

Create a Ranking Graph with Excel LARGE Function

  • Next, apply the following INDEX-MATCH formula with the functions in cell F5. Then, drag the Fill Handle icon to the cells below.
=INDEX($B$5:$B$14,MATCH(G5,$C$5:$C$14,0))

  • After that, select the new dataset (E4:G9) containing only the top 5 wealthiest persons. Then, select Insert >> 2-D Column.

Finally, you will see a graph showing the ranking of the top 5 wealthiest persons as shown in the following picture.👇

Read More: How to Calculate the Top 10 Percent in Excel (4 Ways)


Similar Readings


3. Build a Ranking Graph with Excel SMALL Function

You can use the SMALL function instead to create a ranking graph containing the bottom 5 persons in the list. Just replace the formula in cell G5 with the following one.

=SMALL($C$5:$C$14,E5)

Create a Ranking Graph with Excel SMALL Function

  • Now, insert a chart with the new dataset.

  • Then, the ranking graph will look like the following one.


4. Plot a Ranking Graph with Excel PivotChart

You can get the same result as in the earlier methods by quickly creating a PivotChart in excel. Follow the steps below to see how to do that.

📌 Steps

  • Select the entire dataset first. Then, select Insert >> PivotChart >> PivotChart as shown below.

Create a Ranking Graph with Excel PivotChart

  • Next, mark the radio button for Existing Worksheet in the Create PivotChart window. Use the upward arrow in the Location field to select the cell (E4) where you want the PivotChart. Then hit OK.

Create a Ranking Graph with Excel PivotChart

  • Now drag the Name table in the Axis area and the Net Worth table in the Values area as shown in the picture below.

  • This will create the following PivotChart along with a PivotTable.

  • Now, sort the data in the PivotTable to show the data rank-wise in the graph.


5. Make a Dynamic Ranking Graph in Excel

In this section, we will create a dynamic ranking graph. You can add or delete data from your dataset. But, the ranking graph will automatically update based on the changes you make to your source data. Follow the steps below to learn how to do that.

📌 Steps

  • First, assume you have the following dataset. It contains the monthly sales amount of different products. You will need to add more rows and columns to the dataset in the future.

  • Now, enter the following formula in cell I6. Then drag the Fill Handle icon to the cells below. The SUM function in the formula will return the total sales for each product.
=SUM(C6:F6)

  • After that, apply the following formula in cell J6 and, then to the cells below using the Fill Handle icon.
=RANK.EQ(I6,$I$6:$I$15,0)

  • But, the function returns the rank 8 twice as the total sales for Blackberries and Blueberries are the same. Enter the following formula in cell K6 to correct this issue.
=COUNTIF($J$6:J6,J6)-1

  • After that, apply the following formula in cell L6 to get a unique rank for each product.
=J6+K6

  • Now, enter the numbers 1 to 5 in cells N6 to N10 respectively. Then apply the following formula in cell O6 and, then copy it down.
=INDEX($B$6:$B$15,MATCH(N6,$L$6:$L$15,0))

  • After that, enter the following formula in cell P6. Then, drag the Fill Handle icon to the cells below.
=INDEX($I$6:$I$15,MATCH(O6,$B$6:$B$15,0))

  • Now, the dataset for the dynamic ranking graph is ready. Select the dataset (N4:P10). Then, select Insert >> 2-D Column to create the dynamic graph.

Create a Dynamic Ranking Graph with Excel

Finally, the dynamic ranking graph will appear like the one below.

Create a Dynamic Ranking Graph with Excel

You can insert new rows between rows 11 and 15 to add more products. But, you need to use the Fill Handle icon to copy the formulas to the newly added cells. You can also add more columns between columns C and H to add more sales data for the new months in the future. Then, the ranking graph will update automatically.

Read More: How to Stack Rank Employees in Excel (3 Methods)


Things to Remember

  • You should always be careful about using the references correctly in the formulas.
  • Add rows between rows 11 and 15 and columns between C and H. You will also need to copy the formulas down while adding new rows.

Conclusion

Now you know 5 different methods on how to create a ranking graph in excel. Please let us know if this article has helped you with the solution you were looking for. You can also use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to explore more on excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo