How to Count Word Frequency in Excel (Ignoring "the" and "and")
A thousand open-text survey answers, support tickets or reviews, and one question: what are people actually saying? COUNTIF is no help — it counts whole cells, and on free text every cell is unique, so every count comes back as 1.
Last updated: October 2026
| Term | Type | Count | Rows | % of rows |
|---|---|---|---|---|
| delivery | Word | 3 | 3 | 100 |
| slow | Word | 3 | 2 | 66.7 |
| customer service | Phrase | 2 | 2 | 66.7 |
Why the stop-word list is the whole job
Without one, the answer to every question is "the, and, to, a, of". Excluding them by hand, every time, is the step that makes people give up on text analysis in Excel. The built-in list here is function words only — and deliberately keeps the negations: "no", "not", "never", "very" and "only" stay in, because in feedback they carry the meaning. A list that quietly removed them would turn "not helpful" into "helpful".
The 1-click way (Tellsheet)
Word & Phrase Frequency
Select the text column and run Word & Phrase Frequency. It writes a table of single words and two-word phrases, sorted by how often they appear, with filler words left out and your own extra words excludable. Each row shows the count, how many rows it appeared in, and what share of rows that is — the difference between one person saying "slow" three times and three people saying it once.
Two details worth knowing
- Phrases come from the original word order. Building them after removing filler words would pair words that were never adjacent — "service was slow" must not become the phrase "service slow".
- Count and Rows are different questions. Count is how often a term was said; Rows is how many people said it. For prioritising a complaint, Rows is usually the number that matters.
Frequently asked questions
Why doesn't COUNTIF work?
It counts whole cells, not the words inside them.
How do I exclude "the" and "and"?
They are excluded by default; add your own words too.
Does it do phrases?
Yes — two-word phrases, built from the original order.
Is "not" removed?
No. Negations carry the meaning and are kept.
Related Excel guides
What people are actually saying
Word & Phrase Frequency turns a column of comments into a ranked table — offline, with a preview first.
Get Tellsheet free See pricing