Wasim Akram
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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-chart available but tally charts don’t exist in the list. But it’s easy to make converting a tabular data and then adding 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.
3 Simple Methods to Make a Tally Chart in Excel
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.
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.
- 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.
- 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.
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.
- 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.
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.
- Select a 2-D Bar chart of your choice.
- This way we can create a 2D bar chart.
Step 4:
- Click the right button from the name list lying in the Y axis.
- From the options choose the “Format Axis”.
- Click the “Categories in reverse order” to rearrange the name list.
- Selecting the bar chart click the right button on the mouse.
- Choose the “Format Data Series”.
- Now decrease the “Gap Width” to “0%”.
- Remove all the unnecessary lines and names from the chart.
- Thus our final chart will look like the following screenshot.
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.
- 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.
- Change the font format to “Calibri Light” and size to “36”.
- Choose a line from the “Shapes” options from the “Insert” tab.
- Draw the line diagonally striking through the previous 4 lines.
- Edit the weight of the diagonal line.
- 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.
- Click the blue data chart and press the right button on the mouse.
- Choose the “Format Data Series”.
- 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.
- Choose the yellow part from the chart and click the right button of 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.
- Finally, after completing all the steps properly we have successfully made a tally chart in excel.
Read More: How to Tally Votes in Excel (4 Suitable Methods)
Similar Readings
- Export Tally Data in Excel (with Quick Steps)
- How to Make Tally Marks in Excel (4 Easy Methods)
- Tally Sales Invoice Format in Excel (Download Free Template)
- How to Create a Tally VAT Invoice Format in Excel (with Easy Steps)
- Tally Bill Format in Excel (Create with 7 Easy Steps)
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
- Hit the Enter button to continue.
- Pull the “fill handle” down.
- Our grouped numbers are ready now.
- To calculate the remaining values in a new column, select cell (F5).
- Apply the formula-
=MOD(D5,5)
- Press Enter and drag down the “fill handle”.
- This way we will get our dataset ready to represent the 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.
- 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.
Read More: How to Tally a Column in Excel (with Quick Steps)
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.
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.
- 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.
Read More: How to Make a Tally Sheet in Excel (3 Quick Methods)
Things to Remember
- You can also create a tally chart using the FREQUENCY function which represents different frequencies in a tally chart.
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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
Related Articles
- How to Create Tally Debit Note Format in Excel (With Easy Steps)
- Create a Tally Button in Excel (2 Suitable Examples)
- How to Tally Words in Excel (4 Useful Methods)
- Create Tally Salary Slip Format in Excel (With Easy Steps)
- How to Make Tally Purchase Order Format in Excel (With Easy Steps)
- Export All Ledgers from Tally in Excel
- How to Tally a Balance Sheet in Excel
