How to Create and Customise Histogram Charts in Microsoft Excel

When analysing numerical datasets—such as exam scores, customer transaction values, delivery times, or sensor readings—understanding how data distributes across intervals is essential for statistical insights. While traditional column charts plot individual records against categories, a Histogram automatically groups continuous numerical data into discrete frequency bins (intervals). Microsoft Excel provides a native statistical Histogram chart tool that automates bin calculation while offering complete control over bin widths, bin counts, and outlier thresholds.

What Is a Histogram Chart in Microsoft Excel?

In data analytics, a histogram represents the probability distribution of a continuous variable. The horizontal X-axis represents ranges of values called bins (such as [0–10], (10–20], (20–30]), while the vertical Y-axis measures the count or frequency of observations that fall inside each interval.

Unlike standard bar charts, adjacent bars in a histogram touch each other to emphasise continuous numerical ranges rather than distinct nominal categories.

How to Insert a Native Histogram Chart in Excel

Creating a histogram in modern versions of Microsoft Excel (Microsoft 365, Excel 2021, and Excel 2019) requires only raw numerical data:

1. Open your spreadsheet and highlight the single column of numerical values you wish to analyse (including the column header).
2. Navigate to the top ribbon and click the Insert tab.
3. In the Charts group, click the Insert Statistic Chart icon (the blue bar chart with a bell curve silhouette).
4. Under the Histogram section, select the primary Histogram chart option.
5. Excel will instantly generate a histogram on your worksheet, automatically calculating initial bin ranges using Scott’s normal reference rule.

Customising Bins: Width, Count, and Category Options

While Excel’s automatic binning provides a quick overview, statistical analysis usually requires tailored bin intervals. To customise the horizontal axis bins:

1. Double-click the horizontal (X) axis of the chart, or right-click the axis and select Format Axis.
2. The Format Axis task pane will open on the right side of your Excel window.
3. Under Axis Options (the bar chart icon), locate the Bins configuration section:
   • By Category: Used if your data contains text groupings alongside values.
   • Automatic: Allows Excel to recalculate bin counts dynamically as data rows change.
   • Bin Width: Enter a specific numerical interval range for each column. For instance, entering 10 groups values into clean increments of ten (e.g., 50–60, 60–70, 70–80).
   • Number of Bins: Specify the exact total count of bars displayed. Excel will divide the range (maximum value minus minimum value) equally across the chosen number of intervals.
4. Select either Bin Width or Number of Bins to fine-tune your distribution layout.

Setting Overflow and Underflow Bins for Outliers

Real-world datasets often contain long tails or extreme outliers that skew visual charts. You can cap extreme values using underflow and overflow bins:

1. Inside the Format Axis pane under Axis Options, check the box labelled Overflow bin.
2. Enter an upper threshold limit (for example, 100). Any observation greater than 100 will aggregate into a single final bar labelled > 100.
3. Check the box labelled Underflow bin.
4. Enter a lower threshold limit (for example, 20). Any value equal to or less than 20 will consolidate into an opening bar labelled <= 20.
5. This isolates extreme anomalies without cluttering the core distribution pattern.

Formatting Data Labels and Visual Appearance

To make your histogram publication-ready for business reports or presentations:

• Add Data Labels: Click the chart, tap the green + (Chart Elements) button in the upper-right corner, and check Data Labels. The exact count of records appears above each bar.
• Adjust Bar Gap Width: In histograms, bars typically feature narrow gaps. Right-click any bar, select Format Data Series, and adjust Gap Width (typically between 2% and 5% for clear separation).
• Colour Palettes: Select the chart, click the Chart Design tab on the ribbon, and choose from modern accessible colour schemes or apply individual bar fill colours.
• Descriptive Chart and Axis Titles: Click the default chart title and enter a descriptive name (e.g., “Distribution of Customer Response Times (Minutes)”).

Frequently Asked Questions

Why does my Histogram show empty bins?

If you specify a fixed bin width across a sparse dataset, bins with zero observations will appear as empty gaps on the axis, accurately reflecting the absence of data in that range.

Can I create a Histogram from a PivotTable in Excel?

The native statistical Histogram chart type cannot be created directly from a PivotTable data source. To chart PivotTable data, either group the row fields within the PivotTable itself or copy the summary values to a separate range.

What is the difference between a Histogram and a Pareto Chart?

A standard Histogram sorts bins numerically in ascending order from left to right. A Pareto chart sorts frequency bins in descending order of occurrence and overlays a cumulative percentage line.