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.
- 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.
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.
- 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 & " "
'Select the tally sign you want for each doubleclick
cells.Value = cells.Value & "/"
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
Application.EnableEvents = True
- 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.
- 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.
- 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.
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.
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.
Read More: How to Make Tally Marks in Excel
Download Practice Workbook
You can download the practice workbook from the link below.
Last but not least, I hope you found what you were looking for in this article. If you have any queries, please drop a comment below.
- How to Create a Tally Button in Excel
- How to Tally Words in Excel
- How to Export Tally Data in Excel
- How to Tally a Column in Excel
- How to Tally Votes in Excel
- How to Make a Tally Chart in Excel