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

Suppose you have a candidate list for a voting program, and you want to calculate the votes for each of these candidates. In this case, you can use Excel to calculate the tally efficiently.

Method 1 – Using the LEN Function to Make a Tally Sheet

Steps:

Add a column for Tally.
For each vote you calculate for a candidate, select the respective cell in the Tally column for the candidate and type in a “/“.For example, if you want to add a vote for Joana, select cell D5 and insert a “/“.

Add a new column for Total Votes.
Select cell E5 and insert the following formula.

=LEN(D5)

Here, cell D5 is the first cell of the column Total Votes.

Drag the Fill Handle for the remaining cells in the column.

Method 2 – Applying VBA Code to Make a Tally Sheet in Excel

Steps:

Press ALT + F11 to open the Microsoft VBA Window.
Double-Click on Sheet 3 (or the sheet you are working on).

Copy the following code and paste it into the blank space:

This code will help you to add a tally mark for each double click in the selected range.

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

Press F5 to run the code and save the Excel file as a Macro Enabled Excel File.
You can add a tally mark for each candidate by double-clicking on the cells on the Tally. For example, if you want to add a vote for Joana, select cell D5 and double-click on it.

Add a column for Total Votes.
Select cell E5 and insert the following formula:

=LEN(D5)

Use the Fill Handle to automatically insert the formula for the rest of the cells in the column.

Method 3 – Utilizing FREQUENCY and REPT Functions to Make a Tally Sheet

Consider e a dataset with a list of students and their Scores out of 120 on a test. Let’s find out the occurrence of those scores in Ranges of 0-30, 31-60, 61-90, and 91-120 and then make a tally sheet by adding Tally Marks.

Steps:

Add a column for Bin. In the Bin column, you will add the final value for each range. For example, for range 0-30 you will add 30 in the Bin column.
Add a column for Frequency.
Select cell H6 and insert the following formula:

=FREQUENCY(D5:D12,G6:G8)

H6 is the first cell of the column Frequency. In this case, the FREQUENCY function is used. The arguments of this function are data_array and bins_array. This formula will automatically add a value to all the cells of the column.

Add a new column for Tally Marks.
Select cell I6 and insert the following formula:

=REPT("/",H6)

Cell I6 is the first cell of the column Tally Marks. Also, in this case, the REPT function is used. The arguments of this function are text and number_times, respectively.

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

Method 1 – Using the LEN Function to Make a Tally Sheet

Method 2 – Applying VBA Code to Make a Tally Sheet in Excel

Method 3 – Utilizing FREQUENCY and REPT Functions to Make a Tally Sheet

Related Articles

Leave a Reply Cancel reply