# 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 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. ### 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 “ / ”. • 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 rest of the 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. • 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 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. 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. • Finally, drag the Fill Handle for the rest of the cells of the column. ## 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

Hello! Welcome to my Profile. I , Sowmik Chowdhury, graduated from the Bangladesh University of Engineering and Technology from the Department of Naval Architecture and Marine Engineering. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this..

We will be happy to hear your thoughts  5 Excel Hacks You Never Knew  