Excel Pie Chart: Count Occurrences for Each Category

Yes. To make an Excel pie chart count occurrences, first summarize how many times each category appears, then chart those totals; a PivotTable is the quickest method, while UNIQUE and COUNTIF can build a formula-based summary in Microsoft 365. A pie chart displays the category counts as slices, and can also show each count or its share of the total as a data label.

Why a pie chart needs a summary

A pie chart represents parts of one whole. It needs a category for each slice and a numeric value that determines that slice’s size. A list of repeated responses, such as one answer per row, is not yet the right structure: Excel may treat each row as a separate data point rather than combining identical answers into one slice. Summarizing the list first turns repeated entries into meaningful category totals.

For example, suppose a survey column contains Tea, Coffee, Tea, Juice, Coffee, Tea, Water, Juice, Tea, and Coffee. The useful chart data is Tea: 4, Coffee: 3, Juice: 2, and Water: 1. Those values total 10, so the chart can show both the number of responses and the proportion represented by each category.

This is a count of records or cells, not necessarily a count of unique people. If a person can appear multiple times in the source, each matching row contributes to the total. Decide what one occurrence means in your data before interpreting the chart.

Method 1: Count occurrences with a PivotTable

A PivotTable is usually the easiest choice when you want a summary without writing formulas. The steps below apply to Excel for Microsoft 365 on Windows. Start with a single column of category values and a descriptive header, such as Category, in the first row.

Select a cell in the source list. If the data is in a rectangular range with headers, Excel can usually identify the full range. For a more reliable source, select the entire range, including the header.
Choose Insert > PivotTable. In the dialog, confirm the table or range, choose New Worksheet, and select OK.
In the PivotTable Fields pane, drag the category field to Rows. Drag that same field to Values.
Check the Values field. If Excel shows Count of Category, it is counting entries as intended. If it shows another summary, open the field’s menu, choose Value Field Settings, select Count, and choose OK.

The PivotTable now has one row per category and a count beside it. For the example above, it should show four categories with totals of 4, 3, 2, and 1. If a category appears twice with different spelling, punctuation, or extra spaces, Excel treats those entries as different labels; clean the source if they should be one category.

To chart the PivotTable, select a cell in it and choose PivotTable Analyze > PivotChart. In the chart dialog, select Pie, choose a 2-D Pie option, and select OK. You can also make a regular chart from a summarized table by selecting its category and count columns and choosing Insert > Insert Pie or Doughnut Chart > 2-D Pie.

Method 2: Use UNIQUE and COUNTIF

Formulas are useful when you want a summary that updates as the source changes, or when you need to place the results in a particular part of a worksheet. In this example, the raw responses are in A2:A11. The list has the ten example entries, with no blank rows. Put the headings Category and Occurrences in D1 and E1.

With the source responses in A2:A11, enter this formula in D2 to return each nonblank category once:

=UNIQUE(FILTER(A2:A11,A2:A11<>""))

With the same responses in A2:A11 and the spilled category list beginning in D2, enter this formula in E2 to count each category:

=COUNTIF($A$2:$A$11,D2#)

In Microsoft 365, the first formula spills the distinct category names downward, and the second spills the matching counts beside them. For the example, the results are Tea 4, Coffee 3, Juice 2, and Water 1; the counts add up to 10. The order returned by UNIQUE follows the order in which categories first appear. Select D1:E5, then choose Insert > Insert Pie or Doughnut Chart > 2-D Pie.

These functions are available in Microsoft 365 and Excel 2021 or later, but the formulas as written rely on dynamic arrays and the spill operator. In older Excel versions, use a PivotTable or create a distinct category list by another method, then copy a COUNTIF formula down beside it. For example, with source values in A2:A11 and one category in D2, =COUNTIF($A$2:$A$11,D2) returns the count for that one category; fill the formula down for the remaining labels.

Show counts, percentages, or both

A pie slice’s size shows its share of the total, but the chart does not have to display a number automatically. To add labels, select the chart and choose Chart Design > Add Chart Element > Data Labels. Choose a position, such as Outside End, then open More Data Label Options if you need to control what each label contains.

In the Format Data Labels pane, you can choose options such as Category Name, Value, and Percentage. Choose Value to show the occurrence count. Choose Percentage to show each category’s share of all included values. Selecting both Value and Percentage can make a chart easier to read when the exact count and relative size are both important. Avoid displaying redundant information if labels become crowded.

For the example, the total is 10: Tea represents 40%, Coffee 30%, Juice 20%, and Water 10%. These percentages are derived from the counts, not separate measurements. If you change which categories are included, the whole and therefore the percentages change too.

Check the source and the meaning of Count

Count means the number of nonempty entries in the field being summarized. With text categories, that is generally the desired behavior. If you count a numeric field instead, blank cells are not counted. A PivotTable’s Count is different from a distinct count: if Tea occurs four times, ordinary Count reports four, not one unique category.

Before charting, check the data for common issues:

Blanks: Empty cells do not represent a named category. A formula using UNIQUE can include a blank unless it is filtered out, which is why the example excludes empty strings.
Inconsistent labels: Tea and tea, or North and North with a trailing space, may be treated as separate categories. Standardize labels when they mean the same thing.
Extra rows: Confirm the PivotTable source or formula range includes every response. A fixed range such as A2:A11 will not automatically include new entries typed below row 11.
Unexpected totals: Add the summary counts and compare the result with the number of records you expected to include. Investigate differences rather than assuming the chart is wrong.

For a growing source list, converting the data to an Excel table with Insert > Table can make it easier to manage. PivotTables based on a table can include newly added table rows after you refresh the PivotTable. Formula ranges should also be expanded or designed to refer to the table data; otherwise, new rows can be left out.

Refresh and update the chart

A PivotTable does not always recalculate its summary immediately when source values change. Right-click inside the PivotTable and choose Refresh, or use Data > Refresh All. A PivotChart based on that PivotTable follows its updated summary. If you add rows outside the original source range, first update the PivotTable’s data source or use a suitably expanding Excel table, then refresh.

The formula summary updates when its referenced cells change, but it cannot count entries outside the ranges named in its formulas. If the source is extended, update A2:A11 in both formulas or change the source design so the formulas include the new records. Check that the chart’s selected data range still covers all category and count rows after the summary grows.

On Excel for Mac, PivotTables and charts are available, although ribbon placement and dialog wording can vary by release. Look for PivotTable options on the PivotTable Analyze tab and chart commands on Insert or Chart Design. In Excel for the web, you can work with PivotTables and charts, but some editing controls differ from the Windows desktop app; a regular pie chart from a two-column summary is a practical option when a particular PivotChart control is unavailable.

When a pie chart is a good choice

A pie chart can work well when there are only a few categories, every category is part of the same meaningful whole, and readers need a quick sense of proportion. A count-based pie is appropriate for mutually exclusive responses where each record belongs to one category. It is less suitable when categories overlap, when the values do not share a meaningful total, or when there are many small slices.

For many categories, or when readers need to compare exact counts, a bar chart is often easier to interpret because bar lengths are easier to compare than slice angles. A PivotTable alone may be better when the main task is checking exact counts. You can keep the PivotTable as the auditable summary and use a chart only when the proportional view adds value.

Do not use a pie chart to imply that a count is a percentage of a larger population unless the source actually represents that population. For example, a list of 10 survey responses supports the statement that 4 of those 10 responses were Tea. It does not, by itself, prove that 40% of all customers prefer Tea.

Frequently Asked Questions

Can Excel count repeated words for a pie chart?
Yes. Put the words in a source column, summarize them by category with a PivotTable or a distinct list plus COUNTIF, and chart the category-count summary. Repeated words then contribute to one category’s total.

How do I show the number on each pie slice?
Select the chart and choose Chart Design > Add Chart Element > Data Labels. In More Data Label Options, enable Value to display the count. Enable Percentage as well if readers need both the count and the share of the total.

Why does my pie chart show each row separately?
The chart is likely using the unsummarized source list. Create a category-and-count summary first, then chart those two columns. Otherwise, repeated labels may appear as separate points rather than a single combined category.

Does Count include blank cells?
No. A PivotTable Count summarizes nonempty entries in the selected field. If blanks matter as a category, label them explicitly in the source, such as Not provided, before summarizing.

Will a PivotChart update when I add responses?
It updates after its PivotTable is refreshed, provided the new responses are within the PivotTable’s source. If the source range did not include the added rows, expand the source or use an Excel table as the source, then refresh.

Can I count occurrences without a PivotTable?
Yes. In Microsoft 365 or Excel 2021, UNIQUE can produce a category list and COUNTIF can count each listed category. A regular pie chart can then use that formula-generated summary as its source.

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.