Sentiment analysis of comments in Google Sheets without code
You do not need Python or a paid add-on to get a first reading of how a comment section feels. A word list and a SUMPRODUCT formula will label a few thousand comments in seconds. What it will not do is understand sarcasm, so this guide also shows how to measure how wrong it is.
Updated Nov 19, 2026 · 7 min read
What this method can and cannot tell you
A lexicon method counts words. "Love this" scores positive, "terrible quality" scores negative, and a comment with neither is neutral. On short social comments that is right more often than you might expect, because most comments are short and blunt. It is wrong on sarcasm ("great, another price rise"), on negation it was not told about, on slang, and on languages the list does not cover.
Use it to compare: this post against last month's, replies against top-level comments, one network against another. Do not publish "87 percent positive" as a fact about what people think. The share is a property of your word list as much as of the comments.
Step 1: get the comments in and clean them
Put the export in a tab named Comments, by Copy for Sheets, a live =IMPORTDATA link or by importing the workbook. The comment text is column E and platform is column M, so helper columns start at N.
In N1 type clean. In N2:
=ARRAYFORMULA(IF(E2:E="", "", TRIM(LOWER(REGEXREPLACE(REGEXREPLACE(E2:E&"", "@[A-Za-z0-9._]+", " "), "[^\p{L}\p{N}\s]", " ")))))
The inner REGEXREPLACE removes @mentions, so a tagged friend's handle is never scored as a word. The outer one replaces everything that is not a letter, a digit or a space, which takes punctuation and emoji out of the text. LOWER and TRIM do the rest. \p{L} keeps letters of every alphabet. More cleaning options are in how to clean comments in a spreadsheet.
Step 2: build the lexicon
Add a tab named Lexicon. Column A holds a word or short phrase in lower case, column B holds its score.
| A: term | B: score |
|---|---|
| love | 1 |
| beautiful | 1 |
| thank you | 1 |
| need this | 1 |
| obsessed | 1 |
| disappointed | -1 |
| broken | -1 |
| never arrived | -1 |
| scam | -2 |
| refund | -1 |
Start with 40 to 60 terms. The best source is your own data: run the word frequency formula on the comments, read the top 200 words, and add the ones that carry a feeling in your niche. "Sick" is praise under a bike video and a complaint under a restaurant post. A general-purpose list cannot know that; yours can.
Give a stronger score (2 or -2) only to words you would act on, such as "scam" or "refund".
Step 3: score each comment
In O1 type word_score. In O2, filled down:
=SUMPRODUCT(ISNUMBER(SEARCH(" "&Lexicon!$A$2:$A$200&" ", " "&N2&" ")) * Lexicon!$B$2:$B$200)
For every term in the lexicon it asks "is this term in the cleaned comment?", and adds up the scores of those that are. The spaces glued to both sides make it match whole words: "over" will not match inside "love". Empty lexicon rows have a score of zero, so a range longer than your list does no harm.
The same formula works in Excel, which is useful if the file later moves there. To score only the comments that ask or complain about something specific, combine it with the flags from find questions and buying intent with REGEXMATCH.
A term is counted once per comment, however many times it appears. "love love love" scores 1, which keeps one excited person from outweighing three calm ones.
Step 4: score the emoji
Many comments are only emoji, and cleaning removed them. Score them from the original text. Add a tab Emoji with the emoji in column A and a score in B: 😍, ❤, 🔥, 👏 and 🙌 at 1; 😡, 👎, 🤮 and 💔 at -1. Leave out ambiguous ones such as 😂 and 💀, which mean "funny" far more often than anything negative.
In P1 type emoji_score. In P2, filled down:
=SUMPRODUCT(ISNUMBER(SEARCH(Emoji!$A$2:$A$40, E2&"")) * Emoji!$B$2:$B$40)
No spaces are added here, because emoji are usually typed with none between them.
Step 5: flag negations and label
"Not good" contains "good". Rather than pretend a formula can parse grammar, flag the comments where a negation word appears and read those by hand. In Q1 type check. In Q2:
=AND(O2<>0, REGEXMATCH(N2, "\b(not|no|never|dont|don t|doesnt|doesn t|isnt|isn t|wasnt|wasn t|cant|can t)\b"))
The odd spellings are deliberate: cleaning turned the apostrophe in "don't" into a space. Then the label, in R2:
=IFS(E2="", "", Q2, "check", O2+P2>=1, "positive", O2+P2<=-1, "negative", TRUE, "neutral")
Filter R to check, read those rows and type the right label over the formula. On a typical post that is a few dozen rows.
Step 6: summarise
| Number | Formula |
|---|---|
| Share positive | =COUNTIF(R2:R, "positive") / COUNTA(B2:B) |
| Share negative | =COUNTIF(R2:R, "negative") / COUNTA(B2:B) |
| Likes on negative comments | =SUMIF(R2:R, "negative", G2:G) |
| Negative share among replies only | =COUNTIFS(R2:R, "negative", H2:H, "yes") / COUNTIF(H2:H, "yes") |
The third number matters more than the second. Ten negative comments with no likes are ten people. One negative comment with 400 likes is a problem. For a per-day view, add is_reply or the day to a pivot table with the label under Columns: the pivot table guide has the layout.
Check it against your own reading
Before quoting any number, measure the method. Pick 100 rows at random (sort by a column of =RAND() pasted as values and take the first 100), hide column R, and type your own label in S: positive, negative or neutral. Then:
=SUMPRODUCT(--(R2:R101=S2:S101)) / 100
That is the agreement rate. Around 0.7 to 0.8 is usual for a first list on short comments. Read the rows where you disagree; each one suggests a word to add, a word to remove or a phrase to treat as a unit. Two rounds of that usually lift the rate more than any cleverer formula would.
State the method and the agreement rate wherever you report the result. The page on exporting comments for research and sentiment analysis lists what else belongs in a methods note, including how many comments were read out of how many the post shows.
Comments in other languages
A lexicon only works in its own language. Two options. Build a second list for your second language and add its score in another column, which is the accurate route for Spanish or Portuguese audiences. Or translate first, in a helper column:
=IF(E2="", "", GOOGLETRANSLATE(E2, "auto", "en"))
and clean and score the translation. GOOGLETRANSLATE is slow on thousands of rows and can stop responding for a while when called too often, so fill it down in batches of a few hundred and paste the results as values. Translation flattens slang, so expect a lower agreement rate.
If the comments came from several networks, compare the labels by the platform column: compare comments across networks in one sheet.
FAQ
How accurate is word-list sentiment?
On short social comments a tuned list usually agrees with a human reader on 70 to 80 percent of rows. Measure your own rate on a hand-labelled sample of 100 before reporting anything.
Can I do this in Excel?
Yes. The SUMPRODUCT scoring formula is the same. For the cleaning step, Microsoft 365 has REGEXREPLACE; in older versions, clean with LOWER, TRIM and SUBSTITUTE for the punctuation you see most.
Why are so many comments neutral?
Many comments are a tagged friend, a question or a single word that carries no feeling. A large neutral share is normal. Report the positive and negative shares next to it instead of forcing every comment into one of two groups.
Does sarcasm break it?
Yes. No word list reads sarcasm. The sample check tells you how often it matters on your audience.