Excel Pivot Table Histogram: Create Bins and Counts

Charts & pivot tables · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, create a PivotTable from your numeric data, place the measured field in both Rows and Values, set Values to Count, then right-click a row label and choose Group to define the bins. The result is a histogram-style frequency table; you can visualize it with a column PivotChart, although Excel’s dedicated Histogram chart is a separate chart type.

What a PivotTable histogram shows

A histogram summarizes how often numeric observations fall within ranges, also called bins or classes. For example, a service team might want to know how many support calls lasted 0–9 minutes, 10–19 minutes, 20–29 minutes, and so on. The bins show the distribution of a measurement, rather than the count for each individual value.

A PivotTable is useful for this because it can group numeric row labels into ranges and count the records in each range. It also lets you add other fields to the analysis. If the source data includes a department or product category, you can compare distributions by putting that field in Columns or Filters, or use a slicer to focus on a category.

For example, suppose a table has one response-time value per ticket. A PivotTable with response-time ranges in Rows and a count of ticket IDs in Values answers how many tickets fell into each range. If you count the response-time field itself, blank cells are excluded from the count; counting a reliably populated ticket ID is often clearer.

Build the frequency table

Start with a clean source table: one record per row, a single header row, and a column containing the numeric measurement. Avoid merged cells and blank rows in the middle of the data. In Excel for Microsoft 365 on Windows, select a source cell and choose Insert > PivotTable. Confirm the table or range, choose where to place the PivotTable, and select OK.

  1. In the PivotTable Fields pane, drag the measurement field to Rows.
  2. Drag that same field to Values. Excel may choose Sum automatically, so open the Values field menu, choose Value Field Settings, select Count, and click OK. If you have a populated record ID field, you can use that in Values and set it to Count instead.
  3. Click a numeric item in the PivotTable’s Row Labels, right-click it, and choose Group.
  4. In the Grouping dialog, enter the Starting at value, Ending at value, and By interval. For example, response times from 0 through 60 grouped By 10 create ten-unit ranges across the selected span.
  5. Choose OK, then check the labels and counts against the source data. Rename the row field or value heading if the defaults are unclear.

The dialog’s Starting at and Ending at fields set the limits of the grouping, while By controls the interval size. Excel may adjust the displayed range labels to fit its grouping rules; inspect the first and last groups to confirm that the boundaries cover the observations you intend to include. If you later add records outside the original limits, refresh the PivotTable and review the grouping boundaries.

Choose useful bin widths

Bin width affects the story a histogram tells. Very narrow bins can make a modest data set look noisy, while very wide bins can hide peaks, gaps, or differences between groups. There is no single interval that works for every data set: choose a width that fits the measurement’s precision, the number of observations, and the question you are asking.

  • Match the unit to the decision. For call duration, five-minute intervals may be useful for staffing; for annual revenue, intervals of a few dollars would usually be unhelpful.
  • Use interpretable boundaries. Rounded, consistent intervals are easier to explain than awkward cutoffs. Make sure the minimum and maximum values are included.
  • Consider the sample size. A small set usually cannot support many meaningful bins. A large set may need narrower bins to reveal variation.
  • Keep comparisons consistent. When comparing departments or time periods, use the same bin boundaries. Different widths can make distributions appear more different than they are.

For a simple example, imagine 240 delivery times measured in minutes, ranging from 0 to 58. Grouping from 0 to 60 by 10 produces six equal-width ranges. If the counts are 18, 47, 76, 55, 31, and 13, they add to 240, so every observation is represented. That sum is a useful check; if it does not match the number of valid measurements, investigate blanks, text values, or excluded outliers.

Turn the PivotTable into a histogram-style chart

A histogram conventionally uses adjacent bars because its horizontal axis represents continuous numeric intervals. To chart the grouped PivotTable, select a cell in it and choose Insert > PivotChart. Select a Column chart, such as Clustered Column, and click OK. The chart remains connected to the PivotTable and can respond when you filter or refresh it.

To make the columns look more like histogram bars, right-click a column and choose Format Data Series. Under Series Options, reduce Gap Width, often to zero or a small value. The exact appearance depends on the chart and Excel build. Check that the horizontal axis shows the ranges in numeric order and that the chart title and vertical axis make the units and count clear. If the chart includes field buttons that distract from the view, right-click a button and hide the relevant field buttons, or use the PivotChart Analyze tab’s field-button controls.

A column chart is a practical way to display PivotTable bins, but it is not identical to Excel’s dedicated Histogram chart. The PivotChart inherits the PivotTable’s grouping and filtering behavior. Excel’s built-in Histogram chart instead bins the source observations itself, so it does not use the PivotTable’s grouped field as its bin definition.

Check the counts and explain the result

Before drawing conclusions, verify that the frequency table answers the intended question. The total of the bin counts should normally equal the number of valid numeric observations covered by the grouping. Check the minimum and maximum, and look for missing values or unusual measurements that could sit outside the chosen limits.

Counts are not the same as percentages. If you want to compare groups with different numbers of records, a group with more observations will often have higher counts in every bin simply because it is larger. You can show values as a percentage of the grand total or calculate percentages in a separate summary, but state clearly whether percentages refer to all records or to each group. For comparing distributions within departments, percentages within each department may be more informative than raw counts.

Also distinguish a histogram from a time series. If each row is a dated observation, grouping the date field by month answers how records vary over time; it does not show the distribution of a separate measurement. To study, for example, the distribution of invoice amounts by month, use invoice amount as the grouped numeric field and month as a filter, column, or other comparison field.

Common grouping problems and fixes

If Group is unavailable or Excel reports that it cannot group the selection, inspect the source column. A numeric-looking entry stored as text, an error value, or a blank can prevent grouping. Remove or correct errors, convert values to genuine numbers, and decide how to handle missing observations. Do not replace missing data with zero unless zero is a real measurement; doing so changes the distribution.

  • Mixed types: Make sure the measurement column contains numbers throughout, not a mixture of numbers and labels such as N/A or Unknown. Keep explanatory notes in another column.
  • Unexpected blanks: Filter or review the source rows. If a blank means no measurement, exclude it from the numeric distribution and report how many records were missing.
  • Values beyond the range: Expand Starting at or Ending at in the grouping settings, then confirm the resulting bins still make sense.
  • New rows do not appear: Refresh the PivotTable. A source formatted as an Excel Table expands more conveniently when rows are appended; a PivotTable based on a fixed cell range may require its data source to be expanded.
  • Counts seem too low: Check whether the Values field is set to Count rather than Sum, whether blanks are being excluded, and whether filters are active.

For ongoing reports, format the source range as an Excel Table using Insert > Table before creating the PivotTable. Give columns meaningful headers and keep each observation on its own row. After adding records, use PivotTable Analyze > Refresh. Refresh updates the PivotTable from its source; it does not automatically correct poor data or guarantee that custom grouping limits cover newly added extremes.

PivotTable grouping versus Excel’s Histogram chart

Use PivotTable grouping when you want a refreshable frequency table that can be filtered, split by categories, or incorporated into a broader PivotTable report. It is especially useful when counts need to be checked directly or when the same bins should be compared across several groups.

Use the built-in Histogram chart when you mainly want a quick chart of one numeric column and do not need PivotTable fields or category breakdowns. Select the data and choose Insert > Insert Statistic Chart > Histogram. Excel calculates bins for the chart, and chart-formatting controls let you adjust bin width or the number of bins. This chart is separate from the PivotTable workflow, so its bin settings do not automatically synchronize with PivotTable grouping.

If you need unequal-width categories, such as under 10, 10–24, and 25 or more, the PivotTable Group dialog is not the right tool for defining arbitrary boundaries: its numeric grouping uses a consistent interval. Create a separate bin-category field in the source data, assign each record to the appropriate named category, and put that field in PivotTable Rows. This also gives you control over labels such as Under 10 or 25+. Ensure categories are mutually exclusive and cover the observations you intend to count.

Frequently Asked Questions

Can you make a histogram with a PivotTable in Excel?
Yes. Put a numeric field in Rows and Values, change Values to Count, then group the row field into numeric intervals. A column PivotChart can display the grouped counts with little or no gap between bars.

Why can’t Excel group my PivotTable numbers?
The source field may contain blanks, errors, or values stored as text alongside numbers. Clean the column so its entries are consistently numeric, refresh the PivotTable, and try Group again.

How do I change the bin size?
Right-click a grouped numeric row label, choose Group, and change the By value. Use a consistent interval that suits the data and the comparison you need to make.

Can PivotTable bins have different widths?
Not through the standard numeric Group dialog, which groups by a regular interval. For unequal ranges, create a source-data category field with the desired labels and use that field as the PivotTable row field.

Does a PivotChart automatically use histogram bins?
No. A PivotChart can plot counts from a grouped PivotTable, but Excel’s dedicated Histogram chart is a separate chart type that bins the source values independently.

Related

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.