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

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

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

• Drag the Fill Handle for the rest of the cells of the column.

Read More: How to Make Tally Marks in ExcelÂ

## Related Articles

<< Go Back to Tally in Excel | Excel for StatisticsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sowmik Chowdhury

Sowmik Chowdhuri, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a crucial Excel & VBA Content Developer at ExcelDemy. His profound passion for research and innovation seamlessly aligns with his unwavering dedication to Excel. In this role, Sowmik not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, highlighting his steadfast commitment to consistently deliver content of exceptional quality and value. Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF