How to Create a Rating Score Chart in Excel Step by Step

To create a rating score chart in Excel, put each item or category in one column and its numeric rating in the next, select the data, then choose Insert > Insert Column or Bar Chart > Clustered Column. For repeated ratings, first calculate an average for each category and chart those averages. This guide uses Excel for Microsoft 365 on Windows and shows how to prepare, format, and troubleshoot a chart that makes scores easy to compare.

What You’ll Need

Excel for Microsoft 365 on Windows. The steps also work in most recent desktop versions; Mac and Excel for the web may place some chart controls differently.
A table of names and ratings, such as product names and customer scores. Use a consistent scale, for example whole-number ratings from 1 to 5.
For a chart of category averages, repeated ratings for each category. The worked example below uses two ratings per category.

Step-by-Step

Choose what the chart should compare. Decide whether each bar should represent one individual response, one product, or a summary such as the average score for a service category. A chart of individual responses is simplest when each item has one score. If many people rated the same categories, an average chart is usually easier to read. Keep the scale and meaning clear: a score of 5 should mean the same thing for every row.
Enter the raw ratings in a clean table. In A1, type Category; in B1, type Rating. Enter each category and its score on a separate row, without blank rows within the data. For example, enter Support and 5 in row 2, Support and 4 in row 3, Quality and 4 in row 4, Quality and 5 in row 5, Value and 3 in row 6, Value and 4 in row 7, Ease of use and 5 in row 8, Ease of use and 4 in row 9, Delivery and 3 in row 10, and Delivery and 2 in row 11. This gives you ten responses across five categories. If each category already has only one score, place the category names in column A and its scores in column B, then skip the averaging steps.
Check that the scores are usable numbers. Make sure ratings are numeric, not text that merely looks like a number. Keep every score within your chosen scale and do not type symbols such as stars into the rating cells. To reduce future entry errors, select B2:B100 or the range you expect to use, then choose Data > Data Validation. Set Allow to Whole number, set the minimum and maximum to 1 and 5, and confirm. Change those limits if your rating scale is different. Validation helps with new entries, but it does not automatically repair values already in the table.
Build a small summary table for repeated ratings. In D1, type Category; in E1, type Average rating. Enter Support, Quality, Value, Ease of use, and Delivery in D2:D6, one per row. These category labels must match the spelling in column A. A summary table prevents a bar for every individual response and gives you one comparable score per category. If there is only one observation for each category, you can chart the original two-column table instead.
Calculate the average for each category. With category names in A2:A11, ratings in B2:B11, and the first summary category in D2, enter =AVERAGEIF($A$2:$A$11,D2,$B$2:$B$11) in E2, then fill the formula down through E6. In this example, the results are 4.5 for Support, 4.5 for Quality, 3.5 for Value, 4.5 for Ease of use, and 2.5 for Delivery. For instance, Support averages (5 + 4) divided by 2, which equals 4.5. AVERAGEIF ignores rows that do not match the category in D2. If a category has no matching numeric ratings, check the spelling and data range; the formula may return a division error.
Select the summary data, including its headings. Select D1:E6. Including the headers helps Excel identify the category names and the average-rating series correctly. Do not include the raw responses in columns A and B as well, or Excel may chart both the individual ratings and the summary and make the result confusing. If your data is in a larger formatted Excel table, select only the two summary columns you want to chart.
Insert the chart. Choose Insert > Insert Column or Bar Chart > Clustered Column. Excel adds a chart with categories along the horizontal axis and average ratings as vertical columns. For longer category names, choose Insert > Insert Column or Bar Chart > Clustered Bar instead; horizontal bars usually leave more room for labels. The exact gallery appearance can vary with the Excel window size, but choose a standard clustered chart rather than a stacked chart for comparing separate category scores.
Give the chart a useful title and labels. Select the chart, then choose Chart Design > Add Chart Element > Chart Title > Above Chart. Click the title box and type a specific title, such as Average Rating by Category. To label the score axis, use Chart Design > Add Chart Element > Axis Titles and select the vertical axis title, then type Rating (1–5). Axis titles are optional if the chart is small and the scale is already obvious, but a reader should not have to guess what the numbers mean.
Make the scale and values easy to interpret. With the chart selected, choose Chart Design > Add Chart Element > Data Labels > Outside End to display the average above each column. To set a consistent rating scale, right-click the value axis and choose Format Axis. Under Axis Options, set the minimum to 0 and maximum to 5 if the ratings use a 1-to-5 scale. This makes the bars comparable and leaves room for labels. Keep one decimal place for averages such as 4.5; do not round the displayed scores so aggressively that meaningful differences disappear.
Review and position the finished chart. Check that every expected category appears once, that higher averages have taller columns, and that the data labels match the summary table. Drag the chart by its outer border to move it, or use the sizing handles to change its dimensions. If the chart will be printed or shared, make it wide enough for category labels and place it near the table that supports it. Save the workbook with a clear file name so readers can find the source data as well as the chart.

On Excel for Mac, use the Insert tab and its chart controls to select a clustered column or bar chart; the chart design and formatting commands are similar, though their locations can differ slightly by release. In Excel for the web, select the data and use Insert to add a chart, then use the chart tabs or formatting pane to edit its title and labels. If a control is not visible, select the chart first and look for the contextual Chart Design or Format tab.

Common Mistakes

Mixing up the scale. A table that combines ratings from 1 to 5 with ratings from 1 to 10 does not produce a fair comparison. Standardize the scale before averaging, or convert the scores consistently and explain the conversion.

Charting the wrong range. Selecting unrelated columns, blank rows, or both raw responses and averages can create extra series or blank categories. Select only the summary labels and summary scores, including their headings.

Using text instead of numeric scores. Entries such as Excellent or five stars may be meaningful to a person but cannot be averaged as numeric ratings. Keep a numeric score in the chart data and, if needed, put the descriptive label in a separate column.

Hiding the scale context. A column chart can exaggerate small differences when its value axis starts above zero. For ratings intended to compare bar heights, use a zero baseline and set a consistent maximum, such as 5 for a five-point scale. If you deliberately use a different axis range, make it obvious so the chart does not mislead.

Rounding away useful detail. Averages of whole-number ratings often include decimals. Showing every result as a whole number can make 4.4 and 4.6 look identical. Display one decimal place when that precision helps readers distinguish results, while avoiding extra decimals that imply more certainty than the responses support.

Tips

Turn the raw data into an Excel table by selecting a cell in it and choosing Insert > Table, then confirming that the table has headers. Tables make it easier to filter and add records, and they help keep data organized. Keep the summary table separate so the chart continues to use an intentional set of categories.

Use a consistent order for categories. You can order them by a meaningful sequence, such as the customer journey, or sort by average rating to make the strongest and weakest scores easy to spot. If you sort, include both summary columns in the selection so category names stay beside the correct averages. Avoid relying on color alone to communicate a score; clear labels and a scale are more accessible.

For a quick view of how many people chose each score, make a separate frequency chart rather than mixing counts with category averages. Create a score list from 1 through 5 and count how many times each score appears. With ratings in B2:B11 and a score value of 1 in D2, enter =COUNTIF($B$2:$B$11,D2) in E2 and fill down beside score values 2 through 5. The example data returns counts of 1, 2, 3, 2, and 2 for scores 1, 2, 3, 4, and 5 respectively. Chart that score-and-count summary to show the rating distribution; label its axes as Score and Number of responses. This answers a different question from the category-average chart, so give the charts distinct titles.

Keep a count beside averages when sample sizes vary. An average based on two responses is less stable than one based on a hundred. The chart can still compare the means, but showing the response count in an adjacent table or note gives readers important context. Do not interpret a small difference between averages as a meaningful change without considering the number and pattern of responses.

Troubleshooting

The chart shows Series 1 or the wrong labels. Check that your selection includes the category and value headers and that categories are in one column while numbers are in the next. Select the chart and use Chart Design > Select Data to inspect the chart data range and series. If the labels and values are reversed, correct the range or use Switch Row/Column where appropriate.

Excel does not include new rows. If you add responses after building the chart, check whether the plotted range expanded. Click the chart and choose Chart Design > Select Data to review its source range, then extend the range if necessary. If you used an Excel table, add rows directly below the table so it can expand; confirm that the summary formulas also include the new response rows.

The average formula returns an error or an unexpected result. Confirm that the formula ranges cover the actual data, that the category text in D2 matches column A, and that the rating cells contain numbers. AVERAGEIF does not count blank cells as ratings. If the result is unexpectedly low, check for a mistakenly entered zero or an out-of-scale score. If it is unexpectedly high, look for duplicated responses or a rating stored as text and excluded from the calculation.

The chart looks flat or scores seem exaggerated. Right-click the vertical value axis and choose Format Axis. Confirm the minimum and maximum are appropriate for the rating scale. A maximum much larger than the possible score compresses the bars; a nonzero minimum can overstate small differences. For a 1-to-5 rating chart, a zero minimum and five maximum are a straightforward baseline.

Labels overlap or values are hard to read. Enlarge the chart, use a clustered bar chart for long category names, or shorten labels in the source summary. If the data labels collide, select them and choose a different position through Chart Design > Add Chart Element > Data Labels. Remove unnecessary gridlines or a legend if there is only one data series.

Frequently Asked Questions

Can I make a rating chart without calculating averages?
Yes. If each category has one score, select the category and score columns and insert a clustered column or bar chart directly. If each category has multiple responses, charting every response may be cluttered; use averages for comparison or a separate frequency chart to show the spread.

Should I use a bar chart or a column chart?
Both can compare rating scores. Use columns when labels are short and you want a familiar vertical chart. Use horizontal bars when category names are long or there are many categories. Keep the rating scale consistent whichever orientation you choose.

Can Excel show stars instead of numbers?
Keep numeric scores for calculations and charting. You can add a separate display column that maps scores to symbols, but star characters may not behave like numbers and can be hard to compare on an axis. Numeric data labels and a clear 1-to-5 scale are generally easier to interpret.

How do I show the overall average rating?
Calculate an average across the numeric rating cells and display it in a clearly labeled cell or chart callout. With ratings in B2:B11, the formula is =AVERAGE(B2:B11); for the example data, it returns 3.9 because the ten scores total 39. Make clear whether this is the average of all responses or an average of category averages, since those can differ when categories have different numbers of responses.

Why do my category averages differ from the overall average?
The overall response average weights every response equally. A simple average of category averages gives each category equal weight, even if one category has many more ratings than another. If response counts differ, explain which method you use and consider showing both the category averages and their counts.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.