How to Make Tally Marks in Excel (4 Easy Methods)

In numerous situations, you may need to make tally marks in your Excel sheets. Because tally marks can represent data in a visually pleasing way. But there is no built-in system in MS Excel to create tally marks. Here, we will take you through 4 easy and convenient methods to make tally marks in Excel.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


4 Methods to Make Tally Marks in Excel

A tally graph usually consists of four lines, with the fifth tally represented by a diagonal strikethrough line. This creates a pleasing visual grouping. Usually, we use tally marks to represent the number of times something happens. Although Microsoft Excel has a lot of built-in chart types, it doesn’t include a tally graph option. So we have to use alternative ways.

Here, we’ve got a dataset of Vote Count List including individual’s Name and their total Votes.

How to Make Tally Marks in Excel Dataset

We wanna show those vote counts in tally marks. For this, we’ll use a handful of methods including formulas and bar charts.


1. Using REPT Function

In our first method, we will use just the REPT function. Before that, we need to fix what our tally marks will look like. For four lines, we’ll use 4 vertical straight lines just above the backslash key on our keyboard, and for the substitute of the diagonal strikethrough line, we’ll use a hyphen like that ( ||||- ).

How to Make Tally Marks in Excel Using REPT Function

Now, follow our steps below.

Steps:

  • First, select cell E8. Paste down the formula below into the Formula Bar, and press ENTER.
=FLOOR.MATH(D8,5)/5

How to Make Tally Marks in Excel Using REPT Function

Using the FLOOR.MATH function, the value in cell D8 got rounded down to a number which is divisible by 5. Then divide it by 5, and we got the quotient that is visible in cell E8.

  • After that, select cell F8, type down the formula below, and press ENTER.
=MOD(D8,5)

Here, the MOD function returns the remainder in cell F8 after dividing the value of cell D8 by 5.

How to Make Tally Marks in Excel

  • Later, select cell G8, write down the formula below, and press ENTER.
=REPT($C$4,E8)&REPT($C$5,F8)

Here, we have frozen cells C4 and C5 to make our formula work properly.

How to Make Tally Marks in Excel Using REPT Function

Read More: How to Tally Votes in Excel (4 Suitable Methods)


2. Using a Combination of REPT, QUOTIENT, and MOD Functions

In another way, we can make tally marks in Excel. To do so, we’ll use a formula combining REPT, QUOTIENT, and MOD functions. Here’s our method.

Steps:

  • At the very beginning, select cell E5. Type down the formula below, and press ENTER.
=REPT("tttt ",QUOTIENT(D5,5))&REPT("I",MOD(D5,5))

How to Make Tally Marks in Excel Using REPT, MOD, QUOTIENT Function

Note: While typing tttt, make sure to put a blank space at the end of the last t. Otherwise, all t will stick together in E5:E11 cells.

  • Then, select E5:E11 cell range and change the font to Century Gothic.

How to Make Tally Marks in Excel Using REPT, MOD, QUOTIENT Function

  • Instantly, you can see your output in the correct format.

How to Make Tally Marks in Excel Using REPT, MOD, QUOTIENT Function

Read More: How to Tally a Column in Excel (with Quick Steps)


Similar Readings


3. Creating Tally Marks from Bar Chart

There is no default function or chart in Microsoft Excel to make tally marks. But we can do it with the help of a Bar Chart. To use this method, follow our work steps carefully.

Steps:

  • At first, fill up the Groups column. For this, select cell E8, type down the formula below, and press ENTER.
=FLOOR.MATH(D8,5)

How to Make Tally Marks in Excel Using Bar Chart

  • Secondly, select cell F8, write down the formula as follows, and press ENTER.
=MOD(D8,5)

How to Make Tally Marks in Excel Using Bar Chart

  • Then, select cell E8:F14 and go to the Insert tab, select Insert Column or Bar Chart > 2-D Stacked Bar.

How to Make Tally Marks in Excel Using Bar Chart

  • Instantly, a horizontal bar chart appears in front of us.

How to Make Tally Marks in Excel Using Bar Chart

  • Then, right-click on the y-axis and select Format Axis from the options.

How to Make Tally Marks in Excel Using Bar Chart

  • In the Format Axis menu, tick on the Categories in reverse order.

How to Make Tally Marks in Excel Using Bar Chart

Your y-axis will be vertically flipped after this action.

  • Now, double-click on any bar to open the Format Data Series. Decrease the Gap Width to 0% to remove the gap between two bars.

How to Make Tally Marks in Excel Using Bar Chart

  • Then, delete the unneeded visual elements like Chart Title, Legend, and Axis to make the graph area neater.

How to Make Tally Marks in Excel Using Bar Chart

  • Next, copy cell C4 in your clipboard and double-click on any blue bar in the graph to reopen the Format Data Series menu.

How to Make Tally Marks in Excel Using Bar Chart

  • Currently, from the Format Data Series option, select Fill and Line > Fill > Picture to texture fill > Clipboard > Stack and Scale with. And put down 5 in the box of Unit/Picture.

How to Make Tally Marks in Excel Using Bar Chart

  • For instance, our chart will look like that.

Using Bar Chart

  • Then, select cell C5, double-click on the orange-colored bar, and do the same as the previous steps.

Using Bar Chart

  • Finally, size down the chart suitably according to our table and place it correctly just beside our table.

Using Bar Chart

Read More: How to Make a Tally Chart in Excel (3 Easy Methods)


4. Applying VBA Code to Make Tally Marks in Excel

Applying the VBA code is always an amazing alternative. For doing this, follow as we have done below.

Steps:

  • Right-click on the Sheet name and select View Code.

How to Make Tally Marks in Excel Applying VBA Code

  • Instantly, the Microsoft Visual Basic for Applications window opens. From Toggle Folders, right-click on Sheet5 (VBA) > select Insert > Module.

Applying VBA Code

  • It opens a code module, where paste the below code down and clicks on the Run button or press F5.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim cells As Range
Dim i As Long, j As Long, n As Long
'Select the Range you want to apply this code to
Set cells = Me.Range("D5:D11")
Set cells = Intersect(cells, Target)
If Not cells Is Nothing Then
    Cancel = True
    Application.EnableEvents = False
    n = Len(cells.Value)
    j = n Mod 5
    If j = 4 Then
        cells.Value = cells.Value & " "
    Else
    'Select the tally sign you want for each doubleclick
        cells.Value = cells.Value & "/"
    End If
         cells.Font.Strikethrough = False
    For i = 1 To n Step 5
        If (j = 4) Or (i < (n - j)) Then
            cells.Characters(i, 4).Font.Strikethrough = True
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

Applying VBA Code

  • Now close the code module and return to the worksheet. You’ll be amazed to see that, double-clicking on any cell in column D will put a tally mark in the cell.

Applying VBA Code

  • Besides, you can count the tally and display it in number format. For this, select cell E5, put down the formula below, and press ENTER.
=LEN(D5)

Here, we’ve used the LEN function for counting the character length of cell D5.

Applying LEN Function

Read More: How to Make a Tally Sheet in Excel (3 Quick Methods)


Conclusion

In this article, we tried to show the different methods of making tally marks in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo