Creating Excel histograms is easy and convenient when you learn how to make a histogram in Excel by following the methods mentioned in this Excel tutorial.

If you’ve been using Excel bar or column charts to compare categorical data visuallyâ€”such as tracking the highest temperatures over the last 7 days, sales amounts within 15 days, or students’ scores across different subjects â€” have you considered visualizing the distribution of values in a continuous dataset?

Imagine showcasing the number of discounts ranging from 5% to 25% for a product across various retailers or graphically representing the distribution of students with SAT scores falling into specific ranges. In such scenarios, Excel histograms come to the rescue.

Join me as I explain different approaches to plot histograms in Excel, accommodating both older and the latest Excel editions to ensure compatibility with any version you might be using. Let’s make learning Excel histograms an enjoyable and accessible experience!

ðŸ“’ **Read More**: Best Ways To Make a Pie Chart in Microsoft Excel

## What Is an Excel Histogram?

A histogram is a statistical chart that shows how numbers are spread out on the X and Y axis. It has bars showing the count of values within specific ranges or “bins.”

The bars can be arranged horizontally or vertically based on the data and what you want to see. The height of each bar shows how often data falls into that range.

Histograms are useful for seeing shapes and patterns in data. This helps you find trends, outliers, and central tendencies.

People often use histograms in statistics and data analysis to explore data sets. They are easy for any audience to understand.

In short, histograms give a visual summary of data, helping with data storytelling. This makes them valuable for making decisions and getting insights from data.

## Excel Histogram Using Excel Charts

Before you can begin creating a histogram, the dataset should be structured properly. You can create a histogram from almost any dataset that has enough numerical values in continuation. For example, you want to find out how many students scored between 600 to 800, 800 to 1000, 1000 to 1200, 1200 to 1400, and 1400 to 1600 from the following input dataset:

```
SAT Scores:
420, 550, 720, 900, 620, 780, 660, 800, 430, 710,
690, 520, 590, 480, 720, 560, 650, 540, 760, 610,
590, 500, 670, 720, 800, 530, 480, 820, 670, 910,
1050, 1120, 1200, 1300, 1250, 1400, 1150, 1320, 1450, 1500
```

So, if you’ve got a dataset similar to this structure, go to that worksheet and highlight the whole dataset.

Go to **Insert** > **Recommended Charts** to open the **Insert Chart** dialog.

Go to the **All Charts** tab from the **Recommended Charts** tab.

On the left side of the **All Charts** tab, you should see various Excel chart types. Find and click on the **Histogram** option.

On the right side, you should see two histogram chart types. These are **Histogram** and **Pareto**.

Click on the **Histogram** chart type and hit the **OK** button to populate the chart on your worksheet.

After you create the chart, Excel generates bins from the input dataset randomly. You might need a specific range for the bins as explained above in the SAT score example, like, less than 400, 600 to 800, 800 to 100, and so on.

To customize the bin widths, add overflow and underflow bins, etc., **right-click** on any of the bars on the histogram chart. Click on the **Format Data Series** option from the context menu.

On the right side of the worksheet, you should see the **Format Data Series** navigation panel. There, click on the **Series Options** drop-down and choose the **Horizontal Axis** option.

The **Axis Options** configuration will open. There, select the **Bin widths** option and enter the range you’re looking for. In this example, it’s 200, like 600 to 800, 800 to 100, etc.

If your data is exhaustive and you don’t know the bin width, you can enter a value in the **Number of bins** field too. In that case, the **Bin width** option will become inactive.

Now, if you want to create bins for the smallest and largest categories, checkmark the **Underflow bin** and **Overflow bin** checkboxes. Then, enter values for each of these fields, like SAT scores less than 600 in one bin and SAT scores above 1400 in another bin.

Finally, to make the columns more discrete than the default style, go to **Format Data Series** and choose **Series Options**. Change the **Gap Width** value to 5% or more.

## Excel Histogram Using Data Analysis ToolPak

The previous method is only valid for Excel 2016 and newer editions. If you’re on Excel 2007 to Excel 2013 editions, you need to use the Analysis ToolPak add-in.

First, activate the Excel add-in from the **Developer** tab > **Excel Add-ins** > **Add-ins** dialog box. Checkmark the **Analysis ToolPak VBA** add-in and click **OK** to activate the tool.

On the worksheet where you’ve got the dataset, create a list of bin references as shown in the screenshot. It could be any range that you want to see in the final histogram chart.

Now, go to the **Data** tab and click on the **Data Analysis** button inside the **Analysis** commands block.

When you see the **Data Analysis** dialog, scroll down to the **Histogram** option and click **OK**.

Enter the relevant data or values for the following on the **Histogram** dialog:

**Input Range:**The entire dataset you want to use for the histogram plot.**Bin Range:**Select the cell range where you created the manual bin reference earlier.- Checkmark the
**Chart Output**checkbox. - For the
**Output Options**, choose**New Worksheet Ply**.

Hit **OK** on the **Histogram** dialog to populate a frequency table and histogram chart on a new worksheet.

You may want to delete the **More** row on the frequency table to get rid of the same from the histogram.

## Excel Histogram Using the FREQUENCY Function

You can create a manual bin range and populate the number of items falling into each bin using the **FREQUENCY** function. Once you get the number of items for each bin, you can create a bar chart that will work as a histogram.

First, create a column for **BIns** and enter bin range values as shown in the above screenshot.

Now, select the cell to the right of the lowest bin and enter the following formula into it. For Excel 2016 and newer, hit `Enter` to generate the frequencies of the bin values. For Excel 2013 and older, press `Ctrl` + `Shift` + `Enter` to generate the bin frequencies.

`=FREQUENCY(A2:A41,C2:C8)`

Once you’ve got a table like the one in the above screenshot, highlight the table and go to **Insert** > **Recommended Charts**.

Choose the recommended clustered column and click **OK** to generate a bar chart.

Now, **right-click** on any of the columns inside the chart and go to **Format Data Series** > **Serie Options**.

Reduce the **Gap Width** from the default value to **5%** to give the bar chart the look of a histogram chart.

## Conclusions

Making a histogram in Excel is easy if you’re in the latest Excel desktop app. You just need to highlight the input data and call the Histogram chart from the **Insert** > **Change Chart Type** dialog.

However, if you’re using a dated Excel desktop app, you can use the other methods I described above.

If the article helped you to learn a new Excel data visualization skill, comment below. If I missed any method that would add value to our readership, do mention that in your comment.

Setting the Gap Width to 5% is a no no because a histogram must, by definition, have all bars touching. Why so? If you are going to find the area under the curve, the gap will interfere with that and if you are going to overlay a probability density function, that would be compromised, too.

That’s being a bit picky, don’t you think? A 5% gap isn’t much, and it’s the same for all bars. The area under the bars is the sum of the heights times the width of each bin.

The built-in histogram (which I don’t use; don’t get me started) sets a gap width of 0% and uses a thin white border on the bars.

My own preference is a gap width of 0%, a border that uses the bar fill color, then making the bars 50% transparent.