We often work with count data for different types of studies, including surveys, attendance or defect logs, etc. And in most of these cases, we cannot properly present our data only with the raw numbers. So, to deal with such situations, we need to use a tally mark that groups five counts as one.
Suppose you are tracking student participation in a debate competition. Each student earns points whenever they speak, and at the end of the day, you want to review who was most active. Now, if you show those data with tally marks, it will give a clearer view and make the presentation better. Like, you found a student who has 12 points and showing it as ||||/ ||||/ || instead of just stating 12, will make your data look clearer.
In this article, we will explain three different methods to add tally marks in Excel using REPT with QUOTIENT and MOD function, tally chart from a stacked bar chart, and using VBA code.
➤ First, click on cell D1 and write Tally.
➤ Then, select cell D2 and insert the following formula:
=REPT(“||||/ “, QUOTIENT(C2, 5)) & REPT(“|”, MOD(C2, 5))
Here, remember to add spaces after the ||||/ part in the formula before closing the inverted comma.
➤ Now, press Enter, and you will see the tally for cell C2 in cell D2.
➤ Finally, drag the cursor down to fill the cells for the rest of the students.
Add Tally Marks in a Cell Using Excel Formula
Combining the REPT, QUOTIENT, and MOD functions, we can easily add tally marks in Excel, and it is one of the most efficient and common methods. This approach breaks values into groups of five and leftover counts, then displays them as tally marks that update instantly whenever the data changes.
We will use the dataset below to explain how you can add tally marks to your dataset in Excel using the combination of these three functions.
This is a debate points dataset of Grade 6 and Grade 7 students, where the total debate points is 20.
Steps:
➤ First, click on cell D1 and write Tally.
➤ Then, select cell D2 and insert the following formula:
=REPT("||||/ ", QUOTIENT(C2, 5)) & REPT("|", MOD(C2, 5))
Here, remember to add spaces after the ||||/ part in the formula before closing the inverted comma.
➤ Now, press Enter, and you will see the tally for cell C2 in cell D2.
➤ Finally, drag the cursor down to fill the cells for the rest of the students.
Make Tally Charts in Excel from a Bar Chart
In some cases, we need to make more professional and presentable tally charts. To do it, we can simply use the bar chart to make this tally chart.
Steps:
➤ First, we will insert two new columns beside your Debate Points column. Click on cell D1 and write Group of 5. Then, click on cell E1 and write Remainder.
➤ Now, select cell D2, and insert the following formula:
=FLOOR.MATH(C2,5)
➤ Drag the cursor down to fill all the cells for the students.
➤ Now, click on cell E2 and insert the following formula:
=MOD(C2,5)
This will calculate the leftover tally marks that didn’t make a full group of five.
➤ Drag your cursor down again to fill the cells for all students.
➤ Next, select both the Group of 5 and Remainder columns together.
➤ Go to the Insert tab from the upper menu bar and select the Insert Column or Bar Chart icon. Then, choose Stacked Column from the 2-D column section.
➤ Now, you will see a chart appear. Remove all unnecessary elements, including gridlines, axis titles, and legends.
➤ Now, right-click on the axis and you will see a list of options. Choose Format Axis.
➤ You will see a new pane on the left side now. In it, under the Axis Options, select Categories in reverse order.
➤ Now, we will remove all the labels from the chart. Right-click on the vertical label and select Delete. Similarly, right-click on the horizontal label and select Delete.
➤ Now, right-click inside the chart (not on the bars) and select Format Plot Area.
➤ Now, you will see a new pane on the right side. There, under the Fill option, choose No Fill. Similarly, under the Border option, choose No Line.
➤ Then, select cell F1 and write Tally Chart, and adjust the size of the chart to match the area of the Tally Chart column.
➤ Double-click on any bar, and the Format Data Series pane will appear.
➤ Set the Gap Width to 0%to make the bars appear thicker and closer together.
➤ Now, we will create the tally symbols for a group of five and a single value.
➤ Select cell A12 and write Group Tally. Similarly, click on cell A13 and name it Single Tally.
➤ Then, select cell B12 and press Shift + I for four times. Make sure to change the font to Calibri Light and set the size to 36.
➤ Now, click on cell B1, and select Insert from the upper menu.
➤ Go to Illustrations and select Shapes from it. Choose a normal slanted line from the Lines option.
➤ Now, draw that line across the four lines we have created earlier in cell B11. Make sure it matches the tally for the group of five.
➤ Then, click on the line you have drawn across the four lines earlier. It will open a new tab called Shape Format on the upper menu bar.
➤ Select Shape Outline under the Shape Format and choose Black, Text 1 as the Theme Color.
➤ Set the Weight to 2¼ pt.
➤ Now, for the single Tally mark, click on cell B13 and press the Space Bar three times. Then, enter Shift + I once, and it will create the single tally Mark symbol.
➤ Then, select cell B12 and copy it. You can use the keyboard shortcut Ctrl + C to copy the cell.
➤ Now, right-click on any of the blue bars, i.e., the bar for the Group of five column in the chart.
➤ You will see a new pop-up with a few options. Choose Format Data Series from those options.
➤ A new pane will open on the right side of your dataset. Select the bucket icon (line and fill icon) in that pane.
➤ Now, under the Fill option, choose Picture or Texture fill. Then, click on Clipboard.
➤ Then, scroll down a bit, and you will see the option Stake and Scale with. Click on it and write 5 inside the box beside Units/Picture.
➤ Now, you will see that all the blue bars of our chat have been replaced by the tally for the group of 5.
➤ Next, we will replace the orange bar with the single tally by following the same procedure. Copy cell B13 and right-click on any orange bar.
➤ Then, select Format Data Series and choose Picture or Texture fill in the new pane on the right side. Now, click on Clipboard.
➤ Scroll down to the option Stake and Scale with. Click on it and write 1 inside the box beside Units/Picture.
➤ Finally, our tally chart is ready.
Add Tally Marks in Excel Using VBA Code
VBA code lets us automate the process in Excel and save a lot of our time. In fact, this method is the most suitable in case we need to make multiple tally charts for our dataset.
Steps:
➤ First, open your Excel worksheet and press Alt + F11 . It will open up the VBA editor.
➤ Then, right-click on the sheet name where your dataset is. It will open a list of options. Choose Insert and then Module to open the VBA script.
➤ Now, copy the following code and paste it into the script.
Sub AddTallyMarks()
Dim rng As Range
Dim cell As Range
Dim num As Long
Dim tally As String
Dim i As Long
' Set your range here
Set rng = Range("C2:C9")
' Loop through each cell
For Each cell In rng
If IsNumeric(cell.Value) And cell.Value > 0 Then
tally = ""
num = cell.Value
For i = 1 To num
If i Mod 5 = 0 Then
tally = tally & " / " ' Add space or slash after 5th
Else
tally = tally & "|"
End If
Next i
cell.Offset(0, 1).Value = tally ' Put tally in next column (D)
End If
Next cell
End SubNote:
In this code, remember to replace the range (“C2:C9”) with your own data range. Also, if you want to put the tally marks in a column other than D, replace D in Put tally in next column (D) part of the code.
➤ Now, click on the run icon or press F5 to run the code. Close the VBA window and return to your dataset.
➤ Finally, you will see the tally marks appear in column D.
Frequently Asked Questions
Does Excel Have a Built-in Tally Option?
Unfortunately, Excel does not have any built-in tally mark adding option till the latest update. However, you can create a simple tally chart using the Rept function, or for a quick chart, you can use the VBA code.
Will The Tallies Created WIth VBA Code Update If Data Changes?
No, once you run the VBA code and create the tallies, it will not update automatically if your data changes. In such a case, you can simply create a tally chart directly from a bar chart, and the tally chart will accommodate any changes in your dataset. Or else, you can also use the REPT function.
Can I Customize the Tally Chart?
Of course, you can. In both of the methods, you combine the REPT Function with QUOTIENT and MOD functions, and the tally chart you create from a bar chart, you can simply replace the / with – or any other symbol you prefer. Also, you can choose any color or size for the tally chart you want to make from a bar chart.
Wrapping Up
In this article, we have learned three different methods to add tally marks in Excel, including a combination of the REPT Function with QUOTIENT and MOD function, converting a stacked bar chart to a tally chart, and applying VBA. Give these methods a try and contact us using the comment box in case you have any inquiries or you face any issues. Also, you can share any feedback with us.
