Sentiment analysis in Google Sheets with one formula
Put a five-level sentiment scale in a formula, point it at the review column, and every row gets a level you can count, sort and chart. No add-on to configure, no prompt to maintain.
Why five levels beat positive or negative
A two-way split hides the rows that matter. "It is fine, does the job" and "Love the design, hate the battery" are both mixed, and a positive/negative flag puts them in the same bucket as a rave and a rant. Ordered levels keep the difference, and the model returns a position on the scale, so you can also see how far a row sits between two levels.
The formula
=HUNCHSCORE(B2:B500, "How does the reviewer feel about the product overall?", "angry|disappointed|neutral|happy|delighted", "label")In Excel: =HUNCH.SCORE(B2:B500, "How does the reviewer feel about the product overall?", "angry|disappointed|neutral|happy|delighted", "label")
The last argument, "label", returns the most likely level as a word. Leave it out to get the weighted position from 0 (angry) to 4 (delighted) as a number, which sorts and averages.
Real outputs
Six review-style rows, with the model's answers as returned on 20 September 2026. Column C is the numeric position, column D the label.
| Row | A | B | C |
|---|---|---|---|
| 1 | Review | Position (0–4) | Label |
| 2 | Arrived two days early and the stitching is perfect. Already ordered a second one for my sister. | 3.99 | delighted |
| 3 | Works as described but the app crashes every time I open the history tab. Support answered in a day. | 1.39 | disappointed |
| 4 | Way too expensive for what it is. The materials feel cheap and the strap broke in a week. | 0.46 | angry |
| 5 | It is fine. Does the job. Nothing special, nothing wrong. | 2.00 | neutral |
| 6 | Love the design, hate the battery. Dead by 3pm every day. | 1.06 | disappointed |
| 7 | Shipping took three weeks and the box was crushed, but the product itself is great. | 2.95 | happy |
The mixed reviews land where you would put them: the crushed box with a great product scores 2.95, just under happy; the design-love with a dead battery scores 1.06, disappointed.
Count per level for the report
=COUNTIF(D2:D500, "delighted")Repeat for each level, or build a pivot on column D. For a trend, keep the question text identical between waves; the scale only compares if the question does not move.
Sort by column C descending and read the top and bottom five rows before you trust the summary. Two minutes of spot-checking tells you whether the scale matches your idea of "disappointed".
Questions
Can I use my own scale?
What about reviews in other languages?
How much does 500 reviews cost?
Run it on your own column
Copy the template (or use the Excel add-in), paste your key, point the formula at your data. Every formula here has its Excel spelling underneath. 100 rows free to start, then $29 for 5,000 rows. Credits never expire and there is no subscription.
Reference: HUNCHSCORE