You may have seen the tally mark symbol in the Excel file. Commonly it is used to quickly tally numbers visually in blocks of five. But, it is not only restricted to number five only we can also use blocks of 10, 20, or 50’s to quickly tally higher numbers.
In this article, I will show you how to display emoji number tally marks in Excel using the VBA function.
The process of displaying tally mark symbols in Excel using customized charts is a little difficult method for some users. On the other hand currently excel does not support Unicode tally mark symbols such as (U+1D378);
Imagine, what if we started using other symbols instead of bars? Yes, I have tried using the Emoji number symbol 5️⃣ instead of bars. And here is the result in the following screenshot,
Steps to create a tally mark in Excel using the VBA function
We will create an Excel VBA function that will be used in Excel just like other functions we use. Data reference from the number column will be passed in this function and it will return mark symbols.
Step 1: Create an Excel VBA module with TallyMarks() function
From Excel press Alt + F11 to open VBA Editor and then create a new module from the “Insert->Module” menu option in the Visual Basic Editor Application
Now, copy and paste the following code into the newly created module, and save it.
Function TallyMarks(Number As Integer) As String
Dim tally As String
tally = ""
While Number >= 5
tally = tally + "5ï¸âƒ£" '5️⃣ emoji code
Number = Number - 5
Wend
Select Case Number
Case 1
tally = tally + "1ï¸âƒ£" '1️⃣ emoji code
Case 2
tally = tally + "2ï¸âƒ£ " '2️⃣ emoji code
Case 3
tally = tally + "3ï¸âƒ£" '3️⃣ emoji code
Case 4
tally = tally + "4ï¸âƒ£" '4️⃣ emoji code
End Select
TallyMarks = tally
End Function
Step 2: Use the TallyMarks() function
No go to the Excel sheet and start using TallyMarks() function. Put reference cell address in the function to display the tally mark. Here is an example,
=TallyMarks(A2)
Now it will refer to data from Cell A2 and process it to display the number emojis tally mark.
Done.
Note: Macro or VBA code-enabled Excel file needs to be saved as “Micro-Enabled Workbook” with extension “.xlsm” otherwise you will lose VBA code and modules after closing the file.
The usefulness of Excel Tally Mark
Instead of using words or regular numbers, we can show any number visually. We do this by using tally marks, which represent the frequency or occurrence of different categories.
Tally marks are like a special counting system. We write them in groups of five lines. We can easily make charts with these tally marks using Excel or MS Word. There are two types of tally marks: the 5 marks and the 1 mark.
For the 5 marks, we draw 4 vertical lines, and then we add 1 diagonal line on top of those 4 lines. This group of 5 marks represents a certain number. If we have more than 5, we keep adding these groups of 5 marks.
For the 1 mark, it represents the leftover part after counting groups of 5. We stack and scale these 1 marks accordingly, each representing one unit. This way, tally marks make counting and displaying numbers much faster and simpler.
I have tried emojis which are supported very well. You can also use other emojis to replace blocks of five like ✊???? (raised fist) or ✅ ok symbol.
Further, you can customize it by changing its font or background colors.