Detect bot and spam comments in a spreadsheet
The networks remove a lot of spam before anyone sees it. What reaches the public comment section is what their filters missed: copy-paste promotions, "DM me" bait, accounts posting the same line under every video. A spreadsheet cannot look at a profile, but text and timing give most of these away.
Updated Nov 19, 2026 · 7 min read
What a spreadsheet can and cannot see
An export holds who commented, what they wrote, when, and how many likes it got. It does not hold follower counts, account age, profile pictures or anything else from the profile. So everything here is a signal about the comment, not a verdict about the account. Each one has innocent explanations, which is why the method ends in a review and never in an automatic delete.
Comments the network already filtered as spam are hidden from everyone and are not in the file at all. That is part of the gap described in why the row count and the displayed count differ.
Work on the Comments sheet. comment_id is B, username is C, comment is E, posted_at_utc is F, mentions is K and platform is M, so the new columns start at N.
Signal 1: the same text from different accounts
The strongest signal. Real people rarely write the same 60-character sentence independently. In N1 type norm, and in N2, filled down:
=LOWER(TRIM(E2))
Then count how many different accounts posted that exact text. In O1 type same_text_accounts. Google Sheets:
=IF(LEN(N2)<20, 0, COUNTUNIQUE(FILTER(C$2:C$5000, N$2:N$5000=N2)))
Excel (Microsoft 365):
=IF(LEN(N2)<20, 0, ROWS(UNIQUE(FILTER(C$2:C$5000, N$2:N$5000=N2))))
Any Excel, counting rows instead of accounts:
=IF(LEN(N2)<20, 0, SUMPRODUCT(--(N$2:N$5000=N2)))
The length test matters. Short comments repeat naturally: "love it", "need this", a row of hearts. Twenty characters is a reasonable floor. A value of 3 or more in column O is worth a look.
SUMPRODUCT and FILTER compare the text exactly. Avoid COUNTIF(N:N, N2) for this job: it treats * and ? inside a comment as wildcards and fails on texts longer than 255 characters.
The innocent explanation: giveaways. If the post told people to comment a keyword or a set phrase, hundreds of honest entries are identical. Skip this signal on such posts, or raise the length floor.
Signal 2: bursts from one account
Someone posting eight comments in three minutes is either very excited or automated. In P1 type burst, and in P2:
=COUNTIFS(C:C, C2, F:F, ">=" & (F2 - 5/1440), F:F, "<=" & (F2 + 5/1440))
It counts comments by the same account within five minutes either side of this one (a day has 1,440 minutes). The formula is the same in Excel and Google Sheets, and needs posted_at_utc as a real date, which it is in the Excel workbook. Test with =ISNUMBER(F2).
A value of 5 or more is unusual. For the totals per account without the time window, the workbook's Commenters sheet already lists everyone by number of comments, largest first.
The innocent explanation: giveaways that allow unlimited entries, and the account owner answering a backlog of questions in one sitting. Exclude the owner's handle from the review.
Signal 3: bait phrases and links
Promotional spam uses a small vocabulary. In Q1 type bait. Google Sheets:
=REGEXMATCH(LOWER(E2&""), "https?://|www\.|\.com\b|whatsapp|telegram|t\.me/|dm me|inbox me|check (out )?my (bio|profile|page)|link in (my )?bio|promote it on|send pic|crypto|forex|investment|giftcard|gift card")
Excel (Microsoft 365):
=REGEXTEST(E2, "https?://|www\.|\.com\b|whatsapp|telegram|t\.me/|dm me|inbox me|check (out )?my (bio|profile|page)|link in (my )?bio|promote it on|send pic|crypto|forex|investment|giftcard|gift card", 1)
Any Excel:
=SUMPRODUCT(--ISNUMBER(SEARCH({"http","www.",".com","whatsapp","telegram","dm me","inbox me","my bio","my profile","promote it on","send pic","crypto","forex","investment","gift card"}, E2)))>0
Edit the list for your niche. A finance creator will see "investment" in honest comments; a fashion brand will not. How the pattern syntax works is explained in find questions and buying intent with REGEXMATCH, which uses the same functions for the opposite purpose.
Signal 4: handles that end in a long run of digits
Mass-registered accounts often take a name plus a string of numbers. In R1 type digit_handle. Google Sheets:
=REGEXMATCH(C2&"", "\d{5,}$")
Excel and Google Sheets, without regex:
=SUMPRODUCT(--ISNUMBER(--MID(RIGHT(C2&"", 5), {1,2,3,4,5}, 1)))=5
Both are TRUE when the last five characters of the handle are all digits. This is the weakest of the five on its own. Plenty of real people have a birth year and a postcode in their handle, so never act on it alone.
Skip this signal on Facebook exports. There the username is the person's name plus four characters derived from the account, as explained in how to export Facebook Page post comments, and the ending says nothing about the account. On YouTube, a row whose username is a channel ID starting with UC just means the channel has no handle.
Signal 5: comments that are only mentions
A comment made of nothing except tagged accounts can be tag spam: an account mass-tagging strangers to pull them to the post. In S1 type only_mentions. Google Sheets:
=AND(K2<>"", TRIM(REGEXREPLACE(E2&"", "@[A-Za-z0-9._]+", ""))="")
Excel and Google Sheets, by counting: the number of @ signs equals the number of words.
=AND(K2<>"", LEN(E2)-LEN(SUBSTITUTE(E2, "@", "")) = LEN(TRIM(E2))-LEN(SUBSTITUTE(TRIM(E2), " ", ""))+1)
Combine it with a count of the tags, =LEN(K2)-LEN(SUBSTITUTE(K2, "@", "")), and look at rows with five or more.
The innocent explanation: "tag a friend" giveaways, where a comment of two or three mentions is exactly what was asked for. On those posts this is an entry, not spam: see how to turn comments into a giveaway entry list.
Add them up
In T1 type score, and in T2:
=IF(O2>=3, 2, 0) + IF(P2>=5, 1, 0) + IF(Q2, 2, 0) + IF(R2, 1, 0) + IF(S2, 1, 0)
Identical text and bait phrases weigh 2, the others 1. Sort by the score, largest first, and review everything at 2 or above. A worked example:
| username | comment | same text | burst | bait | score |
|---|---|---|---|---|---|
| hana.ceramics | The glaze on the blue one is perfect | 1 | 1 | FALSE | 0 |
| olu.designs | love it | 0 | 1 | FALSE | 0 |
| theo.bikes | Great page! Promote it on the account in my bio | 14 | 1 | TRUE | 4 |
| marta.reads | Is the link for the pattern on your site? | 1 | 1 | FALSE | 0 |
| priya.plans | Entered! Good luck everyone, fingers crossed | 3 | 6 | FALSE | 3 |
The third row is spam by any reading: the same sentence from 14 accounts, with a bait phrase. The fifth is a keen giveaway entrant, flagged by two signals that both have an innocent cause on a giveaway post. The fourth shows why the bait pattern uses "link in bio" and not the bare word "link". The score orders your reading; you still do the judging.
What to do with the flagged rows
- On the post. Hide, delete or report in the network's own app. A comment export never logs in, so it cannot change anything on the post. Use the
usernameand the comment text to find each one. - For analysis. Filter the score to 0 or 1 before counting words or tone. Twenty copies of a promotion would otherwise top the list in word frequency and top emoji and skew sentiment analysis.
- For giveaways. Decide your rule before looking at who it removes, apply it to everyone, and keep the removed rows on a separate tab with the reason. Repeat entrants are a different question from spam: find duplicate commenters covers it, and how to pick a giveaway winner in a spreadsheet covers the draw.
- Across posts. Stack the exports of several posts and count the posts each
user_idappears on with a high score. An account spamming every post is a stronger case than one odd comment. The stacking steps are in how to find your regular commenters across posts; this is the same table read from the other end.
Keep the flags out of anything you publish. A spreadsheet score is a reason to look at a comment, not evidence about a person.
FAQ
Can a spreadsheet prove an account is a bot?
No. It can show that a comment is identical to others, arrived in a burst or uses bait wording. Those are reasons to review it. Whether an account is automated is something only the network can know.
Why are spam comments I saw on the post missing from the file?
You may have seen them before the network hid them, or they are visible only to you as the account owner. Comments hidden from the public cannot be exported.
Do these formulas delete anything?
No. They add columns to your copy of the data. Removing a comment from the post is done in the network app.
How do I avoid flagging giveaway entries?
On a post that asked for a keyword or tags, drop the identical-text and mention-only signals, or raise their thresholds. Honest entries look alike by design.
Does this work on TikTok, YouTube and Facebook exports?
Yes, the columns are the same. Skip the digit-handle signal on Facebook, where the username is a name plus four characters, and ignore YouTube rows whose username is a channel ID.