Excel Chart Group by Category: How to Set It Up Right

To group Excel chart data by category, first summarize the records so each category has one row, then chart that summary. In Microsoft 365 for Windows, a PivotChart is usually the quickest option: insert a PivotTable, put the category field in Rows and the value field in Values, then create a chart from the PivotTable. A standard chart does not automatically combine repeated category names into totals.

What “group by category” means in an Excel chart

The phrase can mean two different things. You might want to combine many source records with the same label—such as several sales transactions for each region—into one total per label. Or you might already have a summary and simply want the chart to display the labels as groups along its category axis. The first task requires aggregation; the second is a chart-layout choice.

Excel charts use categories and series to organize plotted values. In a column chart, categories usually appear along the horizontal axis, while series are shown as separate colors or clusters. For example, a chart can use regions as categories and years as series. If the source contains repeated region names on separate rows, a regular chart generally treats those rows as separate plotted positions; it does not infer that the values should be added together.

Decide what each plotted mark should represent before choosing a chart. One column per region calls for a summary by region. Several columns per region, one for each year, call for a summary with regions as rows and years as separate series. Being clear about that distinction prevents a chart that looks grouped but communicates the wrong calculation.

Prepare the source data

A well-structured source table makes grouping more reliable. Use one header row, one record per row, and a separate column for each field. Avoid merged cells, blank header names, and manually inserted subtotal rows. For instance, a simple sales list could have columns named Region and Sales, with individual transactions beneath them. If you also need to compare years, add a Year column rather than creating a separate block of unrelated tables.

Before building the chart, check that category labels are consistent. “North,” “North ” with a trailing space, and “NORTH” can be treated as different text entries in a summary. Likewise, make sure amounts are numeric values rather than numbers stored as text; otherwise a PivotTable may count records instead of summing sales. Converting the range to an Excel Table with Insert > Table can make the data easier to maintain as new rows are added.

Give every source column a distinct, descriptive header.
Keep each transaction or observation on its own row.
Use the same spelling and spacing for categories throughout the data.
Store dates as Excel dates and amounts as numbers, not text.
Remove blank rows and existing totals from the middle of the source range.

Group categories with a PivotChart

A PivotChart is usually the most convenient way to summarize repeated categories because the PivotTable behind it performs the grouping and calculation. In Excel for Microsoft 365 on Windows, click a cell in the data, choose Insert > PivotTable, confirm the table or range, and choose where to place the PivotTable. After it is created, use the PivotTable Fields pane: drag the category field to Rows and the numeric field to Values. Excel will typically sum numeric values, but verify the displayed calculation.

To turn that summary into a chart, select a cell in the PivotTable and choose PivotTable Analyze > PivotChart. Select a chart type, such as Clustered Column, and confirm. The category field supplies the chart’s categories, while the Values field supplies the plotted numbers. If you add another field, such as Year, to Columns in the PivotTable Fields pane, the chart can show a separate series for each year.

Click inside the source data and create a PivotTable using Insert > PivotTable.
Drag the field to group by, such as Region, into Rows.
Drag the measure, such as Sales, into Values.
Check Value Field Settings to confirm Sum, Count, or another intended calculation.
Choose PivotTable Analyze > PivotChart and select a chart suited to the comparison.

If the source changes, refresh the PivotTable so the chart reflects the latest records. Use PivotTable Analyze > Refresh, or right-click inside the PivotTable and choose Refresh. If the source is an Excel Table, newly added rows are generally included when the PivotTable is refreshed; a fixed ordinary range may need its source range updated.

Build a regular chart from a summary table

You do not need a PivotChart for a small, stable report. Create a separate summary table with one row per category and the calculated value beside it, select that table, then choose Insert > Insert Column or Bar Chart > Clustered Column. For example, if Region is in A2:A7, Sales is in B2:B7, category names are listed in D2:D4, and totals will go in E2:E4, enter the following in E2 and fill it down:

With category names in A2:A7, sales in B2:B7, a category label in D2, and the result in E2, =SUMIF($A$2:$A$7,D2,$B$2:$B$7) adds sales matching the label in D2. For sample records North 120, South 90, North 80, East 110, South 60, and East 70, the results are North 200, South 150, and East 180.

This approach gives you direct control over the chart’s source and is useful when the output must follow a particular report layout. The trade-off is maintenance: if new categories appear, you may need to extend the summary list and formula ranges. A PivotTable is often easier for changing or larger datasets, while a formula-based summary can be simpler for a compact dashboard.

Show multiple series within each category

To compare related measures within each group, arrange the summary with categories down the first column and one measure or period per additional column. For example, a table with Region, 2025 Sales, and 2026 Sales can produce a cluster for each region, with two columns in each cluster. Select the table and insert a Clustered Column chart. Excel uses the first column as category labels and the other columns as series.

If Excel puts regions in the legend and years on the horizontal axis when you intended the reverse, select the chart and choose Chart Design > Switch Row/Column. The command changes which data orientation Excel interprets as categories versus series; it does not calculate totals or repair inconsistent source data. You can also use Chart Design > Select Data to inspect the chart data range, series names, and category-axis labels.

For a large number of categories, a clustered column chart can become crowded. A clustered bar chart places labels vertically and often makes long category names easier to read. If the goal is to show each category’s share of a total rather than compare absolute values, a 100% stacked chart may be more suitable, but it hides differences in total size. Choose the chart according to the comparison readers need to make.

Group dates or numeric values into broader categories

Sometimes the category field is too detailed. A PivotTable can group valid dates into periods such as months, quarters, or years, or group numeric values into ranges. In desktop Excel, right-click a date or number shown in the PivotTable and choose Group. In the dialog, choose the available grouping units or set a numeric interval. The resulting grouped PivotTable can drive a PivotChart, such as monthly sales rather than one point for every day.

Grouping depends on clean source values. Date grouping may fail if the date field contains blanks, text that looks like dates, or invalid entries. Numeric grouping requires numeric values. Correct the source field, refresh the PivotTable, and try again. For reporting that needs custom period definitions—such as a fiscal year that begins in a month other than January—adding a helper column to the source data can give more control than relying on automatic date grouping.

PivotTable grouping controls can vary by platform. These instructions describe Excel for Microsoft 365 on Windows; on Mac, the same general PivotTable concepts apply, although ribbon placement and available controls can differ. If the Group command is unavailable in Excel for the web, open the workbook in desktop Excel or create a helper field that explicitly assigns each record to the desired period.

Fix common chart grouping problems

If the chart repeats the same category label many times, it is probably charting transaction-level rows rather than an aggregated summary. Create a PivotChart or build a summary table first. If the PivotTable shows Count instead of Sum, open the Values field menu, choose Value Field Settings, and select Sum. If Sum is unavailable or produces an unexpected result, inspect the source values for numbers stored as text or mixed data types.

When a category is missing, check for spelling differences, leading or trailing spaces, active PivotTable filters, and rows outside the source range. Refresh after changes. If labels appear in an unexpected order, sort the PivotTable or summary table into the order readers need; a chart generally follows the order of its source categories. To display categories with no records, use an appropriate PivotTable setting where available or include them in a regular summary table with zero values.

Also distinguish a visual grouping from a calculation. Changing a chart to Stacked Column, switching rows and columns, or editing axis labels cannot combine duplicate source records into a total. Those options change presentation. Aggregation must happen in the PivotTable, a formula-driven summary, or another data-preparation step before a normal chart can show one value per group.

Frequently Asked Questions

Can Excel automatically group repeated category names in a regular chart?
A regular chart does not automatically sum repeated labels into one category total. Summarize the records with a PivotTable or formulas first, then chart the resulting one-row-per-category summary.

How do I group categories in an Excel PivotChart?
Create a PivotTable, place the category field in Rows and the numeric field in Values, verify the calculation, then use PivotTable Analyze > PivotChart. The PivotChart uses the PivotTable’s grouped results.

Why is my PivotChart counting instead of summing?
Excel may treat the value field as nonnumeric or may have selected Count in Value Field Settings. Check the source column for text values, correct the data if needed, and set the field’s calculation to Sum.

Can I group dates by month in an Excel chart?
Yes. Group the date field by month in the underlying PivotTable, then create or refresh the PivotChart. The date column must contain valid Excel dates, and date grouping can fail when the field contains blanks or text entries.

How do I show two years as separate bars for every category?
Build a summary with categories in the first column and one column for each year. Insert a Clustered Column chart and use Chart Design > Switch Row/Column if Excel assigns the years and categories in the opposite orientation.

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.