Tally in Excel (All You Need to Know)

Get FREE Advanced Excel Exercises with Solutions!

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.


Using FLOOR and MATH Functions to make tally amrk in Excel

  • Type the following formula in cell E8.


Use of MOD Function to make tally amrk in Excel

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

Applying REPT Function for Tally in Excel

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.
=REPT("tttt ",QUOTIENT(C5,5))&REPT("I",MOD(C5,5))

Combining Functions to make tally amrk in Excel

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.

Selecting view Code from Context Menu

  • 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 & " "
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
Application.EnableEvents = True
End If
End Sub
VBA code to add tally in Excel

vba code to make tally amrk 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.

Tally in Excel cell

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

  • Type the following formula in cell D5.


Use of LEN Function

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.


Completing Group and Single columns

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

Selecting Bar Chart

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

Marking Categories in Reverse Order

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

Making Gap Width 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.

Selecting items in Format Data Series dialog box

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

Double-Clicking on Orange colored bars

Therefore, you can see the tally chart.

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

Applying LEN Function


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.


Applying FREQUENCY Function

  • Type the following formula in cell H6.


Use of REPT function

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.

Using COUNT Function


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

<< Go Back to Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF