Number Emoji Instead of Tally Mark Symbol in Excel

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 tally mark five (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,

tally mark
Number Emojis To Display Tally Mark In Excel

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
 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,

tally mark function
Use of TallyMark() function with Cell Reference

Now it will refer to data from Cell A2 and process it to display the number emojis tally mark.


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.

Leave a Comment

Related Posts