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

In many situations, you may need to make a tally sheet. With Microsoft Excel you can do such tasks in a bulk and within seconds. This article demonstrates how to make a tally sheet in excel in three different methods.

What Is Tally Sheet and Its Uses

A tally sheet is a very simple procedure to gather data on the occurrence of an event and count their frequency. It is a very handy tool to collect data. Tally sheets are used in a lot of cases. For example, you can use it to collect votes and later calculate them.

How to Make a Tally Sheet in Excel: 3 Quick Methods

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

1. Using LEN Function to Make a Tally Sheet

You can easily calculate the votes for each candidate by making a tally sheet in Excel and then calculating those using the LEN function. Now, follow the steps below to do so.

Steps:

First, add a column for Tally.
Next, 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 “ / ”.

Now, add a new column for Total Votes.
Then select cell E5 and insert the following formula.

=LEN(D5)

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

Finally, drag the Fill Handle for the remaining cells in the column.

2. Applying VBA Code to Make a Tally Sheet in Excel

Applying VBA code is a very convenient way to make a tally sheet in Excel. Now, I will show you the steps to make a tally sheet where you can just double-click on the cells to add a tally mark and then calculate them using the LEN function.

Steps:

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

At this point, 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

After that, press F5 to run the code and save the Excel file as a Macro Enabled Excel File.
Now, 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.

After you are finished adding the tally marks, add a column for Total Votes.
Next, select cell E5 and insert the following formula.

=LEN(D5)

Lastly, use the Fill Handle to automatically insert the formula for the rest of the cells in the column.

3. Utilizing FREQUENCY and REPT Functions to Make a Tally Sheet

Now, let us assume you have a dataset with a list of students and their Scores out of 120 on a test. At this point, you want to find out the occurrence of those scores in Ranges of 0-30, 31-60, 61-90, and 91-120 and then you want to make a tally sheet by adding Tally Marks. In this case, you can follow the steps below to do so in Excel.

Steps:

First, 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.
Next, add a column for Frequency.
Now, select cell H6 and insert the following formula.

=FREQUENCY(D5:D12,G6:G8)

Here, 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 respectively. This formula will automatically add a value to all the cells of the column.

At this point, add a new column for Tally Marks.
After that, select cell I6 and insert the following formula.

=REPT("/",H6)

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