How to create a tally chart in Microsoft Excel

A tally chart is a table of tally marks that statistically shows how often something happens. Microsoft Excel has many built-in chart types, but it doesn't have a tally chart option. Luckily, this can be done using a formula in Microsoft Excel. Besides that you should take a look Free video set to learn Excel at home for working people To increase your Excel knowledge

In this example, I will guide you to create a tally chart to display the number of votes each person received in the list below.

Create Tally system

A tally chart is usually presented as four vertical slash characters, followed by a slash for the fifth tally line:

||||-

And then a single strikethrough symbol for one occurrence would look like:

|

Enter these characters into drives D1 and E1 in Excel.

We will create a tally chart using formulas and reference these two cells to display the correct tally marks.

Calculate the number of groups

To calculate the sum of the group of years (corresponding to the five names), we will round the vote value down to the nearest multiple of five and then divide the result by five. Use the FLOOR.MATH function to round values.

In cell D3 enter the following formula:

=FLOOR.MATH(C3,5)/5

This formula rounds the value in C3 (23) down to the nearest multiple of 5 (20) and then divides that result by 5 to get 4.

Calculate single quantity

Now, we need to calculate what's left after summing the group of years. To do this, we can use the MOD function. This function will return the remainder after dividing two numbers.

In cell E3, enter the following formula:

=MOD(C3,5)

Create a Tally (tally) chart

Now we know the number of groups and the number of orders to display in the tally chart. We just need to combine them into a row of tally marks and we're done.

To do this, we will use the REPT function to repeat the occurrences of each character the necessary number of times and concatenate them together.

In cell F# enter the formula:

=REPT($D$1,D3)&REPT($E$1,E3)

The REPT function repeats text the specified number of times. We used this function to iterate over the specified tally characters in Groups and Singles. Finally, we use the “&” symbol to concatenate them together.

Hide the help column

To complete the tally chart, we'll hide helper columns D and E.

Select columns D and E, right click and then select “Hide”.

The completed tally chart gives you a visual representation of the number of votes each person received.

However, this method still has many shortcomings such as: the implementation process is quite complicated, data rounding leads to errors. I advise you not to apply this method in environments or jobs that require high precision such as accounting or finance. To overcome this drawback, I introduce to you a website that helps us draw tally charts conveniently and quickly. Meta Chart.