What is Tally Chart in Excel?
MS Excel has a lot of pre-installed charts and all these charts speak a lot about the data in an eye-catching manner. A tally chart is one of the features. This chart is one of the techniques for collecting valuable data through tally marks from the data set. Tally marks are numbers, occurrences or total frequencies measured for a category from the grouped observations. There is no such chart type called “tally chart” in excel but there are few ways to draw it.
It reflects a quantitative as well as qualitative information from the tally information in a visually appealing manner. The best way to create a tally chart is to use the column chart in excel. A tabular data can be converted into a tally chart with the help of a column chart where tally information is converted to columns in which each row has a distinct record.
- These are used to collect data faster and in a well-organized manner. Any number can be shown in the visualization manner and is faster than writing in words or numbers. The numbers represent frequencies or occurrences from each category which is then showed as tally marks. Tally marks are defined in the unary number system.
- They are written in a group of five lines. It can not only be plotted in Excel but also is simpler constructive in MS Word etc. The chart has two types of marks- 5 marks and 1 mark. 5 marks are represented in 4 vertical lines with 1 line diagonally on the 4 lines. 1 mark represents the remainder of the number which is basically stack and scale with the unit as 1.
Below are the tally chart representation of numbers from 1 to 7 to make it more clear:
- | = 1
- || = 2
- ||| = 3
- |||| = 4
- |||| = 5
- |||| | = 6
- |||| || = 7
Similarly, any number can be represented in this.
How to Create a Tally Chart in Excel? (Examples)
Let’s see how to create a tally chart from tabular data with examples.
- For creating a tally chart, we need tabular data. We have the data for the same.
The table above has sports as a category that has the count in the next column. The count represents the number of times the game has been played. We need a tally chart for the same.
- Now, we need to find 5 marks and 1 mark for the count. There are two formulas for the same: First one can be seen below.
- Here, B2 is the cell of the count and 5 is the divisor. The result can be seen below.
- Similarly, we can find the 5 marks for all the count values or simply can drag the cursor till the last cell to get the result automatically.
- 1 mark can be calculated with the formula as shown below.
- The result is 2 as shown below.
- Similarly, we can calculate for the rest of the counts.
- This requires a column chart first. So, we’ll select the 5 marks and 1 mark columns to plot a column chart. A column chart can be created by going to Insert > Column chart > 2-D chart > Stacked chart in excel as shown below.
- Now the column chart is ready. The blue color in the chart shows 5 marks and the orange color shows 1 mark. The chart is horizontal in shape as shown below.
- Now, we can select the Y-axis and go to Format Axis > Axis options and select “Categories in reverse order” as shown below.
The chart is now reversed with counts in descending order.
- In the next step, we’ll have to click on the data points and then change the gap width to 0% as shown below.
- Now, we can delete all the axis titles and axis numbers from the graph to make the chart more clear. Next, we’ll resize the chart and paste it close to the table as shown below.
- Each category count can be seen in the column chart above. The next step is to create a tally chart in excel. We’ll manually enter numbers from 1 to 6 and then draw vertical lines each below the numbers. 5 mark can be shown by a diagonal line on the 4 vertical lines and 1 mark can be seen as a single vertical line.
- Now, we can copy the 5 marks and paste it on the right of the first bar of the column chart and 1 mark just below it for convenience.
- Next, we’ll have to copy the 5 marks and click on the column chart. And select the options from the “Format data series” as shown below.
- After clicking “Picture or texture fill” and “Clipboard”, tally marks can be seen. When we set the stack and scale unit to 5, the 5 marks tally chart can be drawn finally as shown below.
- Similarly, we can draw the 1 mark tally chart with scale and stack unit being set as 1. Now, this is fully constructed.
27 is represented as four 5 marks and one 1 mark in the tally chart. All the counts are shown as a tally chart at a time in this manner. All the tally information is converted into a tally chart.
Things to Remember
- Tally information needed to be calculated first using the two formulas before creating a column chart.
- A column chart is drawn from the 5 marks and 1 mark values in the table.
- This relies on the column chart. Without a column chart, we can’t draw a tally chart.
- Knowledge of the tally chart is before constructing it in excel.
- This makes the data clear and easier to understand from a layman’s point of view. Even students use this at school in Mathematics subject.
This has been a guide to Tally Chart in Excel. Here we learn how to create a tally chart in excel and along with examples and downloadable excel template. You may learn more about excel from the following articles –