How to create a tally chart in Microsoft Excel

A tally chart is a table of tally marks that statistics how often something happens. Microsoft Excel has a lot of built-in chart types, but it doesn’t have a tally chart option. Fortunately, this can be done using formulas in Microsoft Excel. Besides that, you should check it out Free video set for self-studying Excel at home for working people to increase 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 strikethrough characters, followed by a slash for the fifth tally line:

||||-

And then a single vertical tile icon for an occurrence would look like:

|

Enter these characters into drives D1 and E1 on Excel.

We will create a tally chart using these two cell references and formulas to show the correct check marks.

Calculate the number of groups

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

In cell D3 enter the following formula:

=FLOOR.MATH(C3,5)/5

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

Calculate the number of singles

Now, we need to calculate what is left after summing the group of five. 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 Tally chart (tally)

Now, we know the number of groups and the number of orders to show in the tally chart. We just need to combine them into a row of tally marks.

To do this, we will use the REPT function to iterate over the occurrences of each character as many times as needed and join them together.

In cell F# enter the formula:

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

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

Hide help column

To complete the tally chart, we will hide the 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 how many votes each person received.

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