In this article, we will show you how to make a tally graph in Microsoft Excel. A tally graph is a graphical representation of the data in a table or spreadsheet. It can be used to show the distribution of data, the relationships between data points, or the changes over time in a dataset. To create a tally graph in Excel, follow these steps:

  1. Open Microsoft Excel and click on the File tab.
  2. Click on the Options button and then on the Graph tab.
  3. On the Graph tab, select either bar chart or pie chart type from the drop-down list.
  4. In the Data area, select either cells A1 through A9 or all cells in row 1 through row 9 (depending on which type of graph you want to create). The cells that contain your data will be filled with blue color while those that do not will be filled with black color. The bars on your tallygraph will be drawn at equal intervals along the x-axis and y-axis, while the colors of each cell will indicate how many values are present in that cell (blue for one value, black for zero values). The size of your bars can be changed by changing their widths and heights as well as their color (see below for more information). You can also change how quickly each bar updates by selecting one of three refresh rates from the drop-down list: once per second, once every five seconds, or never at all (see below for more information). Finally, you can adjust how big each bar is by selecting one of two sizes from the scale box: small or large (see below for more information). # Bar Width Height Color 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

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

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

Create the Tally system

A tally graph is normally presented as four lines followed by a diagonal strikethrough line for the fifth tally. This provides a nice visual grouping.

It is difficult to replicate this in Excel, so instead, we will group the values by using four pipe symbols and then a hyphen. The pipe symbol is the vertical line above the backslash character on the U.S. or U.K. keyboard.

So, each group of five will be shown as:

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

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

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

Total the Groups of Five

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

In cell D3, enter the following formula:

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

Total the Leftover Singles

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

In cell E3, enter the following formula:

Make the Tally Graph with a Formula

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

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

In cell F3, enter the following formula:

The REPT function repeats text a specified number of times. We used the function to repeat the tally characters the number of times specified by the groups and singles formulas. We 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 choose “Hide.”

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