How to Insert a Histogram Chart in Excel

Creating a histogram in Excel is one of the best ways to quickly understand the shape and spread of your data. Instead of just looking at endless rows of numbers, a histogram groups them into ranges, showing you where most of your values fall. This article will guide you step-by-step through creating a histogram in modern Excel, customizing it for clarity, and even using the older method if you're on an earlier version.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is a Histogram, Really? A Quick Refresher

You might think a histogram looks just like a standard bar chart, but they tell very different stories. A bar chart is used to compare distinct, separate categories, like monthly sales by product or the number of support tickets handled by each team member.

A histogram, on the other hand, shows the frequency distribution of a continuous set of data. Instead of categories, it groups a range of numerical data into bins (or intervals) and plots the number of entries that fall into each bin. This reveals the underlying shape of your data set.

For example, imagine you have the test scores for a class of 50 students. A histogram could tell you how many students scored between 90-100, 80-89, 70-79, and so on. You'd instantly see if most students scored high, low, or were clustered in the middle.

In short:

• Bar Charts: Compare categorical, distinct items.
• Histograms: Show the distribution of a set of numerical data.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Why Bother Creating a Histogram in Excel?

Histograms are an essential tool for data analysis because they turn a confusing list of numbers into a clear visual pattern quickly. Here’s what a histogram helps you do:

• See the Shape of Your Data: You can learn a lot by looking at the histogram’s overall shape. A symmetrical, bell-shaped curve indicates a normal distribution. If the tail stretches to the right, your data is "right-skewed" (like population income). If it stretches left, it's "left-skewed" (like retirement age).
• Identify the Center and Spread: It's easy to spot the central tendency - the most common range of values - where the tallest bars are. You can also get a feel for the data's spread, or how much it varies.
• Spot Outliers and Gaps: Are there one or two bars sitting far away from the others? Those could be outliers - unusual data points worth investigating. Similarly, large gaps between bars can indicate missing data or points that don't fit the expected pattern.

Step-by-Step: How to Make a Histogram in Excel (Modern Versions)

For those using Excel 2016, 2019, Microsoft 365, or newer, creating a histogram is a built-in feature that takes just a few clicks. It's incredibly straightforward.

For our example, let's use a list of website user session durations (in seconds) that we want to analyze.

1. Enter and Select Your Data

First, make sure all your numerical data is in a single column or row without any blank cells in the middle. Click and drag to select your entire data range. In our example, we'll select all the cells containing session durations.

2. Go to the ‘Insert’ Tab

With an active data selection, navigate to the Insert tab in the Excel ribbon.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

3. Choose the Histogram Chart

In the 'Charts' section of the ribbon, click the 'Insert Statistic Chart' icon (it looks like a small blue histogram). From the dropdown menu, select the first option under Histogram.

Excel will instantly generate a histogram for you. It automatically analyzes your data and creates its own "bins" - the numerical ranges you see on the horizontal axis.

That’s it! The basic chart is done. But often, the real value comes from a little customization.

How to Customize Your Excel Histogram

Excel's default might not be perfect for telling your data's story. For example, the auto-generated bins might be too broad or too narrow. Customizing things like the bin size and chart labels is where you can make your chart truly insightful.

Adjusting the Bins (The Most Important Part)

"Bins" are the intervals that your data is grouped into. Fine-tuning these can completely change the way you see your data.

To start, double-click on the horizontal axis (the X-axis with the number ranges) of your chart. This will open up the Format Axis pane on the right side of your screen.

Let's explore the key bin options under Axis Options:

• By Category: Leave this unchecked for a numerical histogram.
• Automatic: This is the default setting where Excel chooses the bin sizes for you.
• Bin Width: This option gives you full control. Here, you can define the size of each range. For our session duration data, if we set the bin width to 60, each bar on the chart would represent a 60-second interval (0-60s, 60-120s, etc.). This is often the most useful setting for analysis.
• Number of Bins: This lets you specify how many total bars you want in your analysis. For instance, if you enter 5, Excel will do the math and split your data range into five equal bins. Use this when you want to view a high-level summary.
• Overflow Bin: Extremely useful! Check this box and enter a number to group all values above that number into a single last bin. For instance, you could create an overflow bin for ">300" to capture all very long sessions in one group.
• Underflow Bin: The same concept, but for the other end. Check this box to group all values below a certain number into a single first bin.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Improving Chart Readability

A few more tweaks will make your histogram easy for anyone to understand.

• Add a Title and Labels: Your chart automatically gets a placeholder title. Click on it to write something descriptive, like "Distribution of User Session Durations." To add axis titles (highly recommended!), click anywhere on your chart, then click the green "+" icon that appears in the top-right corner. Check the box for Axis Titles. You can then label your vertical (Y) axis as "Number of Sessions" and your horizontal (X) axis as "Duration in Seconds."
• Adjust Colors and Style: Click on your chart to bring up the Chart Design tab in the ribbon. Here you can find pre-made styles and options to change colors. To change the bar color, right-click on a data point and choose Format Data Series, then select the color you want.

Creating Histograms in Older Versions of Excel

If you're using Excel 2013 or earlier, you'll need to use the "Analysis ToolPak" add-in to create histograms. Here's how:

Enable the Analysis ToolPak

Go to File > Options > Add-Ins. In the "Manage" box, select Excel Add-ins and click Go. Check the box for Analysis ToolPak and click OK. You'll only need to do this once.

Create a Histogram

Once the ToolPak is enabled, go to the Data tab and click on Data Analysis. Choose Histogram from the list and click OK. Select the data range for your input range and bin range. Click OK again to generate the histogram.

Final Thoughts

Creating a histogram in Excel is a straightforward process that provides valuable insights into your data. Whether using modern features or older methods, histograms help you visualize distribution, identify patterns, and spot anomalies with ease. With a bit of customization, they become even more powerful, allowing you to tailor the chart to your specific analysis needs. By following these steps, you'll be able to effectively communicate your data's story in no time.