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.


Download Practice Workbook

You can download the practice workbook from the link below.


What Is Tally Sheet and Its Uses

A tally sheet is a very simple procedure to gather data of 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.


3 Methods to Make a Tally Sheet in Excel

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.

how to make a tally sheet in excel dataset

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 “ / ”.

Using LEN function

  • 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.

Using LEN function

  • Finally, drag the Fill Handle for the rest of the cells in the column.

Using LEN function

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


Similar Readings


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).

Using VBA code

  • 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

Using VBA code

  • 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.

Using VBA code

  • 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)

Using VBA code

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

Using VBA code


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

Now, let us assume that 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.

make a tally sheet using REPT and FREQUENCY Function

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.

make a tally sheet using REPT and FREQUENCY Function

  • 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.

make a tally sheet using REPT and FREQUENCY Function

  • Finally, drag the Fill Handle for the rest of the cells of the column.

make a tally sheet using REPT and FREQUENCY Function


Conclusion

Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Sowmik Chowdhury
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo