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

Watch Video â€“ Create a Ranking Graph in Excel

### Method 1 – Create a Ranking Graph with Sort Command in Excel

The sample dataset below contains a list of the wealthiest persons in the USA.

• Select the entire dataset (B4:C14). SelectÂ Insert >> 2-D Column as shown in the image below.

• The graph does not show the data based on the highest to lowest ranking or vice versa.

• To solve the problem, select the Net Worth column.
• SelectÂ Sort & Filter >> Sort Largest to Smallest from theÂ Home tab as shown below. A warning window will pop up.
• ChooseÂ Expand the SelectionÂ in theÂ Sort WarningÂ window and hit theÂ Sort button.

• The graph will look as shown.

• The data can be sorted fromÂ Smallest to Largest.

Read More: How to Stack Rank Employees in Excel

### Method 2 – Construct a Ranking Graph with Excel LARGE Function

Steps

• Enter the numbers 1 to 5 in cellsÂ E5toÂ E9. Enter the formula below in cellÂ G5. Use theÂ Fill HandleÂ icon to apply the formula to the cells below.
`=LARGE(\$C\$5:\$C\$14,E5)`

• Input the INDEX-MATCHÂ formula with the functions in cellÂ F5. Drag theÂ Fill HandleÂ icon to the cells below.
`=INDEX(\$B\$5:\$B\$14,MATCH(G5,\$C\$5:\$C\$14,0))`

• Select the new dataset (E4:G9) containing the top 5 wealthiest persons. SelectÂ Insert >> 2-D Column.

The graph result shows the ranking of the top 5 wealthiest persons as shown in the image below.

Read More:Â How to Rank Average in Excel

### Method 3 – Build a Ranking Graph with Excel SMALL Function

Input the formula below in cellÂ G5.

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

• Insert the chart with the new dataset.

• The ranking graph will look as shown in the image below.

Read More:Â How to Create an Auto Ranking Table in Excel

### Method 4 – Plot a Ranking Graph with Excel PivotChart

Steps

• Select the entire dataset. SelectÂ Insert >> PivotChart >> PivotChart as shown below.

• Check the radio button for Existing Worksheet in theÂ Create PivotChart.
• Use the upward arrow in theÂ LocationÂ field to select the cell (E4) where you want the PivotChart.
• Click OK.

• Drag theÂ Name table in theÂ AxisÂ area and theÂ Net WorthÂ table in theÂ Values area as shown in the image.

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

• Sort the data in the PivotTable to show the data rank-wise in the graph.

### Method 5 – Make a Dynamic Ranking Graph in Excel

Steps:

• 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.

• Enter the following formula in cellÂ I6. 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)`

• Apply the following formula in cellÂ J6and 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.

• 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`

• Apply the following formula in cell L6 to get a unique rank for each product.
`=J6+K6`

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

• Enter the following formula in cellÂ P6. Drag theÂ Fill Handle icon to the cells below.
`=INDEX(\$I\$6:\$I\$15,MATCH(O6,\$B\$6:\$B\$15,0))`

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

The dynamic ranking graph will appear as shown below.

You can insert new rows between rows 11 and 15 to add more products. 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. The ranking graph will update automatically.

## Related Articles

<< Go Back toÂ Excel RANK Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF