Tally in Excel (All You Need to Know)

In this article, we will discuss and demonstrate how to work with tallies in Excel.

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, quality control and error detection.

Download Practice Workbook

How to Create Tally Marks in Excel

Let’s go through 3 easy methods to create tally marks.

Method 1 – Using REPT Function

In this section, we will use the REPT function to create tally marks, along with the FLOOR.MATH and MOD functions.

Steps:

• Enter the following formula in cell D8:

• Enter the following formula in cell E8:

• Enter the following formula in cell F8:

This is the final formula to get the tally value.

Method 2 – Using a Combination of REPT, QUOTIENT, and MOD Functions

Steps:

• Enter the following formula in cell D5:

Method 3 – Using VBA Code

Steps:

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

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

To count Total Votes, we will use the LEN function.

• Enter the following formula in cell D5:

Read More: Make Tally Marks in Excel

How to Create a Tally Chart in Excel

Steps:

• Enter 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.
• From the From Axis dialog box, mark Categories in reverse order.

• Double-click on any of the bars.
• 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.
• Select Fill in the Format Data Series dialog box.
• Select Picture or texture fill.
• Click on Clipboard.
• Select Stack and Scale with.
• Enter 5 in Units/Pictures.

• Copy cell C5.
• Double-click on an Orange bar.
• Repeat the procedure in the previous steps.

The tally chart is complete.

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.

Method 1 – Using LEN Function

To count Total Votes, we will use the LEN function.

Steps:

• Enter the following formula in cell D5:

Method 2 – Using FREQUENCY and REPT Functions

We can also use the FREQUENCY and REPT functions to make a tally sheet in Excel.

Steps:

• Enter the following formula in cell G6:

• Enter the following formula in cell H6:

Read More: How to Make a Tally Sheet

Alternative: Count Values in Excel

In this section, we will calculate the number of cells that contain Votes, using the COUNT function.

Steps:

• Enter the following formula in cell C13:

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