Shahriar Abrar Rafid
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.
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 ( ||||- ).
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
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.
- 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.
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))
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.
- Instantly, you can see your output in the correct format.
Read More: How to Tally a Column in Excel (with Quick Steps)
Similar Readings
- Export Tally Data in Excel (with Quick Steps)
- How to Create a Tally GST Invoice Format in Excel (with Easy Steps)
- Tally Sales Invoice Format in Excel (Download Free Template)
- How to Create a Tally VAT Invoice Format in Excel (with Easy Steps)
- Tally Bill Format in Excel (Create with 7 Easy Steps)
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)
- Secondly, select cell F8, write down the formula as follows, and press ENTER.
=MOD(D8,5)
- Then, select cell E8:F14 and go to the Insert tab, select Insert Column or Bar Chart > 2-D Stacked Bar.
- Instantly, a horizontal bar chart appears in front of us.
- Then, right-click on the y-axis and select Format Axis from the options.
- In the Format Axis menu, tick on the Categories in reverse order.
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.
- Then, delete the unneeded visual elements like Chart Title, Legend, and Axis to make the graph area neater.
- Next, copy cell C4 in your clipboard and double-click on any blue bar in the graph to reopen the Format Data Series menu.
- 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.
- For instance, our chart will look like that.
- Then, select cell C5, double-click on the orange-colored bar, and do the same as the previous steps.
- Finally, size down the chart suitably according to our table and place it correctly just beside our table.
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.
- Instantly, the Microsoft Visual Basic for Applications window opens. From Toggle Folders, right-click on Sheet5 (VBA) > select Insert > Module.
- 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
- 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.
- 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.
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
- How to Create Tally Debit Note Format in Excel (With Easy Steps)
- Create a Tally Button in Excel (2 Suitable Examples)
- How to Tally Words in Excel (4 Useful Methods)
- Create Tally Salary Slip Format in Excel (With Easy Steps)
- How to Make Tally Purchase Order Format in Excel (With Easy Steps)
- Export All Ledgers from Tally in Excel
- How to Tally a Balance Sheet in Excel
