Chart & Visualization Issues

How to Calculate Supplier Rankings and Create a Bar Chart in Excel

Natalie Taylor Sep 28, 2026 869 views

Question details

The user needs to summarize survey results where suppliers ranked nine business areas and display the final scores in a bar chart.

How to Calculate Supplier Rankings and Create a Bar Chart

Product: Excel
Device & OS: not provided
Scenario: Evaluating and summarizing survey results across multiple categories (e.g., strategic planning, program management) ranked from 1 to 9.
Observed behavior: The user wants to compute the overall ranking for each category across all suppliers and visualize the data effectively.

Before you start

Ensure all your survey data is organized in a table with business categories in one column or row, and the corresponding supplier rankings in adjacent cells. Confirm whether a lower number (e.g., 1) indicates a better ranking before analyzing the results.

Solution 1Recommended

Calculate Category Averages and Insert a Bar Chart

Use the AVERAGE function to find the mean ranking for each category, sort the results, and generate a clear visual representation using a bar chart.

To get an accurate overall ranking, you need to calculate the average score each category received across all suppliers. Once calculated, sorting these averages helps to present the data logically in a chart.

Calculate the average for each category

In an empty column next to your data, use the formula =AVERAGE(range) where 'range' contains all supplier scores for a specific category (e.g., strategic planning). Drag the fill handle down to apply this formula to all nine categories.

Sort the calculated averages

Select both the category names and their newly calculated average scores. Go to the Data tab and click Sort. Sort the data by the average score from smallest to largest (or largest to smallest, depending on your ranking system).

Insert a bar chart

Highlight the sorted category names and average scores. Navigate to the Insert tab on the ribbon, click on the Insert Column or Bar Chart icon, and select a 2D Clustered Bar Chart.

Customize the chart

Click on the chart to reveal Chart Elements. Add a descriptive Chart Title (e.g., 'Supplier Ranking by Business Area'), enable Data Labels for exact scores, and adjust axis titles for better readability.

Calculate Category Averages and Insert a Bar Chart

Chart Type Selection: A bar chart (horizontal) is highly recommended over a column chart (vertical) for this scenario, as long category names like 'Supply chain management' and 'Design management' are easier to read on a horizontal axis.

Powerful Spreadsheet Tool

Easily Calculate Rankings and Create Charts with WPS Spreadsheet

WPS Spreadsheet provides robust data analysis functions and intuitive charting tools to quickly summarize your survey data and create professional bar charts.

1. Open Data: Launch WPS Spreadsheet and open your supplier ranking data file.
2. Calculate Averages: Use the =AVERAGE() function to calculate the mean score for each business area.
3. Create Chart: Select the categories and averages, go to the 'Insert' tab, click 'Chart', and choose 'Bar'.
4. Format and Export: Customize the chart title and colors, then save or export the report as a PDF.

Built-in AVERAGE function for fast and accurate score calculations100% compatible with Microsoft Excel (.xlsx) files and formulasRich gallery of customizable bar and column chart templatesFree, lightweight, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

How does Excel handle empty cells when calculating the average ranking?

The standard =AVERAGE() function automatically ignores empty cells. If a supplier did not rank a particular category and the cell is blank, it will not affect the denominator used to calculate the average.

How can I reverse the order of categories on the vertical axis of my bar chart?

Right-click the vertical axis (where the category names are) and select 'Format Axis'. In the Axis Options pane, check the box for 'Categories in reverse order' to flip the display.

Can I highlight the top-ranked category in a different color on the chart?

Yes. Click once on the bars to select all of them, then click a second time specifically on the bar for the top-ranked category. Right-click, select 'Format Data Point', and choose a different fill color.

Excel & Data Problems