Tally in Excel (All You Need to Know)

In this article, we will talk about and show how to work with tally in Excel. Following some easy and effective techniques, we will do the task smoothly.

Creating tallies in Excel helps organize, analyze, and present data in a structured manner, leading to improved decision-making and understanding of information. It also helps in data analysis, data validation, reporting, budgeting and financial analysis, inventory management, and quality control and error detection.

Now, let’s dive into the following article to explore the topic in detail.

Download Practice Workbook

How to Create Tally Marks in Excel

Here, we will go through 3 easy methods to create tally marks.

1. Using REPT Function to Create Tally Marks

In this section, we will use the REPT function to create tally marks. We will also need FLOOR.MATH and MOD functions to do the task.

• Type the following formula in cell D8.

• Type the following formula in cell E8.

• Then, type the following formula in cell F8. This is the final formula to get the tally value in the Excel cell.

2. Using a Combination of REPT, QUOTIENT, and MOD Functions to Create Tally Marks

Here, we will use the REPT, QUOTIENT, and MOD functions to create tally marks.

• Type the following formula in cell D5.

3. Applying VBA Code to Make Tally Marks in Excel

In this section, we will use VBA code to make tally marks in Excel.

• Right-click on the sheet >> select View Code.

• Type the following VBA code in the Private Sub.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim n_cell As Range
Dim i_cell As Long, j_cell As Long, k_cell As Long
Set n_cell = Me.Range("C5:C11")
Set n_cell = Intersect(n_cell, Target)
If Not n_cell Is Nothing Then
Cancel = True
Application.EnableEvents = False
k_cell = Len(n_cell.Value)
j_cell = k_cell Mod 5
If j_cell = 4 Then
n_cell.Value = n_cell.Value & " "
Else
n_cell.Value = n_cell.Value & "/"
End If
n_cell.Font.Strikethrough = False
For i_cell = 1 To k_cell Step 5
If (j_cell = 4) Or (i_cell < (k_cell - j_cell)) Then
n_cell.Characters(i_cell, 4).Font.Strikethrough = True
End If
Next
Application.EnableEvents = True
End If
End Sub
VBA code to add tally in Excel

• Save the code >> return to your worksheet.
• When you double-click in a cell that is presented in the range C5:C11, a tally mark will be added.

Then, to count Total Votes, we will use the LEN function.

• Type the following formula in cell D5.

Read More: Make Tally Marks in Excel

How to Create a Tally Chart in Excel

In this section, we will create a tally chart.

• Type the following formula in cell D8.

Select the Groups and Singles column >> go to the Insert tab >> from Insert Column or Bar Chart >> select 2-D Stacked Bar.

• Right-click on the y-axis and select Format Axis.
• Then, from the From Axis dialog box, mark Categories in reverse order.

• Double-click on any of the bars.
• Then, from the Format Data Point dialog box, set Gap Width to 0%.

• Delete the unneeded visual elements like Chart Title, Legend, and Axis.
• Copy cell C4 by pressing CTRL+C.
• Double-click on a Blue
• Then, select Fill in the Format Data Series dialog box.
• After that, select Picture or texture fill >> click on Clipboard.
• Select Stack and Scale with >> type 5 in the Units/Pictures.

• Copy cell C5, double-click on an Orange bar >> do the same procedure as the previous steps.

Therefore, you can see the tally chart.

Read More: How to Make a Tally Chart

How to Make a Tally Sheet in Excel

We have 2 methods available to make a tally sheet.

1. Using LEN Function

Here, to count Total Votes, we will use the LEN function.

• Type the following formula in cell D5.

2. Utilizing FREQUENCY and REPT Functions

In this section, we will use the FREQUENCY and REPT functions to make a tally sheet in Excel.

• We will type the following formula in cell G6.

• Type the following formula in cell H6.

Read More: How to Make a Tally Sheet

Alternative: Count Values in Excel

In this section, we will find out the number of cells that contain Votes. We will use the COUNT function for this.

• Type the following formula in cell C13.

Conclusion

This article states about Tally in Excel. This article extensively represents the topic, and we believe you thoroughly understand the topic.

Here, we present how you can create a Tally mark, make a tally sheet, and a tally chart in Excel. Also, we describe an alternate way to count cells in Excel.

Thank you for reading this article. We hope you find this article beneficial. If you have any queries or suggestions, please let us know in the comment section.

Tally in Excel: Knowledge Hub

• Tally Words in Excel
• How to Tally a Column in Excel
• Tally Votes in Excel
• Export Tally Data
• How to Create a Tally Button

<< Go Back to Excel for Statistics | Learn Excel