How to Make a Tally Chart in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

For working advantages in Microsoft Excel, we use different types of charts. Of them, the tally chart is a valuable one to mark numbers or frequencies. In Excel, you can easily create a tally chart of your own. In this article, I am going to share with you how to make a tally chart in Excel.


Introduction to Tally Chart in Excel

A tally chart is a graph in which using the unary number system collected data is visualized. Excel has a lot of built-in charts available but tally charts don’t exist in the list. But it’s easy to convert tabular data and then add it to the table. Most of the time a tally graph is presented with four lines and for the fifth tally a diagonal strikethrough line between those four lines. Sometimes a hyphen is used instead of the diagonal line.


How to Make a Tally Chart in Excel: 3 Simple Methods

In the following article, I am sharing 3 quick methods to make a tally chart in Excel.

Suppose we have a dataset of some Candidate Names and their collected Votes from an election event. Now we are going to reflect their collected votes by creating a tally graph.

Make a Tally Chart in Excel


1. Insert 2D Column to Make a Tally Chart

The simplest way to make a tally chart is by inserting a 2D column in Excel. Follow the below steps-

Step 1:

  • Firstly, we will make 2 new columns. One is for 5 marks and another one is for 1 mark.

Insert a 2D Column to Make a Tally Chart

  • Choose a cell to apply the formula. Here I have selected cell (E5).
  • Put the formula down in the cell-
=FLOOR.MATH(D5,5)

Where,

  • The FLOOR.MATH function rounds a number down to a specific multiple given in the string.

Insert a 2D Column to Make a Tally Chart

  • Hit the Enter button to get the output in the cell.
  • Pull the “fill handle” down to fill the cells.

  • Here you will see we have successfully got our values calculated with specific multiple integers 5.

Insert a 2D Column to Make a Tally Chart

Now we will calculate the remaining values with the MOD function.

Step 2:

  • Choose a cell. Here I have chosen cell (F5).
  • Apply the formula-
=MOD(D5,5)

Where,

  • The MOD function returns the remaining numeric values after division.

Insert a 2D Column to Make a Tally Chart

  • Hit the Enter button to continue.
  • Drag down the “fill handle” to fill.

  • Thus we will get the remaining values by using a simple formula.

Insert a 2D Column to Make a Tally Chart

We have completed our dataset. It’s time to draw the chart with the help of our dataset.

Step 3:

  • Select Names list, 5 mark, and 1 mark data to put the values in the chart. Here I have selected cells (C5:C10), (E5:E10), and (F5:F10) holding the Ctrl button.
  • Go to the “Column or Bar Chart” from the “Insert” option.

Insert a 2D Column to Make a Tally Chart

  • Select a 2-D Bar chart of your choice.

  • This way we can create a 2D bar chart.

Insert a 2D Column to Make a Tally Chart

Step 4:

  • Click the right button from the name list lying in the Y axis.
  • From the options choose “Format Axis”.

  • Click the “Categories in reverse order” to rearrange the name list.

Insert a 2D Column to Make a Tally Chart

  • Selecting the bar chart click the right button on the mouse.
  • Choose the “Format Data Series”.

Insert a 2D Column to Make a Tally Chart

  • Now decrease the “Gap Width” to “0%”.

Insert a 2D Column to Make a Tally Chart

  • Remove all the unnecessary lines and names from the chart.
  • Thus our final chart will look like the following screenshot.

Insert a 2D Column to Make a Tally Chart

Now we will make the tally chart in different columns and add them to our previous chart.

Step 5:

  • Type “I” in side-by-side rows to different columns.

Insert a 2D Column to Make a Tally Chart

  • Selecting the columns click the right button on the mouse to get options.
  • Click the “Column Width”.

  • Change the column width to “0.7”.
  • Press OK to continue.

  • After that, we will change the font format and size selecting those.

Insert a 2D Column to Make a Tally Chart

  • Change the font format to “Calibri Light” and size to “36”.

Insert a 2D Column to Make a Tally Chart

  • Choose a line from the “Shapes” options from the “Insert” tab.

Insert a 2D Column to Make a Tally Chart

  • Draw the line diagonally striking through the previous 4 lines.

  • Edit the weight of the diagonal line.

Insert a 2D Column to Make a Tally Chart

  • This way your tally lines will be prepared.

It’s time for the final part to combine the tally lines with the chart to create the tally chart.

Step 6:

  • Selecting the tally lines press Ctrl+C to copy.

Insert a 2D Column to Make a Tally Chart

  • Click the blue data chart and press the right button on the mouse.
  • Choose the “Format Data Series”.

Insert a 2D Column to Make a Tally Chart

  • Go to Fill > Picture or texture fill > Clipboard > Stack and Scale with.
  • In the “Units/Picture” section put 5.

  • Now let’s attach the single tally line.
  • Select the single tally line and press Ctrl+C to copy.

Insert a 2D Column to Make a Tally Chart

  • Choose the yellow part from the chart and click the right button on the mouse.
  • From the options select “Format Data Series”.

  • Now go to Fill > Picture or texture fill > Clipboard > Stack and Scale with.
  • This time in the “Units/Picture” section put 1.

Insert a 2D Column to Make a Tally Chart

  • Finally, after completing all the steps properly we have successfully made a tally chart in Excel.

Insert a 2D Column to Make a Tally Chart

Read More: How to Tally Votes in Excel


2. Use REPT Function to Make a Tally Chart

The REPT function in Excel repeats characters a specific number of times. Using the REPT function you can also make a tally chart. I have shared the steps below. Please follow the steps-

Step 1:

  • Firstly we will categorize the groups and single just like the previous method.
  • To do that choose a cell to write the formula down. Here I have selected cell (E5).
  • Apply the formula-
=FLOOR.MATH(D5,5)/5

Use REPT Function to Make a Tally Chart

  • Hit the Enter button to continue.
  • Pull the “fill handle” down.

  • Our grouped numbers are ready now.

Use REPT Function to Make a Tally Chart

  • To calculate the remaining values in a new column, select cell (F5).
  • Apply the formula-
=MOD(D5,5)

Use REPT Function to Make a Tally Chart

  • Press Enter and drag down the “fill handle”.

  • This way we will get our dataset ready to represent the tally chart.

Use REPT Function to Make a Tally Chart

Step 2:

  • In two different cells type the 5 tally lines and 1 tally line and edit the font and shape according to your choice.

Use REPT Function to Make a Tally Chart

  • Choose a cell to represent the tally chart. Here I have selected cell (G5).
  • Write the formula down-
=REPT($E$13,E5)&REPT($F$13,F5)

  • Hit the Enter
  • Drag down the “fill handle” to fill all the cells.

  • Thus our final tally chart is ready representing the votes of the candidates.

Use REPT Function to Make a Tally Chart

Read More: How to Tally a Column in Excel


3. Perform COUNTIF Function to Make a Tally Chart

In this method, I have explained another easy method to make a tally chart in Excel. The COUNTIF function in Excel counts the duplicate values in a given string. Performing the COUNTIF function you can create a tally chart.

Suppose we have a dataset of some Candidate Names and their calculated Votes from different places. With the help of the COUNTIF function first, we will find the duplicates and then make a tally chart from it.

Perform COUNTIF Function to Make a Tally Chart in Excel

Steps:

  • Choose a cell (G5) to apply the formula.
  • Put the formula down-
=COUNTIF($C$5:$C$25,F5)

  • Hit the Enter button and drag it down to fill.

Perform COUNTIF Function to Make a Tally Chart in Excel

  • Here we have our duplicated values ready for calculation.

  • Now go follow the steps from the previous methods for successful making of the tally chart.

Perform COUNTIF Function to Make a Tally Chart in Excel

Read More: How to Make a Tally Sheet in Excel


Things to Remember

  • You can also create a tally chart using the FREQUENCY function which represents different frequencies in a tally chart.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have tried to cover all the simple methods to make a tally chart in Excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. Stay tuned and keep learning.


Related Articles


<< Go Back to Tally in Excel | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo