# Tally chart in Excel

Excel 365
A tally chart is a quick and easy way to record data by filling the chart with vertical bars each time a piece of information is observed. In addition, vertical bars (or tally marks) are immediately divided into groups of five (rarely groups of seven or ten).

Such a chart or diagram is convenient because you don't need to correct the data on counting or tallying ongoing results - it is enough to supplement the data with new information.

In Excel, you can create such a chart to present your data more impressively:

To create a tally chart like the one above, do the following:

1.   Prepare the data.

• Add two additional columns with the following formulas:
• The first additional column (named Largest of 5) should contain the largest multiple of five less or equal to the original number:
• = ROUNDDOWN (<data cell> / 5, 0) * 5,

The ROUNDDOWN () function rounds a number down: ROUNDDOWN (number, num_digits), where num_digits is the number of digits to which you want to round the number.

See more about rounding in Excel.

• = <data cell> - <mod of 5>

The <mod of 5> is a value from the second additional column.

• The second column (named MOD of 5) should contain the remainder of the original number divided by five - the remainder of modulo of five:

= MOD (<data cell>, 5)

The MOD () function returns the remainder after the number is divided by divisor: MOD (number, divisor).

For example, the favorite colors of children in year 5:

• Create or download pictures for the one and five pictogram items:
and

2.   Select the data for a new chart.

For this example, cells B3:B8, D3:E8.

3.   On the Insert tab, in the Charts group, click the Insert Bar or Column Chart button:

From the Insert Bar or Column Chart dropdown list, select Stacked Bar:

See more about chart types for pictograms.

4.   Customize the chart as you prefer.

E.g., reorganize the vertical labels, remove chart title and legend:

5.   Select the data series, then do one of the following:

• Right-click on the data series and choose Format Data Series... in the popup menu:
• On the Chart Format tab, in the Current Selection group, click the Format Selection icon:
• Double-click on the selection.

6.   On the Format Data Series pane, on the Series Options tab, in the Fill & Line group, in the Fill section:

• Choose the Picture or texture fill option,
• Under Picture source, click the Insert... button:

In the Insert Pictures dialog box, choose the picture location, select the picture file, and then click Open or Insert:

• Select the Stack and Scale with option adjusts the scale of the stacked pictures using the Units/Picture field:
• Type or select 5 for the first data series,
• Type or select 1 for the second data series:

Make any other adjustments to get the look you desire:

## Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.