Pivot Table Issues

How to Create an Excel PivotChart to Count Categories by Status

Emma Brown Oct 10, 2026 869 views

Question details

The user needs to create a PivotTable and PivotChart that automatically count items based on their category and status without the need to manually add separate helper columns for every status value.

How to Create an Excel PivotChart to Count Categories by Status

Product: Excel
Device & OS: not provided
Scenario: Analyzing a dataset containing support items or records where the user wants a visual representation (PivotChart) of how many items fall into each status per category.
Observed behavior: The goal state is an aggregated PivotTable displaying counts by category and status, accompanied by a column or stacked column PivotChart, omitting unnecessary grand totals.

Before you start

Ensure your raw data is organized in a proper tabular format with clear column headers for 'Category' and 'Status', and verify there are no blank rows or merged cells within the dataset.

Solution 1Recommended

Use the PivotTable Fields to Group and Count Automatically

By placing text fields into the Values area, Excel defaults to counting the records, allowing you to generate the required PivotTable and PivotChart without helper columns.

Excel PivotTables are designed to aggregate data natively. When you drag a non-numeric field into the Values area, it automatically applies the 'Count' function. This eliminates the need to create complex formulas or helper columns to tally status values.

Insert the PivotTable

Click anywhere inside your dataset, navigate to the 'Insert' tab on the ribbon, and click 'PivotTable'. Choose to place it on a New Worksheet or an Existing Worksheet, then click 'OK'.

Configure Rows and Columns

In the PivotTable Fields pane, drag the 'Category' field into the 'Rows' area. Next, drag the 'Status' field into the 'Columns' area.

Set Up the Values Area

Drag either the 'Category' or 'Status' field into the 'Values' area. Excel will automatically count the number of records. To use 'Status' in both the Columns and Values areas, drag the field from the field list to the Values area again (do not drag it away from the Columns area).

Insert the PivotChart

Select any cell inside the newly created PivotTable. Go to the 'Insert' tab, click 'PivotChart', and choose either a 'Column' or 'Stacked Column' chart. Click 'OK' to generate the chart.

Remove Grand Totals (Optional)

If you do not want the grand totals to skew your chart visually, click on the PivotTable, go to the 'Design' tab under PivotTable Tools, click 'Grand Totals', and select 'Off for Rows and Columns'.

Use the PivotTable Fields to Group and Count Automatically

Pro Tip: If you are using a Stacked Column chart, removing the grand total from the PivotTable ensures your chart only focuses on the segmented status counts without an overpowering total column.

Solution 2

Use Power Query and the Data Model

For advanced data transformation or larger datasets, load the data into Excel's Data Model via Power Query before creating the PivotTable and PivotChart.

Effortless Data Analysis

Create PivotCharts Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides a powerful, user-friendly environment for creating PivotTables and PivotCharts. You can count, analyze, and visualize categories by status in just a few clicks without relying on complex formulas.

1. Open Your Data File: Launch WPS Spreadsheet and open your existing dataset containing the category and status columns.
2. Insert PivotTable: Navigate to the 'Insert' tab and select 'PivotTable'. Choose your data range and click 'OK'.
3. Configure the Fields: Drag 'Category' to the Rows area, and drag 'Status' into both the Columns area and the Values area to automatically count the occurrences.
4. Generate the Chart: Click the 'Insert' tab again, select 'Chart', choose 'Column', and pick the stacked column layout to visualize your data.

100% compatibility with Microsoft Excel (.xlsx) file formats.Intuitive drag-and-drop PivotTable interface to easily count categories.Extensive built-in chart types including clustered and stacked columns.Free and lightweight alternative to Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my PivotTable summing the data instead of counting?

If your target column contains numbers, the PivotTable defaults to calculating a 'Sum'. To fix this, right-click any value within the PivotTable, select 'Summarize Values By', and change it to 'Count'.

How do I change my PivotChart from a regular column to a stacked column?

Click on your existing PivotChart to select it. Navigate to the 'Design' tab under PivotChart Tools on the ribbon, click 'Change Chart Type', and select the 'Stacked Column' option.

Can I filter the status directly from the PivotChart?

Yes, PivotCharts are fully interactive. You can click the 'Status' or 'Category' field buttons directly on the chart itself to open a dropdown menu and filter out specific data points without altering the base PivotTable structure.

Excel & Data Problems