Excel Bar Graph: Count Occurrences and Chart Results

To count how often each category appears in Excel and show the totals in a bar graph, make a list of categories, count each one with COUNTIF, then chart the category-and-count summary. With the raw values in A2:A100 and a category to count in C2, use =COUNTIF($A$2:$A$100,C2) in D2, fill it down, and select columns C and D to insert a bar chart.

What an occurrence-count bar graph shows

An occurrence-count bar graph compares how many times each value appears in a list. For example, a column of survey responses might contain Apple, Pear, and Orange many times. The graph does not need every original response as a separate bar; it needs one bar per category, with the bar length representing that category’s count.

This is sometimes called a frequency chart. The key is to summarize the raw data first. If you chart the unsummarized list directly, Excel may display one data point per row rather than combine matching labels into useful totals. A frequency table gives the chart a clear source: one column of category names and one column of counts.

A bar chart is especially useful when labels are long or when there are several categories. It makes the most and least frequent values easy to compare. If the values are measurements on a continuous scale, such as heights or elapsed times, a histogram may be more appropriate because it groups numbers into ranges rather than treating every distinct number as a separate category.

Count occurrences with COUNTIF

COUNTIF counts cells in a range that meet one condition. For a basic frequency table, put the original values in one column and list each category you want to count in another. The category list can be typed manually, copied from the source, or generated with a suitable Excel feature. Make sure each category appears only once in the summary list; otherwise the chart will show duplicate bars for the repeated summary labels.

For the example above, the source values are in A2:A100, the category label to count is in C2, and the result goes in D2. Enter =COUNTIF($A$2:$A$100,C2) in D2 and copy it down alongside the remaining category labels. The dollar signs keep the source range fixed as the formula is copied, while C2 changes to C3, C4, and so on. If C2 contains Pear and Pear appears 12 times in A2:A100, the formula returns 12.

COUNTIF is not case-sensitive for ordinary text, so Apple and APPLE are counted as the same text value. It also recognizes wildcard characters in criteria: an asterisk can represent any sequence of characters, and a question mark can represent one character. If category names themselves contain those characters, check the result carefully or escape wildcard characters with a tilde in the criterion. For everyday category lists without wildcards, a direct cell reference is usually the simplest approach.

Build a small frequency table step by step

Suppose A2:A13 contains the responses Red, Blue, Red, Green, Blue, Red, Yellow, Green, Blue, Red, Green, and Blue. Create a summary table with Red, Blue, Green, and Yellow in C2:C5. The count formula in D2 is =COUNTIF($A$2:$A$13,C2); this assumes the responses are in A2:A13, the first summary category is in C2, and the formula is entered in D2. Copy it down through D5. The results are Red 4, Blue 4, Green 3, and Yellow 1, which total 12 and match the number of responses.

Check that total before charting. If the categories cover every nonblank source value, their counts should add up to the number of nonblank entries. A mismatch can reveal a missing category, a blank row, inconsistent spelling, or extra spaces in some cells. For instance, Blue and Blue followed by a space may look identical on screen but can be treated as different text values.

Put a clear heading above the source data, such as Response.
Enter each distinct category once in the summary column and add a Count heading beside it.
Enter COUNTIF for the first category, then fill the formula down.
Compare the sum of the counts with the number of source entries you intended to include.
Select only the summary labels and counts when creating the chart.

Use a PivotTable when the list is longer

A PivotTable is often quicker than building a formula-based summary, particularly when the source list is large or you expect to change the categories. Select a cell in the data, choose Insert > PivotTable, confirm the table or range and where the PivotTable should go, then create it. In the PivotTable Fields pane, drag the category field to Rows and also to Values. Excel will summarize the field in the Values area.

If the field contains text, Excel normally counts its entries. If the field is numeric and Excel displays Sum instead, open the Values field menu, choose Value Field Settings, select Count, and confirm. Count counts nonblank entries in that field; it does not count blank cells. If blank categories matter to the analysis, decide how to label or handle them before interpreting the result.

When the PivotTable shows one row per category and a count beside it, select a cell in the PivotTable and use Insert > Insert Column or Bar Chart > 2-D Bar > Clustered Bar. A PivotChart remains connected to the PivotTable, so you can use PivotTable filters and refresh the source after adding data. If the source is an ordinary range rather than an Excel table, check that the PivotTable’s source range includes newly added rows before refreshing. Converting a changing source range to a table with Insert > Table can make new rows easier to include.

Choose the right chart and make it readable

For category counts, a clustered bar chart uses horizontal bars; a clustered column chart uses vertical bars. Both can communicate the same totals. Horizontal bars are often easier to read when category names are long, while columns can work well for a short list of labels. Use a standard bar or column chart for discrete categories, not a line chart that suggests a continuous progression between categories.

Give the chart a descriptive title, such as Number of Responses by Color, and make sure the value axis begins at zero. A zero baseline matters because bar lengths encode magnitude; a truncated scale can exaggerate differences. Data labels can display the exact counts on the bars, which is helpful when readers need precise values as well as a visual comparison. Avoid adding effects or too many colors that distract from the ranking.

If the goal is to show which categories occur most often, sort the summary table by count from largest to smallest before charting. In a normal chart based on worksheet cells, the displayed category order follows the source table order. For a horizontal bar chart, Excel may place the first category at the bottom depending on the axis settings; that is an ordering choice, not a counting error. Review the axis options if you want the largest category at the top.

Get distinct categories and keep the summary current

For a short list, typing each category into the summary table is straightforward. For a changing list, Excel for Microsoft 365 and Excel 2021 can produce a distinct, sorted category list with a dynamic array formula. With source values in A2:A100, enter =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))) in C2. It returns the nonblank distinct values in sorted order and spills them into cells below. This formula requires a version with FILTER, UNIQUE, and SORT, such as Microsoft 365 or Excel 2021; older versions need a different method, such as a PivotTable or Remove Duplicates on a copy of the data.

For the counts beside a spilled list, enter =COUNTIF($A$2:$A$100,C2) in D2, assuming the original data is still A2:A100 and C2 is the first spilled category, then fill the formula down far enough to cover the list. If the number of categories changes frequently, a PivotTable may be easier to maintain because it can group the categories and count them in one summary. A formula-based chart can also be set up around a table or dynamic range, but check that its source includes all the rows that may appear.

Common counting and charting problems

A count that seems too low is often caused by inconsistent source entries. Leading or trailing spaces, spelling differences, punctuation, and number-versus-text differences can split what a person considers one category into separate values. Clean or standardize the source before counting if those differences are accidental. Do not merge categories merely because they look similar if the distinction matters to the analysis.

Another frequent issue is counting a range that excludes later rows or includes a heading. Confirm the exact source range in the formula or PivotTable. COUNTIF ignores blank cells when counting a text category, but a blank category in a summary table can make the result confusing. Decide whether blanks mean missing information, a genuine category, or rows that should be excluded.

If the chart has one bar per source row, it was probably created from the raw list instead of the frequency table. Create or select the summarized category-and-count range and insert the chart from that selection. If the graph shows category names as separate series or the axes look reversed, use Chart Design > Switch Row/Column or adjust the selected data range. The exact available chart controls can depend on the chart type and Excel release.

Bar chart versus histogram and other options

A bar chart treats each label as its own category, so it suits responses, product names, departments, or distinct codes. A histogram is designed for numeric observations grouped into bins, such as ages from 10 to 19, 20 to 29, and so on. If every individual measurement becomes its own bar, the data may need binning rather than a category count. Excel’s histogram chart is available through Insert > Insert Statistic Chart > Histogram in supported desktop versions; bin settings control how numeric ranges are grouped.

For a compact report, a PivotTable alone may be enough, particularly if readers need to filter or inspect the exact totals. A bar chart is more effective when the main purpose is quick comparison. A 100% stacked chart answers a different question: it compares proportions within groups rather than simply showing each category’s raw count. Choose the display that matches the question, and label counts or percentages clearly so readers do not confuse the two.

Frequently Asked Questions

Can Excel make a bar graph that counts repeated words automatically?
Yes. Create a category summary with COUNTIF or use a PivotTable with the word field in Rows and Values summarized by Count, then insert a bar chart from that summary. The chart reflects the summarized counts, not an automatic count of every raw row in an ordinary bar chart.

How do I count how many times a value appears in a column?
With the values in A2:A100 and the value to look for in C2, enter =COUNTIF($A$2:$A$100,C2) in another cell. It returns the number of cells in that range matching the criterion in C2.

Why does my bar graph show duplicate categories?
The source summary may list a category more than once, or the raw data may contain near-duplicates such as entries with extra spaces or different spelling. Make the summary labels unique and standardize source values when the differences are unintended.

Should I use a bar chart or a histogram to count occurrences?
Use a bar chart for named or discrete categories, such as colors or survey answers. Use a histogram for numeric measurements grouped into ranges, such as scores or ages. The distinction is whether each value is a category or part of a continuous numeric scale.

Can I count occurrences without writing a formula?
Yes. A PivotTable can group matching values and count them: put the category field in Rows and Values, and set the Values summary to Count if Excel selected another calculation. Then insert a bar chart from the PivotTable or create a PivotChart.

Final checks before sharing the graph

Before sending the workbook, verify that the count range covers the intended records, that the category totals make sense, and that blank or inconsistent values have been handled deliberately. Confirm that the chart uses the summary table, not the ungrouped source list, and that its title and labels explain what is being counted. A simple frequency table paired with a clearly labeled bar graph is usually the most transparent way to show how often each category occurs in Excel.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.