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 :

Creating a Histogram Using Data Analysis Tool pack

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.

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.

Steps to create a Histogram chart in Excel 2016:

Select the entire data set.

Click the Insert tab.

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

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.

Let’s start its
customization process:

Customize this chart by right-clicking on the vertical axis and selecting Format Axis.

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

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.

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.

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.

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.

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:

Click the File tab and then select ‘Options’.

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

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

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

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

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.

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:

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.

In the ‘Data Analysis’ dialog box, select Histogram from the list

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.

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

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.

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.

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.

Important:

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.

About Deepak Jha

I am a Marketer & passionate Excel practitioner. I am using MS excel from past 6 years in my daily routine work, I have tried many things to automate my excel work so i thought why not I help others to automate their work through excel so I started You Learn Excel blog. Just to make things easier for others.