Excel Histogram by Group: A Practical Comparison Guide

Charts & pivot tables · Updated November 19, 2026

To compare an Excel histogram by group, use the same bin boundaries for every group, count each group’s values in each bin with COUNTIFS, then chart the resulting table as a clustered column chart. Excel’s built-in Histogram chart is designed for one numeric data series at a time, so a shared-bin summary table is the practical way to make a direct group comparison.

What “histogram by group” means

A histogram shows how numeric observations are distributed across ranges, or bins. For example, a company might compare delivery times for two carriers, or a teacher might compare test scores for two classes. A grouped histogram places the groups’ counts beside one another within each bin, making differences in shape and spread easier to see.

This is different from a bar chart of group averages. Two groups can have the same average but very different distributions: one might cluster near the average, while the other has many unusually low and high values. A histogram preserves that distribution detail, provided the bins are chosen sensibly.

Excel’s regular Histogram chart automatically groups one selected numeric series. It does not automatically split rows by a category column and show aligned, side-by-side histogram bars for each category. You can make separate native histograms, but their bin settings must be matched manually for a fair comparison. A frequency table gives you more control over shared bins and the chart layout.

Choose the right comparison method

Use a shared-bin table and clustered columns when you want exact, comparable counts by group. It is usually the clearest choice for a report with a small number of groups. Use separate native Histogram charts when you want Excel to select bins automatically for each individual series, or when you are exploring a single group. Avoid comparing separately auto-binned charts as if their bars represented the same value ranges.

  • Clustered columns from a frequency table: best for aligned bins and direct side-by-side comparison.
  • Separate native histograms: useful for quick exploration, but set identical bin widths and boundaries before comparing them.
  • Percentages instead of counts: useful when groups contain different numbers of observations.
  • Box-and-whisker charts: a compact alternative when the main question concerns medians, quartiles, and spread rather than counts in particular ranges.

The choice depends on the question. If a group with more records naturally produces taller bars, counts may obscure the shape comparison. If the question is how many cases fall into each range, counts are meaningful. If it is how each group’s observations are distributed proportionally, use percentages.

Prepare the source data

Put one observation on each row, with a group label in one column and the numeric measurement in another. For example, column A might contain a carrier name and column B its delivery time in days. Keep the headers in row 1, use consistent group labels, and make sure the measurement cells are numeric rather than numbers stored as text. Remove or resolve records that should not be included, such as blank measurements or values outside the study’s defined scope.

For the worked example, assume A2:A11 contains five “Control” labels followed by five “Treatment” labels, and B2:B11 contains these respective measurements: 2, 5, 9, 12, 17, 4, 8, 11, 18, 22. The example uses three bins: 0 to less than 10, 10 to less than 20, and 20 through 30. That gives Control counts of 3, 2, and 0, and Treatment counts of 2, 2, and 1.

In a real workbook, confirm that every group uses the same measurement units and that missing values are handled consistently. If one group has a value of 120 because of a data-entry error while the rest are below 30, decide whether it is a legitimate observation before choosing the chart scale; do not silently discard it just to make the chart look tidy.

Set shared bin boundaries

Create a small summary table away from the source data. Put lower and upper bin limits in columns C and D, readable bin labels in E, and group names as headers in F and G. For this example, set C2:D4 to 0 and 10, 10 and 20, and 20 and 30. Use labels such as “0–<10,” “10–<20,” and “20–30.” Keep the lower and upper limits numeric; the labels are for display, not calculation.

Choose boundaries that suit the data and the audience. Equal-width bins are a common starting point because they are easy to interpret. A bin width of five might suit measurements clustered between 0 and 30, while a width of 100 might be more appropriate for annual sales. Too many narrow bins can make random variation look important; too few broad bins can hide meaningful structure. There is no single bin width that is best for every dataset.

Decide how to treat boundary values. The example counts each ordinary bin as lower-inclusive and upper-exclusive: 10 belongs in the second bin, not the first. The final bin includes its upper limit so that a value of exactly 30 is counted. State this convention in a chart note if readers might otherwise be unsure.

Calculate counts with COUNTIFS

With group names in F1:G1, the lower and upper limits in C2:D4, and raw group labels in A2:A11 alongside values in B2:B11, enter this formula in F2 for the first ordinary bin. Copy it across to G2 and down for the ordinary bins:

=COUNTIFS($A$2:$A$11,F$1,$B$2:$B$11,">="&$C2,$B$2:$B$11,"<"&$D2)

For the 0-to-less-than-10 bin, the formula returns 3 for Control: the values 2, 5, and 9 meet both the group and boundary tests. It returns 2 for Treatment because 4 and 8 qualify. The dollar signs keep the source ranges and boundary columns in place when you copy the formula, while the group header and row references adjust as needed.

For the final bin, use an inclusive upper boundary. With the same layout, put this formula in F4, then copy it to G4:

=COUNTIFS($A$2:$A$11,F$1,$B$2:$B$11,">="&$C4,$B$2:$B$11,"<="&$D4)

That final Treatment count is 1 because 22 falls between 20 and 30, including the upper limit. Extend the source ranges to cover the actual records in your workbook, or use a consistently sized range that includes all records. Check the summary totals: for these ten observations, the three bin counts sum to five for each group. If they do not, investigate values below the first boundary, above the last boundary, blanks, or an unintended gap or overlap between bins.

Build and format the comparison chart

Select the bin labels and count columns, including their headers—for this layout, select E1:G4. On Excel for Microsoft 365 on Windows, choose Insert > Insert Column or Bar Chart > Clustered Column. If the chart places groups on the horizontal axis rather than bins, use Chart Design > Switch Row/Column. The desired result has bins along the horizontal axis and one colored series per group.

To make the columns look more like histogram bars, select a column, open Format Data Series, and reduce Gap Width. A smaller gap emphasizes continuity between adjacent numeric intervals; a modest gap can still help distinguish the group bars within each bin. Add a descriptive title, a vertical-axis title such as “Number of observations,” and a legend with clear group names. Keep the bin labels readable and in numeric order.

This is a clustered column chart, not Excel’s native Histogram chart. The distinction matters: your table determines the bin boundaries and the chart displays the counts you calculated. On Mac, the charting approach is similar, though ribbon labels and available formatting controls can vary by Excel release. Excel for the web may also present fewer chart-formatting controls than the Windows desktop app; if a particular setting is missing, open the workbook in desktop Excel.

Use percentages when group sizes differ

Suppose Control has 50 records and Treatment has 200. Raw counts will often be higher for Treatment simply because it has more observations. If the goal is to compare distribution shape rather than total volume, make a second table that divides each bin count by the total number of valid observations in that group, then format the results as percentages.

With the count for a bin in F2, the group name in F1, and the source group labels in A2:A11, this formula calculates that bin’s share of its group:

=F2/COUNTIF($A$2:$A$11,F$1)

In the example, the first Control bin contains 3 of 5 observations, so the result is 0.6, displayed as 60% when formatted as a percentage. Apply the same calculation to each bin and group, then chart the percentage table with the same shared boundaries. The percentages for each group should total 100% if the bins cover all included measurements and each observation is assigned exactly once.

Percentages are not automatically the right choice: they answer a different question from counts. If the groups have unequal bin widths, percentages per bin are not directly comparable as measures of distribution density. Use equal-width bins for a straightforward percentage comparison, or explain and calculate density per unit of measurement when unequal widths are necessary.

When to use Excel’s native Histogram chart

For one group at a time, select its numeric values and choose Insert > Insert Statistic Chart > Histogram. You can adjust the horizontal axis by right-clicking it and choosing Format Axis. Under Axis Options, Excel provides controls such as bin width, number of bins, and overflow or underflow bins. These options help explore the distribution without first constructing a frequency table.

For group comparisons, make a separate native histogram for each group only if you can match the bin settings and chart scales. Otherwise, a bar in one chart may cover a different interval from a bar in another, making visual comparisons misleading. Native histograms are also less convenient when you need exact counts in a report table, a specific final-bin rule, or percentage values based on each group’s total. A formula-driven table makes those choices visible and easier to audit.

Common problems and checks

  • A group’s counts add up to less than its record count: check whether the bins cover the full value range and whether the last upper boundary is inclusive. Values outside the limits are not counted.
  • A value appears in two bins or none: use a consistent boundary rule, such as lower-inclusive and upper-exclusive bins, and make only the last bin upper-inclusive.
  • The chart shows the wrong categories: select the bin labels and group-count columns together. Use Chart Design > Switch Row/Column if Excel interprets the layout incorrectly.
  • Counts are unexpectedly zero: check that values are numeric, group labels match the headers exactly, and the formula ranges include the data rows.
  • Bars seem unlike a histogram: a clustered chart separates groups within each bin. Reduce Gap Width, but retain enough visual distinction to compare the series.
  • The groups look very different only because one is larger: chart percentages rather than counts when relative distribution is the question.

Before sharing the chart, verify a few rows against the source data by hand. Confirm that a value equal to a boundary goes into the intended bin, check each group’s total, and label whether the vertical axis shows counts or percentages. Those simple checks prevent common interpretation errors.

Frequently Asked Questions

Can Excel make one histogram with different colors for each group?
Not directly with the standard Histogram chart in the same way a grouped categorical chart works. Calculate counts using shared bins for each group, then plot the table as a clustered column chart.

Should I use a PivotTable to make a histogram by group?
A PivotTable can summarize grouped numeric data, especially if you first create bin labels or use numeric grouping where available. A COUNTIFS table is often more transparent when you need precise shared boundaries and explicit control over which bin includes a boundary value.

How many bins should a histogram have?
There is no universally correct number. Start with a width that makes the measurement scale understandable, inspect whether the chart reveals useful structure, and check that small changes in width do not completely change your conclusion. Explain the chosen width when the analysis is important.

Should I compare counts or percentages?
Use counts to compare the number of observations in each range. Use percentages to compare the relative shape of groups with different sample sizes. State which one the chart shows, because they answer different questions.

Why do my histogram counts not equal the number of records?
Some values may fall outside the first or last bin, be stored as text, or be excluded by a boundary rule. Check the bin coverage, source ranges, group labels, and final-bin formula, then compare the sum of each group’s bin counts with its valid observation total.

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.