How to Make a Tally Chart in Excel

A tally chart is a simple yet powerful way to visually count and track frequencies in your data. It provides a quick, at-a-glance understanding rather than having to work with raw numbers. There are various reasons you might want to create one.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Do you want to create a tally chart in Excel? You've come to the right place. This guide will walk you through a few popular methods for setting up tally charts in Excel, along with tips and tricks to impress your coworkers.

Prepare Your Raw Data

First, you need a list of raw data. A single column is ideal, where each cell contains one item you want to count. This could be answers to a survey (e.g., "Agree" and "Disagree"), t-shirt sales (e.g., "Small," "Medium," and "Large"), or any other data. In this example, we'll use a dataset of survey responses to the question, "What is your favorite fruit?" Here is the dataset we'll work with:

For example, in this data, you have three fruits as answers: Banana, Apple, and Orange.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Creating a Basic Tally Chart

Let's start by setting up your worksheet as a two-column chart. Add the unique names of the fruits you want to find the frequency of as rows under the column titled 'Fruits.' In our dataset above, the data is:

• Banana
• Apple
• Orange

Then, in cell 'F1', name it "Counts." This column will contain the final counts of the tallies. Save your Excel worksheet before proceeding to the next step of counting the fruits.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Count with COUNTIF

Now, we'll use the COUNTIF function to count how many times each fruit appears.

• In cell D2, enter the following formula: `=COUNTIF(B:B, B2)`

This formula does two things:

• Looks at the range containing your raw data (the survey results).
• Counts the occurrences of each fruit name in column F ("Apple," for instance).

Create the Tally Using the REPT Function

Next, you'll use the REPT function to repeat a given character a specific number of times. This forms your tally marks. In column E, enter the formula:

• `=REPT("|", COUNTIF(B:B, B2))`

This formula repeats the "|" character according to the count of each fruit in your data.

Final Tally Chart Design

Once the tally chart is created, the next step is to design it to make the chart easy to read. Consider these tips:

• Bold the headers and adjust the text size for better visibility.
• Add color to the table to differentiate the texts.

After applying these enhancements, your tally chart will be more visually appealing and informative.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Conclusion

Now you know how to create a tally chart in Excel using basic functions. When you need a quick count or visualization, the REPT function is a flexible approach that can be customized for more complex tally charts. Converting raw data into a tally chart is no longer a problem in Excel with this tutorial.

Creating tally charts can be fun, but analyzing more complex datasets isn't as easy as using simple tools. That's where we can help. We've built an AI-powered tool like Graphed which allows you to convert data to insights easily. With Graphed, you can ask questions in plain language and get instant answers, reports, and dashboards that look like they were made by a professional. So whether you're starting a project or refining your strategy, Graphed is there for you.