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:

Tally chart in Excel 365

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:

    Data for Tally chart in Excel 365
  • Create or download pictures for the one and five pictogram items:
    Item for one unit of Tally chart in Excel 365   and   Item for five units of Tally chart in Excel 365

   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:

Column Charts in Excel 365

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

Stacked Bar in Excel 365

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:

Stacked Bar for tally in Excel 365

   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:
    Format data series in popup menu Excel 365
  • On the Chart Format tab, in the Current Selection group, click the Format Selection icon:
    Format Selection in Excel 365
  • 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:
    Fill Data Series by picture in Excel 365

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

    Insert pictures in Excel 365
  • 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:
    Stack and Scale with option for pictures in Excel 365

Make any other adjustments to get the look you desire:

Simple tally chart in Excel 365

See also how to draw a chart with chalk effect and customize the axis labels.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more