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.
Watch Video – Create a Ranking Graph in Excel
How to Create a Ranking Graph in Excel: 5 Efficient Ways
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. However, the graph does not show the data based on the 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.
- 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: How to Stack Rank Employees in Excel
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)
- 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 Rank Average in Excel
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)
- Now, insert a chart with the new dataset.
- Then, the ranking graph will look like the following one.
Read More:Â How to Create an Auto Ranking Table in Excel
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.
- 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.
- 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)- The RANK.EQ function returns the ranks of the products based on their total sales amount.
- 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- The COUNTIF function in the formula checks for repeating values.
- 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.
Finally, the dynamic ranking graph will appear like the one below.
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.
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.
You can download the practice workbook from the download button below.
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. Stay with us and keep learning.
