How to Use Stars in Google Sheets to Streamline Scoring

Google Sheets lets you participate prima ratings successful cells with a astute chip. Since galore radical find stars easier to differentiate astatine a glimpse than a database of numbers, this diagnostic whitethorn beryllium utile wherever you privation to complaint products, features, media, apps, places oregon services. The diagnostic offers the aforesaid benignant of 5-star format utilized successful Android and Apple app stores and astatine Amazon, Uber and Yelp.

You whitethorn format cells for prima ratings successful Google Sheets connected the web. Once formatted, you whitethorn prime and participate a standing either successful Google Sheets connected the web oregon successful the Google Sheets mobile apps connected Apple oregon Android devices.

Jump to:

• How to format a compartment for stars successful Google Sheets
• How to participate a prima standing successful Google Sheets
• How to usage a fewer formulas to measure prima ratings

How to format a compartment for stars successful Google Sheets

Working with stars successful a Google Sheet is simply a two-part process. First, you configure a compartment to amusement stars, and past you oregon your collaborators tin participate a prima rating. To format a compartment for ratings:

1. Open a Google Sheet successful a web browser.
2. Place your cursor successful a Google Sheet cell.
3. Type @rating and property instrumentality oregon participate to prime the prima standing constituent from the astute spot paper (Figure A). The recently formatted compartment defaults to a numerical worth of zero and displays nary stars.

Figure A

Figure A. In Google Sheets connected the web, usage the @rating astute spot to format a compartment for prima ratings. Image: Andy Wolber/TechRepublic

How to participate a prima standing successful Google Sheets

Once a compartment has been formatted to show a prima rating, you whitethorn participate the standing either connected the web oregon successful Google Sheets connected a mobile device.

To participate stars portion successful a web browser, specified arsenic Google Chrome:

1. Click oregon pat connected the compartment formatted for stars.
2. From the six paper options that display, prime anyplace from 0 to 5 stars with a click oregon pat (Figure B).

Figure B

Figure B. In Google Sheets connected the web, prime an @rating cell, past take a prima standing option. Image: Andy Wolber/TechRepublic

To participate stars successful the Google Sheets mobile app connected Android, iPhone oregon iPad:

1. Double pat connected a compartment formatted for stars.
2. Select the fig of stars (i.e., 0 to 5) you privation to participate from the database that displays. You whitethorn request to scroll down the database a spot to pat connected the higher numbers (i.e., 4 and 5) (Figure C).

Figure C

Figure C. In the Google Sheets mobile app, pat an @rating cell, past scroll and pat to prime the fig of desired stars. Image: Andy Wolber/TechRepublic

How to usage a fewer formulas to measure prima ratings

Ratings successful Google Sheets are reflected arsenic the numbers zero to five, which means you whitethorn get values from each prima standing cell. The pursuing formulas tin assistance you measure a scope of prima ratings successful Google Sheets (Figure D).

Figure D

Figure D. Since each standing corresponds to a fig betwixt 0 and 5, you whitethorn execute calculations with ratings specified arsenic MIN, MAX, AVERAGE, MEDIAN oregon MODE. Image: Andy Wolber/TechRepublic

What are the lowest and highest ratings?

The Min and Max formulas instrumentality the lowest and highest numbers from a set, respectively. For example:

=Min(C2:C11) =Max(C2:C11)

The quality betwixt the Min and Max indicates the afloat scope of people’s ratings. For example, a Min of 3 and a Max of 4 indicates statement connected a mid-range people from responders, compared to a Min of 1 and a Max of 5, which signals a wider scope of ratings.

What is the mean rating?

The mean of a acceptable of stars volition beryllium determination betwixt 0 and 5, and tin bespeak the wide statement crossed each ratings. When comparing 2 ratings, the higher mean mostly reflects wide higher ratings. To cipher the average, the strategy adds each ratings, past divides the full by the fig of ratings received. For example:

=Average(C2:C11)

What is the mediate rating?

The median is the worth that separates a acceptable successful half, with fractional of the ratings supra the median and fractional of the ratings beneath it. In opposition to the average, which tin beryllium affected by a fewer highly precocious oregon debased ratings, you mightiness deliberation of the median arsenic reliably reflecting the mediate of a set. To get the median, usage a look specified as:

=Median(C1:C11)

What standing was astir provided?

Mode returns the standing astir often recovered successful a set. For example, successful a acceptable of 10 ratings, if 4 of those are 2 stars, 3 are 4 stars, and 3 are 5 stars, the mode would beryllium 2 stars. For example:

=Mode(C2:C11)

Unlike the supra formulas, which volition ever instrumentality a result, Mode mightiness not. For example, successful a acceptable of 10 ratings, if 2 radical each rated items one, two, three, 4 and 5 stars, determination would beryllium nary mode. Since each of the imaginable ratings would person received 2 results, determination would beryllium nary azygous standing that received the most. Similarly, if prima ratings are divided betwixt 2 numbers (e.g., 5 radical rated an point arsenic 4 stars, and 5 radical rated it arsenic 5 stars), again, determination would beryllium nary mode.

Mention oregon connection maine connected Mastodon (@awolber) to fto maine cognize however you usage prima ratings and related calculations successful Google Sheets.