How to Create Excel Histogram? (Plot and Modify)

In this Excel tutorial, you will learn how to plot a histogram in Excel. For Excel 2016 or newer versions of Excel, you can directly insert a statistic chart. You can use Data Analysis ToolPak or different functions such as FREQUENCY or COUNTIF and COUNTIFS to do the same task in older versions. Here you will also find some ways to customize your histogram chart in Excel. We’ll use Microsoft 365 in this article.

You can use histograms to visualize the distribution of data and show how values are spread out and concentrated within certain intervals. It is used in fields such as statistics, image processing, quality control, machine learning, finance, biology, medicine, and social sciences.

Download Practice Workbook

Creating Histogram.xlsx

What Is Histogram?

Histogram is a graphical representation of the distribution of numerical data. It is divided into bins or intervals and shows the frequency or count of values within each bin. It provides a visual summary of the data’s distribution and helps identify patterns and outliers.

How to Create Histogram in Excel?

To create a histogram in Excel, there are 5 different ways you can follow. Such as: Inserting a Statistic chart, Using PivotChart tool, Using Data Analysis ToolPak, Applying various Excel functions etc. Here, we have a dataset containing the names and scores of some students. Now, we’ll show you how to create a histogram using these scores in different ways.

1. Create Histogram by Inserting Statistic Chart in Excel 2016

To create a histogram in Excel 2016 or newer versions, you can insert a statistic chart from the Insert tab.

First, select your data.
Then, go to the Insert tab >> click on Statistic Chart >> select Histogram.

If you want to change the gap width in a histogram, open the Format Data Series options by double-clicking any rectangle in the histogram.

Adjusting the gap width setting from the Series Options.

To change bin width, double-click on the bin values to open a new window named Format Axis.

After that, in Axis Options, you’ll find different Bins options.

By Category: You can use this option when you’ve text categories.

Automatic: This option will automatically decide the Number of Bins or Bin Width for your dataset.

Bin Width: You can manually insert your desired bin width from here.

Number of Bins: Use this option to insert the number of bins you want to have.

You can change the Bin width according to your need like below.

Thus, you can create a histogram in Excel.

2. Using Data Analysis ToolPak to Create Histogram in Excel

To use Data Analysis ToolPak to create a histogram, first, you need to enable Analysis ToolPak and then create a histogram by using the Data tab.

To access the Data Analysis tool, press ALT + F + T >> go to the Add-ins tab >> click on Go.

Then, check the Analysis ToolPak checkbox and click OK.

To create a histogram using the Data Analysis ToolPak, go to the Data tab >> click on Data Analysis.
This will open the Data Analysis box.
Select Histogram from that box and click on OK.

Then, insert your desired Input Range, Bin Range and Output Range in the Histogram box.
Lastly, select the Chart Output checkbox and click on OK.

Thus, a histogram will be created.

3. Apply FREQUENCY Function to Make Histogram

You can also use the FREQUENCY function to make a histogram in Excel. Using FREQUENCY function you can count how many times a value is found in a given range of values.

Use the following formula to count the number of values in each bin.

=FREQUENCY(C5:C14,E5:E10)

Now, select the Frequency column >> go to the Insert tab >> click on Column or Bar Chart >> select Clustered Column chart.

To edit the values in X-axis, select the chart and right-click on it.
Then, click on the Select Data option.

Click on the Edit button under the Horizontal (Category) Axis Labels pane in the Select Data Source box.

Select the Bin column as the Axis label range and click on OK.

Finally, you will find your desired histogram in Excel.

4. Make Histogram Using COUNTIF and COUNTIFS Functions

You can also use the COUNTIF and COUNTIFS functions for this purpose. You can see an overview of the COUNTIF function in Excel below.

Click here to enlarge this image.

Use the following formula to find out the count value for the first bin.

=COUNTIF($C$5:$C$14,"<="&E5)

For the rest of the bins, use the following formula.

=COUNTIFS($C$5:$C$15,">"&E5,$C$5:$C$15,"<="&E6)

After that, going through the same steps shown in Method 3 you can insert a Column chart to make the following histogram in Excel.

5. Create a Histogram with PivotChart Tool in Excel

Yes, you can use PivotChart to create a histogram based on the summary of given data.

To create a histogram with PivotChart in Excel, go to the Insert tab >> click on PivotChart.

Then, insert your data range in Table/Range text box and output location in the Location textbox and click on OK.

Drag the Score field into the Axis and Values area.

After that, click on Sum of Score in the Values area and go to the Value Field Settings.

Select Count as Summarize value field by option and click on OK.

Now, to create groups, right-click on any cell from the Row Labels and select Group.

Enter Starting, Ending at and by values and click on OK.

Finally, you will see a histogram has been created.

How to Customize Histogram Chart in Excel?

There are some customizations that you can do in the histogram chart. They are given below:

Organizing by Category: In a histogram, data can be organized by category by assigning each category to a specific bin or interval. Suppose we have a dataset of customer ages in a retail store. We can organize the ages into categories (bins) and create a histogram to visualize the distribution.
Adding, Removing, or Changing Elements: Histogram offers different elements such as axis title, data label, chart title, etc. You can add or change these elements by first selecting the chart and then clicking on the “+” sign.
Defining Bin Width: Defining the bin width in a histogram involves specifying the range or width of each bin that represents a specific range of values. You can do it by determining the size of each bin based on the range of the data.
Creating Bins Automatically: In the histogram chart, you can simply create bin by using the automatic binning option. It automatically determines the number of bins, typically adding four or more bins based on your data in ascending order. If you need, you can adjust the bin width or manually set the number of bins to modify the results.
Creating Overflow and Underflow Bin: Creating an overflowing bin helps identify data points greater a specific value. On the other hand, an underflow bin combines data points below a level. It helps to analyze exceptional or underperforming periods in a histogram chart.
Resizing Chart: You can resize the histogram by dragging it using one of the eight handlebars in Excel.
Formatting Axis Elements: To format axis elements, select any axis and right-click on it. It will open the Format Axis toolbox with multiple axis options.
Removing Space Between Bins: By adjusting the Gap Width option, you can control the spacing between the bins in your Excel histogram chart.

Which Things Should You Remember?

When you are creating a histogram in Excel, make sure to choose the right number of bins or bin width for your data. Having too few bins may oversimplify the information while having too many can make it hard to understand.

Conclusion

In conclusion, histograms are valuable tools for visualizing the distribution of numerical data. This article has shown various techniques for creating and modifying a histogram in Excel. We used Data Analysis ToolPak, different functions, and PivotChart to create histograms. Going through the articles we’ve added here you will also find ways to adjust bin ranges, add vertical lines, and explore different types of histograms such as stacked, probability and cumulative histograms. By using these techniques, you can effectively analyze data patterns and characteristics using histograms in Excel. Hope you find this article helpful and informative.

Frequently Asked Questions

1. How is a histogram different from a column chart?

A histogram is a type of bar chart that represents the distribution of a continuous variable, where the bars touch each other to show the continuity of the data. On the other hand, a column chart displays discrete categories on the horizontal axis and represents individual values as separate columns, allowing for comparison among categories.

2. Are there any keyboard shortcuts for creating a histogram in Excel?

Yes, there are keyboard shortcuts that can help you create a histogram in Excel more efficiently.
Select the range of data for which you want to create a histogram. Press Alt + N to activate the Insert tab. Then, press SA to select the Bar chart type. Finally, select the Histogram chart.

Excel Histogram: Knowledge Hub

<< Go Back to Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!