To make an Excel histogram by category, first use the same numeric bin boundaries for every category, then count the observations in each bin with a PivotTable or a COUNTIFS table and chart the results as clustered columns. Excel’s built-in Histogram chart handles one numeric series at a time; it does not automatically split that series into separate histograms based on a category column.
What “histogram by category” means in Excel
A histogram shows how often numeric values fall into ranges called bins. For example, a store might compare order values in ranges of $0–$49, $50–$99, and $100–$149 for online and in-store sales. The category is the group being compared; the numeric field is what gets divided into bins.
That distinction matters because Excel’s native Histogram chart expects a single column or row of numeric observations. If you select measurements alongside labels such as East and West, Excel does not automatically create a separate distribution for each label. To compare categories fairly, you need a shared set of bins and a count for each category in each bin. The resulting chart is usually a clustered column chart arranged to look and read like a grouped histogram.
For instance, a delivery team could compare shipping times for two services. If one chart uses bins of 0–2, 2–4, and 4–6 days, both services can be compared range by range. Giving each service different bin boundaries would make the chart much harder to interpret.
Prepare the source data
Keep the data in a simple, consistent layout: one row per observation, one column for the numeric measurement, and one column for the category. A small example might have “Days” in column A and “Service” in column B, with the observations below the headings. Make sure the measurement cells contain actual numbers, not numbers stored as text, and use consistent category labels.
- Remove or account for blank measurements. A blank is not a zero and should not be counted as one.
- Check for spelling variations such as “North” and “NORTH”; Excel treats these as different text values in many comparison contexts.
- Decide how to handle negative values, outliers, and unusually large values before choosing bins.
- Keep a copy of the original data. Build the summary table in a separate area or worksheet so the source remains easy to audit.
When the data is an Excel Table, new rows can be included more conveniently as the source grows. Select the data and choose Insert > Table, or press Ctrl+T on Windows (Command+T on Mac in many Excel configurations). Confirm that the table has headers. You can then use its column names in PivotTables, or expand ordinary formula ranges when adding observations.
Method 1: Build a shared-bin count table with COUNTIFS
A formula summary gives you control over the bin limits and works well when you want a chart that updates as the underlying values change. Suppose numeric values are in A2:A11 and category names are in B2:B11. In a separate summary area, put category names such as East and West in C1 and D1. Put the lower and upper limits of each bin in columns E and F: 10 and 20 for the first bin, 20 and 30 for the next, 30 and 40 for the next, and 40 and 50 for the last.
With values in A2:A11, categories in B2:B11, the first bin’s lower and upper limits in E2 and F2, and the category name in C1, enter this formula in the summary cell for that bin and category. It returns 2 for the example data below:
=COUNTIFS($A$2:$A$11,">="&$E2,$A$2:$A$11,"<"&$F2,$B$2:$B$11,C$1)
Copy the formula across for the other category headings and down for the other bins. COUNTIFS counts rows that meet all the conditions: the value is at least the lower boundary, below the upper boundary, and assigned to the named category.
For a worked check, let East have values 12, 18, 24, 31, and 42, and West have 15, 21, 27, 33, and 47. For bins 10–20, 20–30, 30–40, and 40–50, the counts are East: 2, 1, 1, 1; West: 1, 2, 1, 1. Each category totals five observations, matching the source rows. The formula treats each bin as lower-inclusive and upper-exclusive, so a value of 20 goes in 20–30, not 10–20. For the final bin, the example contains no value of 50; if the maximum could equal the final upper boundary, make that final upper test inclusive or set the last upper boundary above the maximum.
Turn the summary into a histogram-style chart
Select the bin labels and the category count columns in the summary table, then choose Insert > Insert Column or Bar Chart > Clustered Column. In Excel for Microsoft 365 on Windows, the clustered column chart places each category’s count beside the other categories’ count for each bin. Use bin ranges as the horizontal-axis labels and category names as the legend entries.
- Give the chart a descriptive title, such as “Shipping time distribution by service.”
- Label the vertical axis “Number of orders” or “Frequency” so the measure is clear.
- Reduce the column Gap Width in Format Data Series if you want the bars to resemble histogram bars more closely. A smaller gap makes bars wider; it does not change the counts.
- Use a consistent color for each category throughout the chart and keep the legend visible.
A clustered column chart is often the clearest choice when comparing categories within each range. A 100% stacked column chart can instead show the share of each bin contributed by each category, but it answers a different question and can hide differences in total sample size. If category sizes differ substantially, consider showing percentages within each category as well as raw counts, or use a separate chart to show sample sizes.
Method 2: Group numeric values in a PivotTable
A PivotTable is useful when the dataset is large or you want to filter by other fields. Select a cell in the source data and choose Insert > PivotTable. In the PivotTable Fields pane, place the numeric measurement in Rows, the category field in Columns, and the measurement field in Values. If Excel summarizes the Values field as Sum rather than Count, open its value field settings and choose Count. Count is appropriate when each source row represents one observation.
To group the row values into ranges, right-click a numeric item in the PivotTable and choose Group. Set Starting at, Ending at, and By to suitable limits—for example, 10, 50, and 10—and confirm. The resulting grouped row labels provide common intervals; the category columns show the counts in each interval. Exact labels and dialog placement can vary slightly between Excel builds.
Grouping requires usable numeric values. Text in the measurement column, blanks, or mixed data can prevent grouping or produce confusing results. Correct the source first, then refresh the PivotTable after source data changes. To chart the summary, select the PivotTable and choose PivotTable Analyze > PivotChart, then select a clustered column chart. On Mac, ribbon organization and some labels may differ, but the same PivotTable fields and grouping idea apply.
Choose bins that make the comparison meaningful
Bin width affects the story a histogram tells. Very narrow bins can make a small dataset look erratic; very wide bins can conceal useful patterns. Start with a width that fits the measurement and the amount of data, then check whether the display makes important differences visible without creating mostly empty ranges.
- Use the same boundaries across categories. This is essential for a direct comparison.
- Cover the full range. The first and last bins should include the minimum and maximum observations, or the chart should clearly state what is excluded.
- Use readable labels and units. “20–30 days” is clearer than an unlabeled number range.
- Think about the question. Equal-width bins are easy to compare; business-defined thresholds may be more useful when decisions depend on fixed cutoffs.
Unequal bin widths need extra care. A simple count chart makes a wide interval appear comparable to a narrow interval even though it covers more values. If unequal widths are necessary, explain them clearly and consider plotting frequency density—count divided by bin width—rather than raw count. For many business reports, equal-width ranges are easier for readers to understand.
Native Histogram chart versus a category comparison
For one numeric series without categories, select the values and choose Insert > Insert Statistic Chart > Histogram in Excel for Microsoft 365 on Windows. You can adjust bin settings by formatting the horizontal axis and opening Axis Options. Depending on the data and chart settings, Excel can use automatic bins or let you specify a bin width, number of bins, overflow bin, or underflow bin.
That built-in chart is convenient for a single distribution, but it is not a substitute for a category-by-category summary. It does not automatically create shared bins and separate category counts from a category column. For category comparisons, use the COUNTIFS summary or a grouped PivotTable, then create a column chart. Excel for Mac offers histogram charting in supported desktop versions, although ribbon layout and formatting controls may differ. Excel for the web can have fewer chart-formatting controls; if a needed histogram or axis option is unavailable, the summary-table method with a column chart is a practical alternative.
Common problems and how to fix them
- A category is missing: Check for extra spaces, inconsistent spelling, or a filter that excludes its rows. Verify the category labels in the summary against the source.
- Counts seem too low: Confirm that every value is numeric and that the bins cover the full data range. Check how the formula treats boundary values; adjacent bins should not both include the same endpoint.
- A PivotTable will not group: Look for blanks, text values, or errors in the numeric field. Clean those entries and refresh or recreate the PivotTable if needed.
- Bars are hard to compare: Use shared bins, a visible legend, and a chart type suited to the question. Avoid 3-D effects, which make heights harder to judge.
- New records do not appear: Expand the formula ranges or use an Excel Table, and refresh a PivotTable. A PivotTable does not necessarily recalculate its source summary just because rows were added.
Frequently Asked Questions
Can Excel make a histogram for multiple categories automatically?
No. Excel’s built-in Histogram chart works with a numeric series rather than splitting values by a separate category field. Create shared-bin counts with COUNTIFS or a grouped PivotTable, then chart those counts.
Should I use a histogram or a clustered column chart?
Use the built-in Histogram chart for one numeric distribution. For side-by-side category comparisons, a clustered column chart based on a shared-bin summary is usually clearer and gives you direct control over category series and bin labels.
How do I make every category use the same bins?
Define one list of lower and upper boundaries and use it for all category counts. In a PivotTable, group the numeric field once; in a formula summary, reference the same boundary cells for each category.
Can I compare percentages instead of counts?
Yes. Divide each bin’s count by that category’s total number of observations to show the within-category percentage. This helps when groups have different sample sizes, but label the chart as a percentage distribution so readers do not mistake it for raw frequency.
Why does a value on a bin boundary appear in only one range?
The COUNTIFS example uses lower-inclusive, upper-exclusive intervals: 10–20 includes 10 but not 20, while 20–30 includes 20 but not 30. This convention prevents a boundary value from being counted twice.
Related
- Excel Flash Fill: Does It Only Copy Data?
- Excel Hide Columns Toggle Button: Options That Work
- Excel Hide Columns Just for Me? Use a Personal Sheet View
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Balloon Chart in Excel: A Step-by-Step TutorialTo make a balloon chart in Excel for Microsoft 365 on Windows, arrange your data in columns for…
- Microsoft Excel Pivot Charts: How to Create and UseA Microsoft Excel PivotChart is a chart linked to a PivotTable, so you can summarize and explore data…
- How to Create a Gantt Chart in Google SheetsTo create a Gantt chart in Google Sheets, list each task with its start and finish dates, add…
- How to Set Up a Pie Chart in Google Sheets EasilyTo set up a pie chart in Google Sheets, put category names in one column and their numeric…
- How to Sort an Excel Pie Chart from Largest to SmallestTo sort an Excel pie chart from largest to smallest, sort the chart’s source data by its value…
- Excel Pivot Table Relationships: How to Link DataExcel PivotTable relationships connect separate tables through matching columns in the workbook’s Data Model, so you can summarize…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.