How to Create a Tally Chart in Excel – 3 Methods

 

The sample dataset showcases Candidate Names and their collected Votes.

Represent the collected votes in a tally graph.

Make a Tally Chart in Excel


Method 1 – Inserting a 2D Column to Create a Tally Chart

Step 1:

  • Insert 2 new columns. One for 5 marks and the other for 1 mark.

Insert a 2D Column to Make a Tally Chart

  • Choose a cell to enter the formula. Here,E5.
=FLOOR.MATH(D5,5)

The FLOOR.MATH function rounds a number down to a specific multiple given in the string.

Insert a 2D Column to Make a Tally Chart

  • Press Enter button to get the output.
  • Drag down the Fill Handle to see the result in the rest of the cells.

Values are multiple integers of 5.

Insert a 2D Column to Make a Tally Chart

Calculate the remaining values with the MOD function.

Step 2:

  • Choose a cell to enter the formula. Here, F5.
=MOD(D5,5)

The MOD function returns the remaining numeric values after division.

Insert a 2D Column to Make a Tally Chart

  • Press Enter.
  • Drag down the Fill Handle to see the result in the rest of the cells.

The remaining values are displayed.

Insert a 2D Column to Make a Tally Chart

 

Step 3:

  • Select the data in Names list, 5 mark, and 1 mark: (C5:C10), (E5:E10), and (F5:F10) holding Ctrl.
  • Go to “Column or Bar Chart” in “Insert”.

Insert a 2D Column to Make a Tally Chart

  • Select a 2-D Bar chart.

  • The 2D bar chart is created.

Insert a 2D Column to Make a Tally Chart

Step 4:

  • Right-click the name list in the Y axis.
  • Choose “Format Axis”.

  • Click “Categories in reverse order” to rearrange the name list.

Insert a 2D Column to Make a Tally Chart

  • Right-click to select the chart.
  • Choose “Format Data Series”.

Insert a 2D Column to Make a Tally Chart

  • Decrease the “Gap Width” to “0%”.

Insert a 2D Column to Make a Tally Chart

  • Remove all lines and names.

This is the output.

Insert a 2D Column to Make a Tally Chart

 

Step 5:

  • Enter “I” in side-by-side rows in different columns.

Insert a 2D Column to Make a Tally Chart

  • Select the columns and right-click.
  • Click “Column Width”.

  • Change the column width to “0.7”.
  • Click OK.

  • Change the font format and size:

Insert a 2D Column to Make a Tally Chart

  • Change the font to “Calibri Light” and size to “36”.

Insert a 2D Column to Make a Tally Chart

  • Go to the “Insert” tab and choose a line in “Shapes”.

Insert a 2D Column to Make a Tally Chart

  • Draw the line diagonally striking through the previous 4 lines.

  • Edit the weight of the diagonal line.

Insert a 2D Column to Make a Tally Chart

The tally lines are prepared.

Combine the tally lines with the chart to create the tally chart.

Step 6:

  • Select the tally lines and press Ctrl+C to copy.

Insert a 2D Column to Make a Tally Chart

  • Click the blue data chart and right-click.
  • Choose “Format Data Series”.

Insert a 2D Column to Make a Tally Chart

  • Go to Fill > Picture or texture fill > Clipboard > Stack and Scale with.
  • In “Units/Picture”, enter 5.

  • Select the single tally line and press Ctrl+C to copy.

Insert a 2D Column to Make a Tally Chart

  • Select the yellow part in the chart and right-click.
  • Select “Format Data Series”.

  • Go to Fill > Picture or texture fill > Clipboard > Stack and Scale with.
  • In “Units/Picture”, enter 1.

Insert a 2D Column to Make a Tally Chart

This is the output.

Insert a 2D Column to Make a Tally Chart

Read More: How to Tally Votes in Excel


Method 2 – Using the REPT Function to Create a Tally Chart

Step 1:

  • Choose a cell to enter the formula. Here, E5.
=FLOOR.MATH(D5,5)/5

Use REPT Function to Make a Tally Chart

  • Press Enter.
  • Drag down the Fill Handle to see the result in the rest of the cells.

  • This is the output.

Use REPT Function to Make a Tally Chart

  • To calculate the remaining values in a new column, select F5.
  • Enter the formula:
=MOD(D5,5)

Use REPT Function to Make a Tally Chart

  • Press Enter and drag down the Fill Handle to see the result in the rest of the cells.

The dataset is ready to represent the tally chart.

Use REPT Function to Make a Tally Chart

Step 2:

  • In two different cells, enter the 5 tally lines and 1 tally line.
  • Edit font and shape.

Use REPT Function to Make a Tally Chart

  • Choose a cell to display the tally chart. Here, G5.
  • Enter the formula:
=REPT($E$13,E5)&REPT($F$13,F5)

  • Press Enter
  • Drag down the Fill Handle to see the result in the rest of the cells.

The final tally chart is ready representing the votes of the candidates.

Use REPT Function to Make a Tally Chart

Read More: How to Tally a Column in Excel


Method 3 – Using the COUNTIF Function to Create a Tally Chart

The dataset showcases Candidate Names and their calculated Votes.

Find the duplicates and create a tally chart.

Perform COUNTIF Function to Make a Tally Chart in Excel

Steps:

  • Select G5 and enter the formula.

=COUNTIF($C$5:$C$25,F5)

  • Press Enter.
  • Drag down the Fill Handle to see the result in the rest of the cells.

Perform COUNTIF Function to Make a Tally Chart in Excel

The duplicated values are displayed.

  • Follow the steps described in the previous methods to create the tally chart.

Perform COUNTIF Function to Make a Tally Chart in Excel

Read More: How to Make a Tally Sheet in Excel


Things to Remember


Download Practice Workbook

Download the practice workbook.


Related Articles


<< Go Back to Tally in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo