Alan Murray has functioned as an Excel trainer and also consultant because that twenty years. On many days, he deserve to be discovered teaching Excel in a class or seminar. Alan gets a buzz from helping civilization improve your productivity and working lives with Excel. Review more...
You are watching: How to do tally marks in excel
A tally graph is a table the tally clues to current the frequency in which something occurred. Microsoft Excel has a big number of integrated chart species available, but it does not have a tally graph option. Fortunately, this deserve to be produced using Excel formulas.
For this example, we want to produce a tally graph come visualize the votes got by each human being on a list.
Create the Tally system
A tally graph is normally presented as 4 lines complied with by a diagonal strikethrough line for the 5th tally. This gives a nice intuitive grouping.
It is complicated to replicate this in Excel, so instead, we will team the values by using four pipe symbols and also then a hyphen. The pipeline symbol is the upright line above the backslash character on the U.S. Or U.K. Keyboard.
So, each team of 5 will be shown as:
||||-And climate a single pipe symbol because that a single occurrence (1) will appear as:
Type these symbols into cells D1 and also E1 top top the spreadsheet.
We will produce the tally graph making use of formulas and reference these 2 cells to display the correct tally marks.
Total the teams of Five
To complete the groups of five, we will round the votes worth down come the nearest multiple of five and also then divide the result by five. We can use the function named FLOOR.MATH to ring the value.
In cabinet D3, enter the adhering to formula:
This rounds the worth in C3 (23) down to the nearest many of 5 (20) and also then divides that result by 5, providing the answer 4.
Total the Leftover Singles
We now need to calculate what is left end after the teams of five. Because that this, we deserve to use the mod function. This duty returns the remainder after two numbers room divided.
In cabinet E3, get in the adhering to formula:
Make the Tally Graph with a Formula
We now know the variety of groups of 5 and also the variety of singles to display screen in the tally graph. We just need to combine them right into one heat of tally marks.
To perform this, us will use the REPT function to repeat the incidents of each character the required variety of times, and also concatenate them.
In cabinet F3, enter the complying with formula:
The REPT function repeats message a specified variety of times. We offered the duty to repeat the tally characters the variety of times stated by the groups and singles formulas. We also used the ampersand (&) to concatenate them together.
Hide the Helper Columns
To complete the tally graph, we will certainly hide the helper columns D and E.
See more: How Many Zeroes In A Trillion ? Names Of Large Numbers Names Of Large Numbers
Select columns D and also E, right-click, and then choose “Hide.”
Our completed tally graph gives a nice visual presentation that the number of votes each human being received.
Alan MurrayAlan Murray has operated as an Excel trainer and consultant because that twenty years. On many days, he deserve to be discovered teaching Excel in a classroom or seminar. Alan it s okay a buzz from helping world improve your productivity and working lives with Excel. Read full Bio »