In our daily calculation and management work, we need to Tally various types of data column values in Excel. Tally marks enable us to grasp a quick insight into the data. If you are curious to know how you can Tally a column in Excel, this article may come in handy for you. In this article, we are going to show how you can Tally a column in Excel with elaborate explanations.
We will demonstrate how we produce a dynamic Tally for one column in Excel. In this process, we will use different functions to extract parameters that will help us to create the Tally.
Step 1: Preparing Dataset in Excel
In order to prevent any kind of unwanted result, and for better clarification, we need to prepare the dataset properly. The dataset we are going to use is given below.
- In the given dataset, we got the number of students who passed each subject.
- Tally marks abide by the unary number system, which means that the tallies numbers are presented with a single line for each value.
- We know, that in Tally, we usually separate the numbers into 5 equal chunks. If there is a remainder, then we present each of them with single dot lines.
- So basically, we will try to round each of the numbers closest to the multiple of 5 and if there is any remainder, we will show them right after the multiple of 5 values.
Read More: How to Export Tally Data in Excel
Step 2: Calculating 5 Separate Mark and Remainder Values
Right after we create the dataset we are going to round the passed students’ numbers closest to 5 using the FLOOR.MATH function. After we manage to do that, the remains (if there are any) will be calculated using the MOD function.
- In the beginning, select the cell D5 and enter the following formula:
=FLOOR.MATH(C5,5)Doing this will round the number in cell C5 to the multiple of 5.
- After that, drag the Fill Handle icon in the corner of cell D5 to cell D9.
- Dragging the Fill Handle will fill the range of cells D5:D9 with the values from the range of cells C5:C9 rounded close to the multiple of 5.
- Now select the cell E5 and enter the following formula:
=MOD(C5,5)- Doing this will return the remainder when the first argument (cell C5) is divided by the second argument (5). In other words, it will return the remainder of the first argument divided by the second argument.
- After that drag, the Fill Handle icon in the corner of cell E5 to cell E9.
- Dragging the Fill Handle will fill the range of cells E5:E9 with the remaining values of the range of cells C5:C9 when divided by 5.
Read More: How to Make a Tally Sheet in Excel
Step 3: Creating an Excel Stacked Column Chart
After we manage to distribute the passed student numbers into multiple of 5 in 5 Mark and remainders in the 1 Mark column, we will create a Stacked Bar Chart where we are going to set the Tally.
- To do this, we will first go to the Insert tab, then click on the Insert Column or Bar Charts.
- Then select Stacked Bar from the 2D bar group.
- Now a new Stacked Chart will be entered into the worksheet.
- The Stacked bar chart might catch some data automatically. We should better configure the values according to our needs.
- Right-click on the chart and click on the Select Data from the context menu.
- After that, a new dialog box named Select Data Source will open, in that box we have to enter the data ranges of our dataset.
- Click on Add.
- Then in another dialog box, click on the Series values range box and then select range D5:D9.
- Click OK after this.
- Then again click on Add, then in the dialogue box. Click on the Series values range box and then select range E5:E9.
- Click OK after this.
- After entering the data, return to the previous Select Data Source dialogue box and click on Edit on the right side.
- Select the range of cell B5:B9 and then click OK.
- After entering the data, return to the previous Select Data Source dialogue box and click on OK.
- Our Stacked Chart will look somewhat like this.
- We need to reverse the Vertical axis order, to do this double click on the Vertical axis, then click on the Format axis on the context menu.
- After that, tick on the Categories in the reverse order box.
- After that our Stacked Bar chart will look like the below image.
Read More: How to Make a Tally Chart in Excel
Step 4: Adding Tally Symbols
As we now have the Stacked bar chart, the Tally symbols now can be placed on it.
- We will create some Tally symbols in Excel. And then we will use those Tally symbols in the chart area.
- In order to create the symbols select cell R4 and press Shift+”\”.
- Doing this will enter the “|” sign in cell R4.
- Repeat the same process for the range of cells R5:R7.
- After that click on the Insert tab, and then from the Illustration group, click on the Shapes.
- Then click on the Lines from the Line section.
- Then there would be a draw crosshair on the Excel worksheet. Draw the line diagonally on the vertical line created just in the previous steps.
- After that, change the line wight line to 2.25.
- Then the Tally symbols for the 5 lines will look like this,
- Insert another single “|” in the worksheet.
- Now copy the whole symbol to the clipboard.
- Next, click on the blue section of the chart, and make sure all the section is selected. Then right-click on the mouse, and from the context menu, click on Format Data Series.
- Then there will be a side panel menu named Format Axis.
- In that menu, click on the Fill and Line option and then click on Picture or texture Fill on the Fill section.
- And then click on the Stack and Scale with, and then proceed to enter 5 in the below box.
- Right after that, you will notice that the Stacked Bar chart is now with the Tally boxes for the Mark 5 column.
- But our job is not complete yet, we need to tally the remaining orange data series on the chart plot area.
- Right-click on the orange portion of the chart.
- Then from the context menu, click on Format Data Series.
- Then there will be a side panel menu name Format Axis.
- In that menu, click on the Fill and Line option and then click on Picture or texture Fill on the Fill section.
- And then click on the Stack and Scale with, and then proceed to enter 1 in the below box.
- Doing this will fill the orange section (1 Mark) portion of the Stacked Chart with the single vertical “|” according to the values in the 1 Mark column.
- After some modifications, our Tally will look like the below image.
Read More: How to Create a Tally Button in Excel
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the question “how to Tally a column in Excel” is answered here in 4 separate steps with elaborate explanations.
For this problem, a workbook is available for download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section.
