Tally marks in Excel and Google Sheets: tested formulas for groups of five

The formula, and what it returns

One formula draws a count as tally marks in groups of five, and it works unchanged in Excel and Google Sheets. With the count in A2:

=TRIM(REPT("||||⁄ ",INT(A2/5))&REPT("|",MOD(A2,5)))

Copy it as it is: the fifth stroke of each gate is the fraction slash, U+2044, which leans further than a keyboard slash and copies with the formula. To type it by hand, build the character instead; UNICHAR exists in both programs, and Microsoft lists it for Excel 2016 and later:

=TRIM(REPT("||||"&UNICHAR(8260)&" ",INT(A2/5))&REPT("|",MOD(A2,5)))

A plain-keyboard version, =TRIM(REPT("||||/ ",INT(A2/5))&REPT("|",MOD(A2,5))), gives the same shape with an ordinary slash. All three returned the text in the table for every whole number from 0 to 120, and for 1,000.

The count stays in A2 as a number, so the marks are a picture of it and never the data. Sort, sum and chart column A; the tally column follows. If you only need a bar, =REPT("|",A2) draws one pipe per count, as the tally counter's FAQ suggests, but past about ten strokes nobody can read the number off it, which is the reason tallies are grouped in fives at all.

How each part works

INT counts the complete groups of five, MOD counts what is left, and REPT draws each part that many times. For 17, INT(17/5) is 3 and MOD(17,5) is 2, so the formula repeats the gate three times and the pipe twice.

Each function behaves as its documentation says, and two of those rules shape the formula:

• REPT adds nothing between repeats. Google's REPT page says it "does not insert spaces between repetitions", so the gate text carries its own trailing space, "||||⁄ ", to keep the groups apart.
• TRIM removes the space the last gate leaves behind. Microsoft's TRIM page says it "removes all spaces from text except for single spaces between words", and Google's TRIM page says the same of Sheets. The single spaces between gates stay; only the one after a final gate, as in 15, goes.
• A fraction is dropped, not rounded. Microsoft's REPT page says that if the number of repeats "is not an integer, it is truncated", which is why 7.6 shows as 7. Use ROUND(A2,0) in place of A2 if 7.6 should draw 8.
• MOD takes the sign of the divisor. Microsoft's MOD page defines it as n - d*INT(n/d), so with a divisor of 5 the remainder is always 0 to 4. That is why the loose strokes can never reach a fifth: the fifth always becomes a gate.

How the formulas were tested

Every formula in this guide was built into a real Excel workbook and recalculated by two independent spreadsheet engines, and the results were compared character by character with the expected marks. The workbook was written as an .xlsx file and recalculated on 4 November 2026 by HyperFormula 2.7.1, an open-source engine built for Excel and Google Sheets compatibility, and by the Python formulas library 1.3.4, an Excel calculation engine.

The inputs were every whole number from 0 to 120, then 1,000, 7.6, −3, a blank cell and the word "seven". For the whole numbers, seven formulas (the three gate versions, the ten-a-line version, two read-backs and the plain bar) produced 0 mismatches in both engines. 1,000 came back as 200 gates, 1,199 characters.

Where the two engines disagreed, this guide says so rather than picking one:

• A negative count. For −3, HyperFormula returned #VALUE! and the formulas library returned two strokes. Microsoft's REPT page asks for "a positive number". Neither result is a tally, so keep counts at zero or above.
• A wildcard and capital letters. =COUNTIF(A2:A31,"Walk*") found 11 answers in HyperFormula and 10 in the formulas library, which missed a lower-case "walk". Microsoft's COUNTIF page says criteria "aren't case sensitive", which makes 11 the documented answer.

What was not tested: the formulas were not run inside Microsoft Excel or Google Sheets themselves, and nothing here checks how a cell looks on screen. The function rules come from Microsoft's and Google's own pages, linked where they are used.

A tally chart from raw answers, with COUNTIF

When the answers are already typed into a column, count each category with COUNTIF and draw the marks from that count; never type the marks by hand. COUNTIF is COUNTIF(range, criteria) in Excel and COUNTIF(range, criterion) in Google Sheets, and neither is case sensitive.

A worked example: 30 people answered "How did you get here today?", and the answers are in A2:A31. The categories go in C2:C5 and the formulas in D and E:

D2  =COUNTIF($A$2:$A$31,C2)
E2  =TRIM(REPT("||||⁄ ",INT(D2/5))&REPT("|",MOD(D2,5)))
D6  =SUM(D2:D5)
D7  =COUNTA(A2:A31)

Fill D2 and E2 down to row 5. In the tested sheet the total came to 29 against 30 answers:

The missing answer was "Walk " with a space after it. COUNTIF matched "walk" in lower case, as documented, but not the trailing space, and Microsoft's COUNTIF page names leading and trailing spaces as a cause of wrong counts. =COUNTIF(A2:A31,"* ") counts the answers that end in a space; it returned 1 in both engines. Clean the answer, or count with C2&"*" as the criterion if no category's name starts another's, and the total reaches 30. Google's TRIM page says Sheets trims text typed into a cell, so in Sheets the stray space is more likely to arrive in pasted or imported answers.

That total row is the same check a paper tally chart needs, for the same reason: it is the only line that shows a lost answer. The tally marks guide explains it for charts drawn by hand, and the tally to percentage calculator turns the counts into percentages that add to exactly 100. Whether 30 answers are enough to say anything about a larger group is a separate question, answered in how many people to survey.

If you are counting live rather than from a typed list, a spreadsheet is the wrong place to do it. Count with the tally counter, one counter per category, then use its Export CSV button: the file has the counter's name in column A and its total in column B, so the tally formula goes in a new column with B2 in place of A2.

Ten marks a line, for bigger counts

For counts past about 20, start a new line after every second gate, so each full line is ten. That is how large tallies are kept readable on paper, as the tally marks guide shows for 50 and 100, and a cell can do the same with the line-feed character, CHAR(10):

=MID(REPT(CHAR(10)&"||||⁄ ||||⁄",INT(A2/10))&REPT(CHAR(10),SIGN(MOD(A2,10)))&TRIM(REPT("||||⁄ ",INT(MOD(A2,10)/5))&REPT("|",MOD(A2,5))),2,32767)

Each full ten becomes a line of two gates, SIGN adds one more line break only when something is left over, and MID drops the line break the formula starts with. For 23 the cell holds three lines: two full lines of two gates and a last line of three strokes. For 100 it holds exactly ten lines with no empty line at the end. Both engines returned the expected text for every count from 0 to 120 and for 1,000.

The line breaks show only when the cell wraps text. In Excel that is the Wrap Text button, described on Microsoft's wrap text page; in Google Sheets it is the cell's text wrapping setting. Microsoft's CHAR page notes that Excel for the web supports CHAR(10).

Reading typed tally marks back into numbers

If each mark is its own character, the count is the number of characters that are not spaces: =LEN(SUBSTITUTE(A2," ","")). A gate typed as four pipes and a slash is five characters, so it counts as five. To read cells made by the ten-a-line formula, remove the line breaks too: =LEN(SUBSTITUTE(SUBSTITUTE(A2," ",""),CHAR(10),"")).

The common advice, =LEN(A2), counts the spaces between groups as marks. Typed tallies come in other shapes as well, and each was tested:

Two rows need their own formula, because the problem is not the spaces:

• Strikethrough is formatting, and formulas cannot see it. Typing four pipes and striking them through is the usual way to fake a gate in Word or Google Docs, as the tally counter's typing tips describe. In a spreadsheet each struck group is still four characters, so it reads one short. If a sheet was kept strictly that way, add one for every run of four: =LEN(SUBSTITUTE(A2," ",""))+(LEN(A2)-LEN(SUBSTITUTE(A2,"||||","")))/4. It returned 12 and 10 for the two struck rows, but it counts a loose, unstruck four as five, so it is only safe on a sheet kept by that rule. =LEN(A2) landing on 12 in the first struck row is a coincidence: two spaces stand in for two missing diagonals, and the next row shows it failing.
• 卌 is one character for five. The character U+534C is often pasted as a gate because it looks like one; counting characters reads it as one mark. Replace it with five pipes before counting.

The safest sheet stores the number and draws the marks from it, as in the first section. Then nothing has to be read back.

Which characters display: a font check

Pipes and the fraction slash display in every common Windows font checked; the real Unicode tally characters were in none. On 4 November 2026 the character maps of all 157 font files in C:\Windows\Fonts on a Windows 11 Pro machine were read with fontTools, looking for each character the formulas might use.

A font missing a character does not always mean a box on screen, because Windows and the browser can borrow the glyph from another font, but it does mean the result depends on the machine. The real tally characters, UNICHAR(119671) and UNICHAR(119672), are the ones most likely to fail; the tally counter's symbol table explains why Unicode encoded only one and five and why almost no font joins them. Aptos was not installed on the test machine, so it was not checked.

Limits and errors

The formula breaks on text, on negative numbers and on very long results, and becomes unreadable long before it breaks.

• Text in the count cell gives #VALUE! in both engines. "12 votes" is text; split the number into its own column.
• Result length. Microsoft's REPT page says the result "cannot be longer than 32,767 characters", the same as the most a cell can hold under Excel's specifications; Google's REPT page puts the cap at 32,000. A gate and its space are six characters for five counts, so by that arithmetic the formula fails at about 27,300 in Excel and 26,700 in Sheets. The boundary itself was not tested.
• Readability fails first. A cell of 40 gates is 200 marks and nobody counts them by eye. Past a few dozen, keep the number and use a bar chart or the Excel data bars or Google Sheets SPARKLINE function instead of marks.

What this guide does not cover

This guide covers drawing tally marks from numbers in Excel and Google Sheets, building a tally chart from answers with COUNTIF, and reading typed tallies back. It does not cover pivot tables, charts, VBA macros or Apps Script, Excel for Mac's handling of CHAR(10), or LibreOffice and Numbers, none of which were tested. How to write and read tally marks on paper is in the tally marks guide.

Simple Online Counter is a free, browser-based set of counters, including a tally counter that keeps one total per category and exports the totals as a CSV file for a spreadsheet. The formulas here were tested in two spreadsheet engines on 4 November 2026; the function rules quoted are from Microsoft's and Google's own help pages, read the same day.