Excel Logo on a gray background

A tally graph is a table of tally marks to present the frequency in which something occurred. Microsoft Excel has a large number of congenital-in nautical chart types available, but it does non have a tally graph option. Fortunately, this can be created using Excel formulas.

For this example, nosotros want to create a tally graph to visualize the votes received past each person on a  list.

Sample data for the tally graph

Create the Tally system

A tally graph is unremarkably presented as four lines followed by a diagonal strikethrough line for the fifth tally. This provides a dainty visual group.

Information technology is difficult to replicate this in Excel, so instead, we will group the values past using four pipage symbols and so a hyphen. The pipe symbol is the vertical line above the backslash graphic symbol on the U.S. or U.G. keyboard.

Then, each group of five volition be shown every bit:

||||-

And then a single pipe symbol for a unmarried occurrence (1) will appear as:

|

Blazon these symbols into cells D1 and E1 on the spreadsheet.

tally marks in a cell for formula referencing

We will create the tally graph using formulas and reference these two cells to brandish the correct tally marks.

Total the Groups of V

To total the groups of five, we will round the votes value down to the nearest multiple of five and then divide the result past five. We can use the function named FLOOR.MATH to circular the value.

In cell D3, enter the following formula:

=Floor.MATH(C3,five)/5

Total the groups of five

This rounds the value in C3 (23) down to the nearest multiple of 5 (20) so divides that event by 5, giving the answer 4.

Total the Leftover Singles

We now need to calculate what is left over subsequently the groups of v. For this, we can use the MOD role. This function returns the remainder after two numbers are divided.

In cell E3, enter the following formula:

=MOD(C3,five)

Calculate the remainder with MOD

Brand the Tally Graph with a Formula

We now know the number of groups of five and too the number of singles to display in the tally graph. We just demand to combine them into one row of tally marks.

To do this, we will use the REPT function to repeat the occurrences of each graphic symbol the required number of times, and concatenate them.

In cell F3, enter the following formula:

=REPT($D$1,D3)&REPT($East$one,E3)

Create a tally graph with REPT

The REPT function repeats text a specified number of times. Nosotros used the function to repeat the tally characters the number of times specified past the groups and singles formulas. Nosotros also used the ampersand (&) to concatenate them together.

Hide the Helper Columns

To finish the tally graph, we will hide the helper columns D and E.

Select columns D and E, right-click, and then cull "Hide."

Hide the helper columns

Our completed tally graph provides a nice visual presentation of the number of votes each person received.

Completed tally graph in Excel

The above article may contain affiliate links, which aid support How-To Geek.