Examples of Ratings in Excel: Scores, Stars, and Rules

Examples of ratings in Excel include 1-to-5 customer scores, star displays, letter-grade categories, and color-coded performance scales. For a quick summary, put numeric ratings in B2:B6 and calculate their average with =AVERAGE(B2:B6); if the five ratings are 5, 4, 3, 2, and 5, the result is 3.8.

What a rating means in a spreadsheet

Excel does not impose one special meaning or built-in format for the word “rating.” A rating is usually data that you enter or calculate, then summarize or display using ordinary worksheet features. It might describe customer satisfaction, product quality, a service experience, employee performance, a risk level, or a reviewer’s opinion.

The most useful rating design depends on what is being judged and how people will interpret the result. A numeric scale is easy to average and sort. A category such as “Good” is easier to read at a glance, while stars or colors make a dashboard more visual. These formats can be combined: keep the underlying score as a number and show a label or visual cue in another column.

Numeric: a score from 1 to 5 or 1 to 10.
Categorical: labels such as Poor, Fair, Good, and Excellent.
Visual: stars, icons, or cell colors tied to a score.
Composite: a total or weighted score built from several criteria.

Keeping the original score is important. If a worksheet stores only colors or star symbols, later averages, comparisons, and checks may be harder or impossible without converting that display back into data.

Example: enter and summarize a 1-to-5 rating

Suppose a small product survey has a product name in column A and a customer rating in column B. Enter the headings Product and Rating in A1 and B1, then use rows 2 through 6 for five responses. For example, the scores could be 5, 4, 3, 2, and 5. This layout makes each rating traceable to the item or response it describes.

With the five numeric scores in B2:B6, the mean rating is =AVERAGE(B2:B6). For those example values, Excel returns 3.8: their sum is 19, divided by five responses. The average is useful for a broad summary, but it does not show whether most people chose 4 or 5, or whether responses were split between high and low scores.

With the same scores in B2:B6, the number of top ratings is =COUNTIF(B2:B6,5). It returns 2 because two entries equal 5. This count helps put an average in context. You could also count low scores or calculate how many responses are present, particularly when the survey may contain blank cells.

For a larger dataset, turn the range into an Excel table by selecting it and choosing Insert > Table. Tables expand as rows are added and can make filtering and structured references easier. A simple worksheet range is perfectly adequate for a small example; the key is to use one row per response and keep the score column consistently numeric.

Example: convert scores into rating categories

Some reports need words rather than numbers. You can create a category in a new column while retaining the original score. For instance, define 4–5 as High, 3 as Middle, and 1–2 as Low. Write those thresholds down before applying them so that coworkers understand the same score will always receive the same label.

With a numeric score in B2, enter this formula in C2 and copy it down: =IF(B2>=4,"High",IF(B2=3,"Middle","Low")). For a score of 5, the result is High; for 3, it is Middle; and for 2, it is Low. The formula assumes scores are valid numbers from 1 to 5. It will label a blank or an out-of-range value Low, so use input controls or an additional check if those cases are possible.

This style of formula is suitable when the bands are simple and unlikely to change. For many categories or frequently revised thresholds, a small lookup table can be easier to maintain than a long chain of nested IF functions. Document whether a boundary belongs in the higher or lower band: “4–5” and “1–3” is unambiguous, while a vague phrase such as “around four” is not.

Example: display a score as stars

Stars are a familiar display for product and service ratings, but they should generally be a presentation layer rather than the only stored value. A score of 4 can be shown as four star characters while the adjacent numeric cell remains available for analysis. The star character’s appearance depends on the font and device, so check that the chosen font displays it cleanly in the workbook and in exported files.

With a whole-number score from 1 to 5 in B2, put =REPT("★",B2) in C2. If B2 contains 4, the formula repeats the star character four times. Copy it down to display the other scores. This compact approach displays filled stars only; it does not automatically add empty stars to make every row a five-star graphic. It also assumes B2 contains a valid nonnegative integer. A text score or negative number is not a suitable input.

For a more polished visual, you can instead use conditional formatting or a chart, depending on the report. Star characters are quick to understand, while a numeric score is generally more convenient for sorting, calculating averages, and checking exact values. Including both avoids forcing a reader to infer the underlying number from a graphic.

Example: compare several criteria with a weighted rating

A single overall rating can hide important differences. A hotel, for example, might receive separate scores for cleanliness, location, and service. If each criterion matters equally, an ordinary average may be appropriate. If some criteria matter more, assign weights that reflect the intended scoring model and explain them to readers.

Suppose the scores are in B2:B4 and their weights are in C2:C4: cleanliness is 4 with weight 50%, location is 5 with weight 30%, and service is 3 with weight 20%. With those values in those cells, calculate the weighted rating using =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). The result is 4.1: (4 × 0.50) + (5 × 0.30) + (3 × 0.20) equals 4.1, and the weights sum to 1. If weights are entered as 50, 30, and 20 instead of percentages, the same formula returns 4.1 because it divides by their total of 100.

Weights are not neutral: changing them changes the final score. Make the assumptions visible, ensure the weights have a meaningful total, and avoid presenting a weighted result as a purely objective fact. If all criteria should have equal importance, using a simple average is easier to explain.

Choose a scale and control the input

A 1-to-5 scale is compact and familiar, while a 1-to-10 scale allows more distinctions but may encourage users to assign arbitrary precision. A scale with an odd number of choices includes a midpoint; an even-numbered scale does not. Decide whether “not applicable” or “no response” is allowed, and store it distinctly from a genuine low score. Otherwise, an average may treat missing feedback as a poor rating.

In Excel for Microsoft 365 on Windows, select the cells where people will enter scores, then choose Data > Data Validation. In the dialog, set Allow to Whole number, choose a condition such as between, and enter 1 and 5 as the minimum and maximum. You can also add an input message and an error alert to explain the allowed range. This reduces accidental entries such as 0 or 7, but it does not replace checking imported or pasted data.

Use the same scale and definitions across all rows in a dataset. If one sheet treats 5 as best and another treats 1 as best, combining their data without correcting the direction produces misleading summaries. Include a short note such as “1 = very dissatisfied; 5 = very satisfied” near the input area, especially when other people will use the workbook.

Make ratings easier to scan with conditional formatting

Conditional formatting changes a cell’s appearance based on its value while leaving the underlying score intact. Select the rating cells, then use Home > Conditional Formatting > Color Scales to apply a gradient, or choose Home > Conditional Formatting > Highlight Cells Rules to set rules for particular values. For a 1-to-5 scale, a color scale can show low-to-high patterns quickly; individual rules can emphasize ratings of 1 or 5.

Color choices should reinforce, not replace, the number. Red-to-green scales may be difficult for some readers to distinguish, and colors can print differently or disappear in a plain-text export. Use labels, symbols, or clear column headings as well. A dashboard should also make its direction obvious: a high score may be good for satisfaction but bad for risk or defect counts.

Before applying a rule to a large range, check the minimum and maximum values and whether blanks should be formatted. A gradient that automatically rescales to the data can make a narrow range look dramatic; a fixed scale may be more suitable when comparing separate periods. The appropriate choice depends on whether the goal is to show variation within one group or compare groups against the same standard.

Common rating mistakes and useful alternatives

The most common analytical mistake is treating an average as the whole story. Rating scales are often ordinal: a score of 4 ranks above 3, but the difference between 3 and 4 may not represent exactly the same amount of satisfaction as the difference between 1 and 2. Averages are widely used as summaries, but pair them with the response count and, when relevant, the distribution of scores. A mean of 3.8 from five responses is less stable than the same mean from five hundred.

Other frequent problems include mixing text and numbers in one score column, leaving the scale undefined, counting blanks as responses, and averaging scores that run in opposite directions. Keep raw responses unchanged when possible, and make any recoding or exclusions explicit. For a rating report, useful context can include the number of valid responses, the share of top-box ratings, and a breakdown by score rather than a single headline number.

Different tools suit different purposes:

Data validation helps restrict new entries to an allowed numeric range.
Conditional formatting emphasizes patterns in an existing score range.
PivotTables can group and summarize ratings by product, date, or other fields.
Charts can show the distribution or trend, provided the scale and labels remain clear.

These features can work together. For example, a survey table can store one numeric rating per response, use a PivotTable to count scores by product, and use a chart to compare the results. The data structure matters more than the decoration: a clean table with clearly defined fields is easier to summarize than a sheet built mainly from merged cells, manually colored ratings, or symbols.

Frequently Asked Questions

How do I calculate an average rating in Excel?
Put numeric ratings in one range, such as B2:B20, and use =AVERAGE(B2:B20). AVERAGE ignores blank cells and includes numeric entries, so check that the range contains only valid scores and that missing responses are not represented by a number such as 0 unless zero is part of the scale.

How do I count five-star ratings?
With numeric ratings in B2:B20, use =COUNTIF(B2:B20,5). It counts cells equal to 5. If the cells contain text such as “5 stars” instead of the number 5, this criterion will not count them as numeric scores.

Can Excel show ratings as stars?
Yes. One option is to use REPT with a star character in a separate display column, while keeping the numeric score in its own cell. You can also use conditional formatting or chart elements for a visual summary. Check the font and export format because symbols may render differently across systems.

Is an average the best way to summarize ratings?
Not always. An average is a compact measure, but it can conceal a polarized set of responses or a small sample. Include the response count and consider showing how many ratings fell into each score category, especially when readers need to understand disagreement or compare groups.

How can I prevent ratings outside a 1-to-5 scale?
Select the input cells and choose Data > Data Validation. Set the allowed value to a whole number between 1 and 5, then provide an explanatory error alert. Review pasted or imported data separately, since validation is not a guarantee that every existing value in a workbook is valid.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.