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

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

`=FLOOR.MATH(C8,5)/5`

• Type the following formula in cell E8.

`=MOD(C9,5)`

• Then, type the following formula in cell F8. This is the final formula to get the tally value in the Excel cell.
`=REPT(\$C\$4,D8)&REPT(\$C\$5,E8)`

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

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

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

`=LEN(C5)`

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.

`=FLOOR.MATH(C8,5)`

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.
`=LEN(C5)`

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

`=FREQUENCY(C5:C12,F6:F8)`

• Type the following formula in cell H6.

`=REPT("/",G6)`

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.
`=COUNT(C5:C11)`

## Conclusion

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.

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

Advanced Excel Exercises with Solutions PDF