To make a ranking chart in Excel, sort values from highest to lowest, select the labels and values, then choose Insert > Insert Column or Bar Chart > 2-D Bar > Clustered Bar. Excel does not have a separate chart type named “ranking chart”; the usual approach is a bar chart whose categories are ordered by rank.
What a ranking chart shows
A ranking chart compares categories—such as products, employees, teams, or regions—by a numerical measure and makes their order easy to see. A horizontal bar chart is often a good choice because category names have room to display, and the longest bar quickly identifies the highest value. A chart may show sales, scores, counts, or another measure; ranking is about how those values compare, not about a special chart feature.
For example, a small sales report might contain Cedar with 42, Maple with 67, Birch with 55, Ash with 67, and Elm with 31. A descending chart would place the two 67 values first, followed by 55, 42, and 31. You can add rank numbers as labels or keep the chart focused on names and values. The latter is usually easier to read when the bars already communicate the order.
One useful distinction: sorting a chart’s source data changes the category order, while calculating a rank adds a rank number to each row. You can do either or both. Sorting is essential if the chart should appear in value order; a rank formula alone will not automatically rearrange the chart.
Prepare and sort the source data
Keep each category and its value together in a simple two-column list. Put descriptive headers in the first row, avoid blank rows inside the list, and use one row per category. A clear layout makes sorting less error-prone and gives Excel sensible chart labels.
For the example, enter Category in A1 and Sales in B1, then place the five names in A2:A6 and their sales in B2:B6. To sort the full list, click a cell in the data and use Data > Sort. In the Sort dialog, choose Sales as the sort column and Largest to Smallest as the order. If Excel asks whether to expand the selection, choose Expand the selection so names stay paired with their values. Sorting just the numbers would scramble the data.
For repeat use, you can format the list as an Excel Table: select a cell in the data and choose Insert > Table, confirm that the table has headers, and select OK. Table filters make it convenient to sort by the Sales header. Tables also make it easier to extend a report as new categories are added, although you still need to confirm that the sort order is appropriate after the source values change.
Calculate rank with a formula
A rank column is helpful when a report needs explicit position numbers, or when a reader wants to see the ranking even if the chart is printed in grayscale. Put Rank in C1. With names in A2:A6 and sales in B2:B6, enter this formula in C2 and fill it down through C6:
=RANK.EQ(B2,$B$2:$B$6,0)
The final argument 0 ranks from largest to smallest. For the example, Maple and Ash each receive rank 1, Birch receives 3, Cedar receives 4, and Elm receives 5. Excel assigns equal values the same rank and leaves a gap after a tie; there is no rank 2 here. If you want the smallest value to be rank 1 instead, use 1 as the final argument. Be aware that equal values then still share a rank.
RANK.EQ does not sort the rows; it only returns a number beside each row. If you add a rank column to a chart, check whether it improves understanding. Often it is clearer to sort the rows and label the bars with their sales values, rather than plot rank numbers as bar lengths. Rank is ordinal—first, second, third—not a measure of the distance between categories.
Create and format the bar chart
After sorting the source data in descending order, select the Category and Sales columns, including their headers, and choose Insert > Insert Column or Bar Chart > 2-D Bar > Clustered Bar. Use only the category and value columns for a basic ranking chart; do not include the rank column unless you have a specific reason to chart it. In Excel for Mac, use Insert > Chart and select a bar chart, then choose the clustered bar option. The exact labels can vary slightly by Excel release.
In a horizontal bar chart, Excel may display the first source category at the bottom of the vertical category axis. If your highest value appears at the bottom even though the data is sorted descending, right-click the category axis, select Format Axis, open Axis Options, and turn on Categories in reverse order. The top-to-bottom display should then follow the desired ranking. Check the value axis position after reversing the categories; Excel may move it to the opposite side, and its position can be adjusted in the same Format Axis pane.
Give the chart a specific title, such as Sales by Category, and make sure the value axis has an appropriate scale. To show exact values, select the chart and choose Chart Design > Add Chart Element > Data Labels, then choose a suitable position. Data labels are particularly useful when bars are close in length. Avoid 3-D effects and unnecessary decoration: they can make comparisons harder rather than clearer.
Make rankings easier to update
If the source numbers change, a formula-based rank column recalculates, but the rows do not necessarily move into their new order. A chart based on the original unsorted rows may therefore remain out of rank order. Re-sort the source before presenting the chart, or use a separate sorted output as the chart source.
In Microsoft 365 and Excel 2021 or later, SORTBY can create a sorted copy while leaving the original list intact. With category names in A2:A6 and sales in B2:B6, enter this in D2, leaving the cells below and to the right clear for the results:
=SORTBY(A2:B6,B2:B6,-1)
The formula returns the two columns sorted by sales in descending order: the values are 67, 67, 55, 42, and 31, with the matching category names beside them. This is a dynamic array formula, so its results spill into adjacent cells. If you use it as a chart source, create the chart from the returned category and value columns and check the chart range when the list grows. Dynamic-array formulas cannot spill inside an Excel Table, so keep the formula output outside the table.
For a report that changes often, make the original source an Excel Table and use it as the maintained data list. Tables can expand as you add rows, but a normal chart does not automatically sort itself by newly changed values. Decide whether your process will sort the table, update a sorted output, or refresh the chart source, and verify the result before sharing it.
Handle ties and choose a ranking style
Ties are common in scores, ratings, and small datasets. RANK.EQ gives tied values the same rank and skips the next position. In the example, two categories share first place and the next category is third. That is a standard competition ranking. If the report instead needs unique positions from 1 to 5, you must define a tie-breaker, such as a secondary measure or alphabetical order. Do not imply that equal values have different performance unless you have a stated rule for separating them.
There are two common display choices. A sorted bar chart emphasizes the measured values and makes the ranking visible through bar length and position. A table with rank numbers emphasizes exact positions and is often better when readers need to look up a category. For a long list, consider showing only the top ten or using a filter rather than compressing dozens of labels into a small chart.
You can also use a column chart, especially when there are only a few categories and labels are short. However, horizontal bars generally handle long names better. A pie chart is usually a poor choice for ranking many categories: comparing similarly sized slices is harder than comparing aligned bar lengths, and the chart can become crowded.
Common problems and how to fix them
- The bars are in the wrong order: Sort the entire data range by the value column, or reverse the category axis as described above. Sorting only one column can detach labels from their values.
- The chart shows rank numbers instead of values: Check Select Data and remove the Rank series if it was included accidentally. Use the category labels and measured values as the chart’s data.
- New rows do not appear: Check whether the chart source includes the added rows. A Table can help the source expand, but confirm that the chart and sort order include the new data.
- There are unexpected gaps in ranks: This is normal with RANK.EQ when values tie. Decide whether shared ranks or a documented tie-break rule better fits the report.
- Names or numbers look wrong: Look for blank cells, text stored in the value column, duplicate categories, or values that were sorted separately from their labels. Correct the source before adjusting the chart.
Frequently Asked Questions
Does Excel have a built-in ranking chart?
No. Build a regular bar or column chart and sort its source data by the measure. A rank formula is optional and is separate from the chart type.
How do I make the highest value appear at the top?
Sort the source from largest to smallest, then, if needed, right-click the chart’s category axis and choose Format Axis > Axis Options > Categories in reverse order.
Can a ranking chart update automatically?
It can reflect changed source values, but the category order may not re-sort automatically. Use a sorted output such as SORTBY in supported Excel versions, or sort the source and check the chart after edits.
How do I show rank numbers on the chart?
Add a rank column to the worksheet and use it as category text or display it in an adjacent table. For most comparisons, show the actual measured values as data labels; ranking numbers alone do not show how far apart the values are.
What is the best chart type for ranking?
A clustered horizontal bar chart is a strong default for comparing categories, especially when labels are long. A column chart can work for a short list with brief labels.
Related
- Open Excel Files Online: Your Options Explained
- Open an Excel Sheet: Quick Steps and File Options
- Open Issues List Template Excel: What to Include
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Chart: How to Change the Data Source SafelyTo change an Excel PivotChart’s data source, select its associated PivotTable, choose PivotTable Analyze > Change Data Source,…
- Excel Chart With Two Different Scales: How to Make OneYes. In Excel for Microsoft 365 on Windows, put one data series on a secondary axis: select the…
- Excel Pivot Chart Axis Labels: How to Rotate Them EasilyTo rotate category-axis labels in an Excel PivotChart, select the labels, right-click and choose Format Axis, then open…
- Make Charts in Excel the Same Size: A Step-by-Step GuideTo make several charts the same size in Excel for Microsoft 365 on Windows, select their chart borders…
- How to Apply an Outline to a Chart Area in ExcelTo outline a chart in Excel for Microsoft 365 on Windows, right-click the chart’s outer background, choose Format…
- How to Copy and Paste a Graph in ExcelTo copy and paste a graph (called a chart in Excel), select its outer border, press Ctrl+C, click…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.