How to Create Excel Histogram? (Plot and Modify)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Histogram

Download Practice Workbook

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.

Read More: Difference Between Excel Histogram and Bar Graph

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.

Inserting Statistic Chart

  • 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.

Opening Format Data Series options by double-clicking any rectangle

  • Adjusting the gap width setting from the Series Options.

Changing Gap Width

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

double-clicking on the bin values

  • 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.

Changing Bin Width

  • Thus, you can create a histogram in Excel.

Created Histogram Inserting Statistic Chart in Excel

Read More: How to Create a Histogram in Excel with Bins

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.

Opening Excel Options box

  • Then, check the Analysis ToolPak checkbox and click OK.

Adding Analysis ToolPak in Excel

  • 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.

choosing histogram in Data Analysis box in Excel

  • 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.

Histogram box

  • Thus, a histogram will be created.

Created Histogram Using Data Analysis Toolpak in Excel

Read More: How to Make a Histogram in Excel Using Data Analysis

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.

Using FREQUENCY function to make histogram in Excel

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

inserting column chart to create a histogram

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

editing chart data

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

Select Data Source box

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

inserting Axis Label

  • Finally, you will find your desired histogram in Excel.

Histogram created using FREQUENCY function

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.

Syntax of COUNTIF function

Click here to enlarge this image.

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

using COUNTIF function

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

Using COUNTIFS function

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

Inserting column chart

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.

Inserting PivotChart

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

Create PivotTable box

  • Drag the Score field into the Axis and Values area.

PivotChart Fields toolbox

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

changing field value settings

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

field value settings box

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

Grouping values

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

Grouping box

  • Finally, you will see a histogram has been created.

Created histogram using pivot chart in Excel

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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.


Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF