How to Show Ranking in Excel Chart

A basic bar or line chart in Excel can show you sales figures or website traffic, but illustrating how rankings change over time requires a few clever tricks. Turning a simple list of numbers into a clear, visual story of who's rising and falling isn't hard, but it's not immediately obvious how to do it. This article will walk you through, step by step, how to set up your data and create a dynamic ranking chart in Excel.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Bother Visualizing Your Ranks?

Ranking data on its own is just a list of numbers. When you plot it on a chart, however, that list becomes a powerful visual narrative. A ranking chart instantly lets you and your team spot a few key things:

• Identify Trends: Is a particular sales rep consistently improving? Is a specific product slipping in popularity? A chart makes these long-term trends immediately obvious in a way that raw numbers just can't.
• See Competitive Dynamics: When you track your company, product, or keyword rankings against competitors, you can see exactly when someone pulls ahead or falls behind. It's perfect for competitive analysis.
• Tell a Story Quickly: During a presentation, showing a chart where one line steadily climbs to the #1 spot is far more compelling than displaying a static table. It tells the story of progress and success at a glance.

Common examples include tracking sales reps by monthly revenue, monitoring e-commerce products by units sold, following sports league standings, or even visualizing your website's SEO keyword positions over several weeks.

Step 1: Get Your Data Ready for Ranking

Before you can create the chart, you need to structure your data correctly and calculate the ranks. A well-organized table is the foundation for a successful ranking chart. You simply can't skip this part.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Organize Your Raw Data

Start by setting up a simple table with your items in one column and their performance values across subsequent columns for different time periods. Let's use an example of ranking five sales representatives based on their quarterly sales.

Your raw data might look something like this:

This table shows us the performance but not the ranks. Our next step is to create a new table that calculates the rank for each representative in each quarter.

Calculate Ranks Using the RANK.EQ Function

The RANK.EQ function in Excel is perfect for this. It tells you the rank of a specific number within a list of other numbers. A higher number will get a lower rank value (e.g., the person with the highest sales gets rank #1).

1. Create a New Table: To keep things clean, create a separate table for your rankings right next to or below your raw data. Set it up with the same headers and row labels.
2. Write Your First Formula: In the first cell of your ranking table (e.g., Ana's Q1 Rank), type the following formula:

=RANK.EQ(B2,$B$2:$B$6,0)

Let's break that down:

• B2 is the sales value you want to rank (Ana's Q1 Sales). This is a relative reference.
• $B$2:$B$6 is the range of all sales values for that quarter. The dollar signs make this an absolute reference. This is extremely important! It ensures that when you drag the formula down, the range it checks against doesn't change.
• 0 tells Excel to rank in descending order, where the highest value gets rank #1.

1. Drag to Fill: After entering the formula, click on the small green square in the bottom-right corner of the cell and drag it down to fill the rest of the column for Q1. Then, drag that entire column across for Q2 and Q3. Excel will automatically adjust the formula for each period, while keeping the absolute reference ranges correct.

Your final ranking table should look like this:

Now that your data is properly structured, you're ready to start building the chart.

Step 2: Create a Basic Ranking Chart with an Inverted Y-Axis

This is the simplest way to create a functional ranking chart. We'll use a standard line chart and then flip the vertical axis so that rank #1 appears at the top, just as you'd naturally expect to see it.

Insert the Line Chart

1. Highlight the data in your ranking table. Make sure you select the names of the reps, the headers (Q1 Rank, Q2 Rank, etc.), and all the rank values.
2. Go to the Insert tab on Excel's ribbon.
3. In the charts section, click "Insert Line or Area Chart" and choose the "Line with Markers" option. Markers are the dots that appear on the line, which help pinpoint the exact rank at each time period.
4. Excel will immediately generate a chart. You'll notice something is a bit off: rank #5 is at the top of the chart, and rank #1 is at the bottom. This looks weird and is confusing to read. Let's fix that.

Invert the Y-Axis

1. Right-click on the numbers of the vertical axis on the left side of your chart (e.g., the 1, 2, 3, 4, 5).
2. From the dropdown menu, select "Format Axis..."
3. A sidebar will open called "Format Axis." Within the "Axis Options" tab (usually represented by a tiny bar chart icon), scroll down until you see the checkbox for "Values in reverse order."
4. Check this box. The chart will instantly update, and now Rank 1 will be where it belongs - at the very top!

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Small Cleaning Touches

Your chart is functional now, but a couple of small adjustments will make it look professional.

• Adjust Axis Bounds: Sometimes, Excel leaves extra space. In the same "Format Axis" pane, you can set the "Maximum" bound to your highest rank value (e.g., 5) and the "Minimum" bound to 1. This tightens up the chart. Also, set the "Major units" under "Units" to 1 so the scale doesn't show decimals like 1.5, 2.5, etc.
• Add a Title: Click on "Chart Title" at the top of the chart and give it a helpful name, such as "Quarterly Sales Rep Rankings."
• Keep Horizontal Axis Clear: The horizontal axis is showing "1", "2" and "3" for axis labels by default. Let's change this to "Q1", "Q2" and "Q3". In order to fix this, left-click anywhere on your chart, then right-click and select "Select Data..." Within the "Select Data Source" sidebar, click edit under "Horizontal (Category) Axis Labels". In the sidebar opened up titled "Axis Labels", you can select your column labels "Q1 Rank", "Q2 Rank", "Q3 Rank" from your "rank" data source table.

Step 3: Advanced Method - Creating a Bump Chart

While the inverted line chart technique works well, a "bump chart" is a gold standard for visualizing rank changes. These charts, created using a Scatter plot, allow you to put labels directly on the lines, which looks fantastic and is much easier to read, especially with many competitors.

Restructure Your Data for a Scatter Plot

Scatter charts require data in a different format. Instead of a wide table, you need a "long" table. This sounds complicated, but it's just a different way of organizing the same information. Your table should have three columns: Name, Period, and Rank.

For our sales rep data, it would look like this:

Using numerical values for the period (1 instead of "Q1," 2 instead of "Q2") is crucial for the scatter plot to work correctly. You have to manually adjust your table to look like this format.

Here's the full "long" version based on our "Sales Rep Rank" dataset. Left-click anywhere on your "rank data source" table and copy it to another sheet by selecting paste special > transpose. Use this method twice for each column and your "Sales Rep" column to create the table below:

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Create the Bump Chart with a Scatter Chart

To convert this table to the ultimate ranking plot:

• Use Excel's filtering option to filter out the above information for each period of time. After applying the filter:

• Create a scatter chart by highlighting the first two columns of your new table: go to Insert > Charts > Scatter/XY, and select the "Scatter with Smooth Lines and Markers". Leave 'Chart Title' blank.
• Edit your plot: In 'Chart Design', click on 'Select Data'. Right-click anywhere on your scatter chart, click "Select Data..." >, on the left side panel of the opened sidebar click "Add". The first field 'Series name' is your representative name: "Ana". Under 'Series X values', click the selection icon and select the Period value only. For 'Series Y Values', click the selection icon and select the Rank values for that specific representative.
• Right-click your numbers on your Vertical axis and in the sidebar, check "Values in reverse order". In 'Axis options' >, set bounds: Minimum=1 and Maximum=the highest rank (e.g., 5). This will produce a chart where rank #1 is at the top, which is more intuitive.

Step 4: Use Color Intentionally

• Highlight One Line: If you are highlighting the performance of one particular rep or product ("our product"), make that line a bold, bright color and make all other lines subtle grey. That draws attention to the key performer.
• Color-code Lines: Right-click on one of the lines, and at the right sidebar, click the 'Bucket' icon. Pick a different color for each line to improve readability.
• Label for Clarity: Adding Labels to Bump Charts: Right-click the series to which you want to add labels, select "Add Data Label". Format labels by right-clicking and choosing your preferred position or label source, such as the name of the representative for better understanding.

Final Thoughts

Creating a rank change chart in Excel boils down to setting up your data correctly with the RANK.EQ function and then inverting the chart's vertical axis. Whether you opt for the quick line chart method or a more advanced bump chart, you can effectively turn a dull list of performers into a dynamic visualization of progress and competition.

While Excel is certainly powerful, managing this process—recalculating ranks, ensuring formulas are correct, and refreshing charts—can require quite a bit of labor, especially if your data is constantly changing or pulled from different sources like your CRM or advertising platforms. At Graphed, we handle all that manual work automatically. By connecting directly to your tools, we pull in your data in real-time, allowing you to build the visualizations you need by simply asking for them. Instead of building pivot tables and wrestling with chart settings, you could just say "create a bump chart to show the performance of my product against my competitors in the last 4 months ranked according to profit and a label for each line with the name of the competitor" and get a better live, shareable result in seconds.