Rating Chart in Excel: Build Stars and Score Charts

To make a five-star rating chart in Excel, enter whole-number ratings from 1 to 5 in column B, then put this formula in C2 and fill it down: =REPT("★",B2)&REPT("☆",5-B2). With a rating of 4 in B2, it displays four filled stars and one empty star; for a chart of how often each score occurs, count ratings by score and insert a clustered column chart.

What a rating chart shows

“Rating chart” can mean more than one thing in Excel. It may be a visual display of an individual score, such as five stars beside a product review, or a chart summarizing a group of scores, such as the number of people who chose each rating. These displays answer different questions, so decide what you want readers to see before choosing a chart type.

A star display makes individual records easy to scan, but it is not itself a conventional Excel chart. It is text generated in a worksheet cell. A column chart, by contrast, can show the distribution of scores: whether most respondents chose 5, whether ratings are spread evenly, or whether low scores deserve attention. You can use both in the same workbook: stars in the detail table and a chart on a summary sheet.

Keep the underlying rating as a number even if you add stars or colors. Numeric values are easier to average, count, filter, and chart. Treat the visual as a presentation layer, not as a replacement for the data.

Create a five-star display with a formula

Start with a simple table: put a reviewer, product, or other item in column A, and its whole-number rating from 1 to 5 in column B. Add a heading such as Star display in C1. With the rating in B2, enter this formula in C2, then copy it down the column:

=REPT("★",B2)&REPT("☆",5-B2)

For example, if B2 contains 4, the first REPT part returns four filled stars and the second returns one empty star. The combined result is ★★★★☆. A score of 1 displays ★☆☆☆☆, and a score of 5 displays ★★★★★. REPT repeats text a specified number of times, while the ampersand joins the two strings.

This formula assumes ratings are integers from 1 through 5. A blank cell or a value outside that range may produce an unexpected display, so validate the data before relying on the visual. A font such as Segoe UI Symbol usually supports the star characters, but appearance can vary by font and platform. If the stars look like boxes, select the display cells and choose a font that includes those symbols.

For ratings out of 10, change both occurrences of 5 in the formula to 10 and use the same 1-to-10 scale for the entries. If you want half-stars or ratings with decimals, this simple formula is not suitable: REPT repeats a whole number of times, and a decimal rating does not map cleanly to a half-star image. Keep the numeric score visible or choose a separate visual system for fractional values.

Control which values can be entered

Data validation reduces mistakes before they affect formulas or charts. Select the cells intended for ratings, such as B2:B101, and choose Data > Data Validation. In the settings, choose Whole number, set the data condition to between, and enter 1 and 5 as the minimum and maximum. You can also add an input message explaining the scale and an error alert that asks users to enter a whole number from 1 to 5.

In Excel for Microsoft 365 on Windows, those commands are on the Data tab. Excel for Mac also provides Data Validation from the Data tab, although the dialog layout can differ. If the workbook will be edited in Excel for the web, check the validation behavior in the browser version as well; some desktop dialog options may not be presented in the same way.

Decide how to handle missing ratings. A blank should normally mean “not rated,” not zero, because zero is outside a five-point scale and can pull down calculations if it is entered as a score. If a rating is not yet available, leave its cell empty and make the chart’s treatment of blanks clear.

Summarize ratings and insert a chart

For a distribution chart, create a small summary table away from the raw data. For example, enter the possible ratings 1, 2, 3, 4, and 5 in D2:D6, with a heading called Rating, and enter Count in E1. With the original ratings in B2:B101 and the rating category in D2, enter this formula in E2 and fill it down through E6:

=COUNTIF($B$2:$B$101,D2)

COUNTIF counts entries equal to the category shown in column D. For example, if there are 20 ratings in the data and three of them are 3, the row with 3 in D will return 3 in E. The five counts should total the number of valid ratings in the source range; if they do not, check for blanks, values outside 1–5, or numbers stored as text.

Select D1:E6, including the column headings, then choose Insert > Insert Column or Bar Chart > Clustered Column. Excel adds a chart with rating values along the horizontal axis and counts on the vertical axis. Give it a descriptive title such as Rating distribution. If the category labels or series appear in the wrong orientation, use Chart Design > Switch Row/Column.

A clustered bar chart is another sensible choice, especially when category labels are long. In Microsoft 365 for Windows, choose Insert > Insert Column or Bar Chart and select a clustered bar option. Excel for Mac has the corresponding chart choices on the Insert tab, though the gallery arrangement can differ. For a five-category rating scale, a column chart is usually straightforward to read; a pie chart makes exact comparisons between adjacent scores harder.

Choose a chart for the question

Use the chart that matches the decision a reader needs to make. A score distribution is often the most informative overall summary because it preserves the shape of the responses. Averages can hide important differences: two groups may both average 4, while one group gives mostly 4s and the other gives a mix of 1s and 5s.

Rating distribution: Use a column or bar chart to compare the number of responses at each score.
Ratings by product or team: Use a clustered bar or column chart when comparing a modest number of groups. Label the categories clearly.
Ratings over time: A line chart can show a trend in average ratings by week or month. Include the number of responses for each period, because a change based on two responses is less stable than one based on hundreds.
Individual records: Use the star formula in a table, rather than plotting every record as a chart category. A chart with too many individual items becomes difficult to read.

An average can be useful alongside a distribution, but it is not a substitute for showing how scores are spread. Also state the scale: a chart labeled only “Rating” does not tell a reader whether 5 is best, whether 1 is best, or what the endpoints mean. A note such as “1 = poor; 5 = excellent” prevents that ambiguity.

Format the chart for accurate reading

Make the chart title, axes, and categories understandable without requiring the reader to inspect the source table. Use a vertical axis title such as Number of ratings, keep score categories in numerical order, and avoid decorative effects that make bar heights hard to compare. If the chart starts its value axis above zero, small differences can look much larger than they are; for counts, a zero baseline is generally the clearest choice.

For a small five-category chart, data labels can help readers see exact counts. Select the chart, then use Chart Design > Add Chart Element > Data Labels and choose a position. In some Excel window sizes, chart element controls may be displayed differently, but the Chart Design tab offers the chart-element commands. Do not add labels if they overlap or make the chart crowded.

Colors can draw attention to low ratings, but do not use color as the only way to convey meaning. Some readers have difficulty distinguishing colors, and charts may be printed in grayscale. Keep the rating numbers visible, use adequate contrast, and explain any color convention in a legend or note.

Common problems and what to check

The formula shows an error or odd number of stars: Check that the input is a whole number from 1 through 5 and that the formula points to the correct rating cell. Text entries such as “4 stars” are not numeric ratings.
Stars appear as empty boxes: Change the font in the display cells to one that supports the star characters. The numeric rating remains usable even if the symbol is unavailable on a particular device.
A score is missing from the chart: Confirm the summary table includes every possible score and that its COUNTIF range covers the complete data range. Check for numbers stored as text, which may not match numeric categories as expected.
Counts look too high or too low: Look for duplicate submissions, blank rows, or ratings outside the intended scale. Decide whether each row represents one response before interpreting totals.
The chart changes when new records are added: A summary formula with a fixed range such as B2:B101 does not automatically include entries below row 101. Extend the range, or store the source data as an Excel table so new rows are easier to include in the analysis.

For a reusable workbook, consider converting the source range into a table with Insert > Table. Tables can expand as records are added, and formulas or charts that refer to table data are easier to maintain than manually adjusted ranges. Review the chart after adding data to ensure the new records are included.

Alternatives to a star rating chart

If the goal is to show a score inside a worksheet rather than build a chart, conditional formatting can add visual emphasis. Select the numeric rating cells and choose Home > Conditional Formatting to apply a color scale or rules for low and high values. A simple color scale may imply that the gaps between scores are equal, so use it only when that visual interpretation is appropriate. It should not replace the actual numbers.

Another option is a bar-style display using conditional formatting data bars, available from Home > Conditional Formatting > Data Bars. This is useful when comparing scores down a list, but set the scale deliberately and retain the numeric value. For a report that needs both exact counts and a quick visual impression, a small summary chart plus the underlying table is usually clearer than a complicated dashboard.

Frequently Asked Questions

How do I make a 1-to-5 star rating in Excel?
Put a whole-number score from 1 to 5 in a cell, then use =REPT("★",B2)&REPT("☆",5-B2) when the score is in B2. Copy the formula down for other rows and use a symbol-supporting font if needed.

How do I count how many 5-star ratings I have?
Create a summary count for the value 5, or use a COUNTIF formula with the source rating range and 5 as the criterion. Check that the range includes all records and that ratings are stored as numbers.

Can Excel make a star chart automatically?
Excel does not need a special chart type for star symbols. A worksheet formula can display stars beside each record, while a standard column or bar chart can show the number of ratings in each category.

Should I use an average or a rating distribution?
Use the distribution when the pattern of responses matters, and include an average only as a companion summary. An average alone can conceal a split between very high and very low ratings.

Why are my rating counts not adding up?
Check for blank cells, entries outside the allowed scale, numbers stored as text, and a summary formula range that stops before the last record. The total of the category counts should match the number of valid ratings included in the analysis.

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.