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.

**Table of Contents**hide

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

### 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
for the candidate and type in a “*Tally*column**/**” .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 rest of the cells in the column.

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

**Similar Readings**

**Export Tally Data in Excel (with Quick Steps)****How to Create a Tally GST Invoice Format in Excel (with Easy Steps)****Tally Sales Invoice Format in Excel (Download Free Template)****How to Create a Tally VAT Invoice Format in Excel (with Easy Steps)****Tally Bill Format in Excel (Create with 7 Easy Steps)**

### 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 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

**How to Create Tally Debit Note Format in Excel (With Easy Steps)****Create a Tally Button in Excel (2 Suitable Examples)****How to Tally Words in Excel (4 Useful Methods)****Create Tally Salary Slip Format in Excel (With Easy Steps)****Make Tally Purchase Order Format in Excel (With Easy Steps)****Export All Ledgers from Tally in Excel****How to Tally a Balance Sheet in Excel**