Count Frequency of Words: Online, Python, Excel, Shell

November 11, 2026 · 10 min read

Count Frequency of Words (and Letters) in Any Text

Any tool or script that can count frequency of words does four things: it splits text into words, normalizes them, tallies each one and sorts the result. This guide shows four ways to get that table (online, Python, Excel and the shell), the choices that make counts differ, and how to count letter frequency too.

How Do You Count the Frequency of Words?

Every word counter follows the same pipeline. You tokenize the text into words, normalize them (usually lowercase), tally how often each one appears, and sort by count. The tools differ only in how they make those calls.

Here's the sample text used throughout this post, saved as sample.txt:

The cat sat on the mat. The dog didn't sit on the mat, so the cat kept it. It's the cat's mat now, and the dog doesn't mind.

It holds 28 words. Lowercased, split on anything that isn't a letter, digit or apostrophe, and tallied, the top of the table looks like this:

7 the
3 mat
2 cat
2 on
2 dog

Every method below can produce this table once its settings match those rules. The interesting part is why other tools might not.

Choices That Change Your Word Frequency Counts

Two counters can read the same text and disagree, because each one silently picks its own rules. Here's what each choice does to the sample sentence.

ChoiceWhat happens to the sample
Case-sensitive countingthe 5 and The 2 become separate rows
Splitting on apostrophescat's becomes cat plus a stray s, so cat jumps to 3 and t shows up twice
Keeping apostrophesdidn't, it's and cat's each stay one word
Removing stop wordsthe, on, so and and disappear and mat tops the list
Minimum word length of 3on, so and it vanish even with stop words kept

Curly apostrophes cause a quieter problem. Text pasted from a word processor often has didn’t with a typographic apostrophe, which a pattern that only allows ' splits into didn and t. Hyphens raise a similar question: is well-known one word or two? Markdown source adds noise of its own, since link URLs and code spans can show up as words. Pick a rule and apply it every time, especially when you compare counts across a docs set that follows one Markdown style guide.

We prefer case-folded counts with stop words kept for a raw table, and filtered counts only when you're hunting for themes. If you need density percentages for SEO, that's a different job from building a raw frequency table.

Count Word Frequency Online With the Keyword Density Checker

For pasted text, the quickest route is our keyword density checker. Paste a draft or a Markdown document and it builds a single-word table plus 2-word and 3-word phrase tables, each with a count column. It runs in your browser, and every table exports as CSV.

Be clear about one limitation: it isn't a raw count of every word by default. It filters an English stop-word list, folds case and strips Markdown syntax, which drops fenced code blocks and link URLs. Its word table also hides words shorter than three letters.

Untick Exclude stop words and lower Min. word length to 1 when you want everything. The Rows setting (up to 100) caps how many rows each table shows and exports. The checker doesn't count letters, so use one of the methods below for that.

Count Word Frequency in Python With collections.Counter

Python's standard library does the whole job. collections.Counter tallies any iterable, and most_common(n) returns the top n items sorted by count.

import csv
import re
from collections import Counter

with open("sample.txt", encoding="utf-8") as f:
    text = f.read().lower().replace("’", "'")

words = [w.strip("'") for w in re.findall(r"[\w']+", text)]
counts = Counter(w for w in words if w)

for word, n in counts.most_common(5):
    print(n, word)

with open("word-counts.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow(["word", "count"])
    writer.writerows(counts.most_common())

Run on the sample, it prints 7 the, 3 mat, 2 cat, 2 on, 2 dog and writes every word to word-counts.csv. The replace call turns curly apostrophes into straight ones, so didn’t stays whole. In Python 3, \w matches Unicode letters in string patterns, so café counts as one word instead of caf.

To check a single word, look it up directly: counts["mat"] returns 3, and a missing word returns 0 instead of raising an error.

How Can I Count the Frequency of Words in Excel?

In Excel for Microsoft 365 or Excel 2024, paste the text into cell A1 and split it into one word per row with TEXTSPLIT. Leaving the column delimiter empty makes it spill down rows.

C1: =TEXTSPLIT(LOWER(A1),,{" ",".",",","?","!",";",":"},TRUE)
E1: =UNIQUE(C1#)
F1: =COUNTIF(C1#,E1#)

C1# refers to the whole spilled list, and TRUE drops the empty cells that consecutive delimiters leave behind. Column F then holds a count beside each unique word. In Microsoft 365, =GROUPBY(C1#,C1#,COUNTA,0,0,-2) builds the same table in one formula, sorted by count from highest to lowest.

Older versions don't have TEXTSPLIT, so get one word per row in column A first. Power Query's Split Column by Delimiter can split text into rows, and Find and Replace clears leftover punctuation. Copy the column to C, run Remove Duplicates from the Data tab on it, then enter =COUNTIF(A:A,C1) in D1 and fill it down. COUNTIF ignores case, so The and the count together.

Count Word Frequency From the Command Line

On macOS or Linux, a five-stage pipeline gives you the same table without installing anything:

tr -cs "[:alpha:]'" '\n' < sample.txt | tr '[:upper:]' '[:lower:]' | sort | uniq -c | sort -rn | head

The first tr turns every run of characters that isn't a letter or apostrophe into a single newline, so you get one word per line. The second lowercases. sort groups duplicates, uniq -c counts each group, and sort -rn puts the biggest counts first. On the sample, the top rows are 7 the, 3 mat, then on, dog and cat at 2 each (tie order can vary).

How can you count the number of words in a file? wc -w sample.txt prints the total (28 here). For one specific word, grep -o -i -w 'cat' sample.txt | wc -l prints 3, because -w treats the cat in cat's as a whole word. Avoid grep -P in scripts you share, since the grep that ships with macOS doesn't support it. For a total inside a document, use the Microsoft Word counter or the word counter for Google Docs.

Some tr builds work on bytes rather than characters, so accented letters can split apart. On macOS we tested with a UTF-8 locale and café stayed whole. If your output shows fragments like caf, switch to the Python method.

Letter Frequency Count: Count Letter and Character Frequency

A letter frequency count uses the same tally, applied to characters instead of words. In Python, Counter over a string counts characters directly, and an isalpha() filter keeps letters only:

from collections import Counter

with open("sample.txt", encoding="utf-8") as f:
    text = f.read().lower()

letters = Counter(c for c in text if c.isalpha())
total = sum(letters.values())

for letter, n in letters.most_common(5):
    print(letter, n, f"{n / total:.1%}")

On the sample's 88 letters it prints t 20 22.7%, e 9 10.2%, a 8 9.1%, h 7 8.0% and o 7 8.0%. Drop the isalpha() filter for a full character frequency count, including spaces and punctuation (the space wins with 27).

To count letter frequency in Excel, =LEN(LOWER(A1))-LEN(SUBSTITUTE(LOWER(A1),"e","")) returns how many times e appears in A1. In the shell, tr '[:upper:]' '[:lower:]' < sample.txt | grep -o '[a-z]' | sort | uniq -c | sort -rn lists every letter with its count.

Short samples are skewed: seven thes and a lot of cat, mat and sat push t far ahead. For a baseline, Peter Norvig's analysis of Google Books data counted about 3.56 trillion letters across 743.8 billion word mentions:

LetterShareLetterShare
E12.49%S6.51%
T9.28%R6.28%
A8.04%H5.05%
O7.64%L4.07%
I7.57%D3.82%
N7.23%C3.34%

The same analysis puts the at 7.14% of all words, of at 4.16% and and at 3.04%. Compare your letter shares against this table when you solve a Caesar cipher or a cryptogram: the most common letter in a long English ciphertext usually stands for E.

Turn a Frequency Table Into a Markdown Table

A CSV from the checker or from Python is easy to turn into a readable table for a README or report. Our guide to converting CSV to a Markdown table covers the conversion steps. Here's the sample's result as a finished table you can edit:

Word frequency: sample text

The cat sat on the mat. The dog didn't sit on the mat, so the cat kept it. It's the cat's mat now, and the dog doesn't mind.

Rank Word Count
1 the 7
2 mat 3
3 cat 2
4 dog 2
5 on 2
Letter Count
t 20
e 9
a 8
d, h, n, o 7 each
116 words371 characters18 lines
Markdown

Change a count or add a row and the preview updates as you type.

Word and Letter Frequency Questions

Whichever route you choose, the same rules decide the result: how you split words, fold case and treat apostrophes. Fix those first, then count frequency of words with the keyword density checker, a Python Counter, Excel or a shell pipeline. When your Markdown table is ready, paste it into the editor and check the preview before the numbers go into your report.