How to Make a Histogram in Excel (Step-by-Step Guide)

Histogram is basically an data analysis tool which is available in Excel and you can use it whenever you are doing any kind of data analysis for available date set.

In simple words , A Histogram is Column Chart which is a built-in option of MS Excel. In Excel 2016 it’s available as a chart, but if you are using Excel 2013, 2010 or prior versions then you can use one of these methods :

  1. Creating a Histogram Using Data Analysis Tool pack
  2. Creating a Histogram using FREQUENCY Function

I promise you that I won’t be that hard, you just need to follow the below given methods.

Let’s jump in the process of creating histogram in excel:

Creating a Histogram in Excel 2016

If you are using the excel version 2016 then it will be few click away to create histogram for available data set as it is a built-in chart in Excel 2016.

Don’t worry if you are using Excel 2013 or prior version, as next two ways will help you in creating the Histogram in excel 2013 or prior version of excel.

Let’s assume we have below given data set and we have to create a Histogram chart for the below data set.

Students-Marks-to-create-histogram-in-Excel-2016

Steps to create a Histogram chart in Excel 2016:

  1. Select the entire data set.
  2. Click the Insert tab.
Insert-Tab-in-Excel-Ribbon

3. In the Charts group, click on the ‘Insert Static Chart’ option.

Insert-Static-Chart-Option-in-the-Ribbon

4. In the Histogram group, click on the Histogram chart icon.

The above steps would insert a histogram chart based on your data set (as shown below).

As we have the Histogram chart for our available data set, so now we have the option to customize it as per our needs.

Resulting-Histogram-Chart-in-Excel-2016

Let’s start its customization process:

  1. Customize this chart by right-clicking on the vertical axis and selecting Format Axis.
Format-Axis-for-Histogram-Bin-Options

2. This will open a pane on the right with all the relevant axis options.

Bin-Options-in-the-Histogram-Chart-in-Excel-2016

Histogram Chart Customization Options

 Once you have the Histogram chart then you have multiple options to customize it and present it as per your needs. Some of the well-known customization options are as follows:

Histogram Chart Customization by Category

You can customize Histogram charts by using category option, but you have to make sure to use it only when you have text categories.

For example, if you have online store or offline store with multiple products category, Now you want to know the sales data for each product category then you can use the histogram chart option and calculate the sum of sales for each category.

This could be beneficial when you have multiple repetitions in categories, and you want to know the total sum or total count of the categories.

Histogram Chart Customization by Automatic: This is one of the default options in Histogram chart, as when you create your histogram chart for your data set then it automatically creates Bins for you.

In our example it created 4 bins, but its upto us how many bins we want in our chart. We can change this by using the ‘Bin Width/Number of Bins’ options.

Histogram Chart Customization by Number of Bins

You can use this option to decide the number of Bins you want in your chart. If you specify 2 Bins or 6 Bins or 10 bins then it will automatically create your chart in your mention Bins.

Specifying-Number-of-Bins-in-histogram-chart

You have to careful that you only define either number of bins or bins width, don’t define both options.

Histogram Chart Customization by Bins Width

This option will help you in defining the width of Bins, you can decide how big you want your Bin.

Specifying-Bin-width-in-histogram-chart

Let’s create a chart and define Bins Width to 20, so it will create Bins as 36-56, 56-76, 76-96, 96-116 and it will look big as compare to normal bins.

Histogram Chart Customization by Overflow Bins

This option will give you the customization option through which you can define certain value and above that value all other value will be clubbed together in a histogram chart.

Overflow-Bin-Value-in-Histogram-Excel-2016

In our student data set, if we want to know that how many students have scored more than 60 or 70 or may be more then 75 then we can define overflow bin value to 75 than we will get similar chart as shown below.

Histogram Chart Customization by underflow Bins

This is similar to Overflow Bin but works opposite, underflow bin will help you in finding the low scored students.

Let’s say we want to know how many students have scored less then 40 percent, so once we define the underflow bin to 40 then it will show chart as given below.    

Underflow-Bin-Value-in-Histogram-Excel-2016

If you can want then you can further customize it by changing the title, removing grid lines, changing colors, etc.

Finally, you have your Customized Histogram Chart in Excel 2016, now let’s explorer other methods of creating the Histogram Charts.

Creating a Histogram Using Data Analysis Tool pack

I call it as all-rounder method of creating histogram chart, as it works with MS excel 2016, 2013, 2010 and all the prior version of excels.

If you ask me then I will suggest using Built in Histogram chart if you have Ms excel 2016.

To create a histogram chart using Data Analysis tool pack, you first need to install the Analysis Toolpak add-in in excel.

This add-in will enable you to quickly create the histogram chart in excel by taking the data and data range as inputs.

Install Data Analysis Tool Pack in Excel

To install the Data Analysis Toolpak add-in:

  1. Click the File tab and then select ‘Options’.
Histogram-in-Excel-Options

2. In the Excel Options dialog box, select Add-ins in the navigation pane.

Histogram-in-Excel-Addins

3. In the Manage drop-down, select Excel Add-ins and click Go.

Histogram-in-Excel-Excel-Addins

4. In the Add-ins dialog box, select Analysis Toolpak and click OK.

Histogram-in-Excel-Addins-dialog-box

This would install the Analysis Toolpak and you can access it in the Data tab in the Analysis group.

Histogram-in-Excel-Data-Analysis-Tab

Creating a Histogram Chart in Excel using Data Analysis Toolpak

After you install Data Analysis Tool Pak, now you can histogram chart in excel.

Let’s say we have a data set as shown below. As we are taking the same data set that we have used in our above examples, so it won’t be a complicated data for us.  

Students-Marks-to-create-histogram-in-Excel-2016

Now to create Histogram chart by using this data set, we need to define the data intervals in which we want to find the data frequency. These can be called as Bins.

You need to specify these bins separately in an additional column as shown below:

Create-Histogram-in-Excel-bins

As we have all our data set so now let’s create the Histogram chart.

  • Click the Data tab.
  • In the Analysis group, click on Data Analysis.
Histogram-in-Excel-Data-Analysis-Tab
  • In the ‘Data Analysis’ dialog box, select Histogram from the list
Histogram-in-Excel-Data-Analysis-Dialog-Box
  • Click OK.
  • In the Histogram dialog box:
    • Select the Input Range (all the marks in our example)
    • Select the Bin Range (cells D2:D7)
    • Leave the Labels checkbox unchecked (you need to check it if you included labels in the data selection).
    • Specify the Output Range if you want to get the Histogram in the same worksheet. Else, choose New Worksheet/Workbook option to get it in a separate worksheet/workbook.
    • Select Chart Output.
  • Click OK.
Histogram-in-Excel-Histgram-dialog-box

You will have the frequency table and chart in your specified location.

Histogram-in-Excel-frequency-table

Note: If you create Histogram without defining the Bins then it will automatically create the bins and use your provided data set.

Creating a Histogram Chart in Excel using FREQUENCY Function

The above two methods would give you the histogram charts but what if we have dynamic data set??

Now we will create dynamic histogram by using the FREQUENCY functions.

Let’s take the same old data set that we have used for this tutorial, now we need to create the bins to show the frequency.

Create-Histogram-in-Excel-bins

Here is the function that will calculate the frequency for each interval:

=FREQUENCY(B2:B41,D2:D8)

Note: This is not a simple formula, so don’t just use ENTER. You need to use Control + Shift + Enter.

Histogram-in-Excel-frequency-function

Follow These Simple steps to get the correct data results:

  • Select all cells adjacent to the bins. In this case, these are E2:E8.
  • Press F2 to get into the edit mode for cell E2.
  • Enter the frequency formula: =FREQUENCY(B2:B41,D2:D8)
  • Hit Control + Shift + Enter.

Now the results we got; we can create the histogram chart for our data set.

Histogram-in-Excel-chart

Important:

  1. This is an Array data set, so you can’t delete a part of the array. If you still want to delete, then you have to delete all the cells.

These are the ways through which you can create Histogram in excel, if you have Excel 2016 then use Built-in option and if you have prior version or in 2016 as well you can use other two methods.

You May Also Like the Following Excel Tutorials:

Leave a Reply

Your email address will not be published. Required fields are marked *