How to Create a HISTOGRAM in Excel – Step by Step

I’m sure you want to become better in data analysis.

Right? Yes? I knew this.

And that's why, I want you to learn to create a histogram in Excel…

…using simple steps.

If you see a histogram first time, it looks like a normal column chart which we use in our spreadsheets all the time.

steps to create a histogram in excel all version

But the idea of creating a histogram is different and in this guide, I’ll be sharing with you everything you need to master this chart.

So stay with me for the next few minutes…

Definition

When to use it?

Why You Need to Know About Bins Before You Create Your First Histogram

How to Make a Histogram in Excel - In all the Versions

A histogram is a specific statistics chart, and for this, you need to follow different steps in each version of the Excel.

how to create histogram in excel stap by step guide

Here in the post, I’ve described about making a histogram in the following ways:

Let's kick it off.

1. Steps to Make a Histogram in Excel 2016/Office 365

If you are like me, using Excel 2016 I've got a have good news for you…

…and the good news is, there is an inbuilt option to create a histogram in this version of Excel.

In this part of the post, I’ll share with you how can you create this awesome chart in your spreadsheet.

employee data to create a histogram in excel

And now you need to create a histogram which should show the number of employees in a particular period range (Bin).

Follow these simple steps:

  • First of all, select the data and go to the Insert Tab Charting Insert Statistic Charts Histogram Histogram, to insert the chart.
go to data tab to create a histogram in excel 2016

In reality, this is the only step you need to follow, your chart is there in your spreadsheet.

insert excel histogram from data tab charts

You can delete the chart title and grid lines if you want.

excel histogram 2016 without chart title gridlines

Important Options Your Must Know to Use

2. Steps to Create a Histogram Chart in Excel 2013 or Lower Versions using Data Analysis Tool Pack

If you are using Excel 2013 or a lower version then you need to follow different steps to create this chart.

There is ADD-IN which you need to install, "Analysis Tool Pack".

It comes with your Excel but you need to install it…

…so let’s learn to install it (I’m using screenshots for the Excel 2016 but the steps are same in all the Excel versions) and then we’ll get into making a histogram.

Steps to Install Data Analysis Tool Pack

  • First of all, click on the file tab and open “Excel Options”.
open file tab to open excel option to install analysis tool pack to create histogram
  • Now from here, go to Add-In and open “Excel Add-Ins”.
open excel addins to install analysis tool pack to create histogram
  • And, tick-mark the “Analysis Took Pack” and click OK.
tick mark to install analysis tool pack to create histogram

Once you click OK, it will process for few seconds and you’re ready to go. You’ll have a “Data Analysis” button in the Data tab.

data analysis button to create histogram

Before you create a histogram using “Analysis Took Pack” you need to create Bins.

Yes, manually. but it’s a simple thing, you know.

Here we need bins like below or whatever you want to make.

create bins to create histogram in excel 2013

Now let’s follow these simple steps to insert the histogram and make sure you have this data file with you to follow along.

  • First of all, go to the data tab and click on “Data Analysis”.
click on data anlysis button to create histogram in excel 2013
  • Now, from the data analysis menu, select “Histogram” and click OK.
select histogram from data analysis to create histogram
  • At this point, you have histogram window just like below and from this window, you need to specify the following details:
define input rang and bin range to create a histogram in excel 2013
  1. Input Range: The range where you have employee years with the company (Make sure to only select the values without heading).
  2. Bin Range: The range where you have bins, yes the one we have just created (Make sure to only select the values without heading).
  3. Output Option: Select the “New Worksheet” for the output and tick mark the “Chart Output” for the chart.
  • In the end, click OK.

Once you click OK you’ll have a new worksheet with a chart and a frequency.

your histogram in excel 2013 is ready

3. Creating a Histogram in the Excel for Mac [2011]

To create a histogram in the Mac version of Excel [2011] we need to install “Analysis Tool Pack” as well.

Activating “Analysis Tool Pack” in Excel Mac

Follow these simple steps to activate “Analysis Tool Pack” in Excel Mac

  • First of all, go to Tool Menu and click on “Excel Add-Ins”.
activate analysis tool pack in excel mac to create a histogram in mac
  • Now from the add-in window, tick mark “Analysis Tool Pack” and click OK.
tick mark analysis tool pack to create histgram in excel mac
  • This will instantly add the “Data Analysis” button into the Data Tab.
data analysis button to create histogram in excel 2011 mac

Now the next step.

Step to Create a Histogram [Mac Excel]

Before you create your chart, make sure to create the bins as we have made in the above method.

And, if you don’t know about the Bins, make sure to read about them in the above section of this post.

  • First of all, go to the data tab and click on “Data Analysis” button.
  • From the data analysis window, select “Histogram” and click OK.
select histogram to create histogram chart in excel 2011 mac
  • Once you click OK, you’ll have the histogram dialog box where you need to enter the following:
input values range and bin range to create histogram in excel 2011 mac
  1. Input Range: The range where you have employee years with the company (Make sure to only select the values without heading).
  2. Bin Range: The range where you have bins, yes the one we have just created (Make sure to only select the values without heading).
  3. Output Option: Select the “New Worksheet” for the output and tick mark the “Chart Output” for the chart.
  • After that, click OK.

Once you click OK, it’ll insert a new worksheet with the frequency table and a histogram.

final histogram in excel mac version

Important Points You Need to Understand when you are using "Analysis Took Pack" to Create a Histogram in Excel

  • The first bin includes lower than value from itself and rest of the bins includes the lower than values from itself, and greater values the from the previous bin.
how bins work wneh you create a histogram in excel using analysis tool pack
  • Apart from the bins you create, it adds a extra bin with a name of "More" to show values more than the last bin. Even if you don't have a value greater than the last bin it adds "More" as a bin.
more bin when you create a histgram in excel using analysis tool pack
  • "Analysis Took Pack" creates a chart which is not dynamic. When you add a new value in the main data it will not update it, so you need to create a new chart.
  • If you skip specifying the bins, it will automatically choose bins and creates the chart.
when you create histogram in excel using analysis tool packand don't specify bin it create bins automatically
  • When you create a histogram using "Analysis Took Pack" you can't undo it, you need to delete it and create a new one, if you want to make changes.

Can I Create a Histogram in Some Other Ways?

The methods which I have shared above are standard methods to create a histogram…

…but you know, in Excel, there’s always more than one possibility to do something…

…and I’m always curious to know if we can do something out of the box.

Let’s do something like this.

Dynamic Histogram using FREQUENCY Function

If you create a histogram using “Analysis Tool Pack” it won’t be a dynamic one.

But…

You can create a dynamic histogram by using FREQUENCY function.

=FREQUENCY(InputDataRange,BinRange)

using freuency function to create a dynamic histogram in excel

📌 Note: Make sure enter this formula as an array formula by using Alt + Shift + Enter.

After that, you just need to insert a normal column chart using the table we have just created.

once you create table with frequncy formula simply insert a cloumn chart to create a histogram

And don't forget to change gap width to 0% from “Format Data Series” options.

make gap width zero to make a histogram in excel using frequency function

Just Forget Everything and Create a Histogram using Pivot table and Pivot Chart

I have found a simple way to create a histogram by combining pivot table and pivot chart.

The below chart is a pivot chart, can you believe it?

create a histogram in excel using pivot chart and pivot table
  • First of all, you need to create a pivot table (or you can also create a pivot table and a pivot chart in one go).
  • For this go to Insert Tab ➜ Charts ➜ PIvot Chart ➜ Pivot Chart and Pivot Table.
  • In the “Create PivotTable” dialog box, click on the “New Worksheet” and click OK.
create pivot table window to create a histogram using pivot table and pivot chart
  • At this point, you have a blank pivot table and pivot chart in your worksheet.
a blank pivot table to create a histogram in excel
  • The next we need to add values, so now, add the column “Employment with Company (in years)” to rows area and into values as well.
add values and row field into the pivot table to create a histogram
  • Now, for values area, open the “Value Field Setting” and select “Count” instead of “Sum”.
select count for the value field to insert a histogram in excel using pivot table
  • After that, go to the pivot table and right click on the “Row Label” and click on “Group”.
  • In “Group By” window, enter 5 in the “By” and click OK.
select the group to create a histogram in excel using pivot table

Now…

…you need to do a bit of customization in the chart to make it a perfect one.

  • Select the data series and open data series options. From here, make the “Gap Width” 0.
make gap width zero for the pivot chart to make a histogram in excel
  • After that, remove the grid lines and chart title (if you want).
  • Click on the filter button and “Hide All”.
hide all the filters from your pivot chart histogram
  • In the end, right-click on the right axis and open ‘Format Axis“.
  • In the format axis options, go to Axis Options ➜ Axis Options ➜ Units and enter 1 in the major and 0 in the minor.
makes change to majon and minor axis to create a perfect histogram using gpivot chart in excel

Boom! your histogram is ready.

your histgram using a pivot chart and pivot table is ready

Conclusion

If you are using Excel 2016 then there is a simple one-click way to create a histogram as it’s there already.

Otherwise, you can use “Analysis Tool Pack”, but you need to install it first to use it.

Well, if you ask me, my favorite method to make a histogram is by using a pivot chart as it’s way more dynamic and gives you more options to control the chart.

But now you 👉 have to tell me one thing.

Which method is your favorite?

Make sure to share your views with me in the comment section, I'd love to hear from you. And please 🙏, don’t forget to share this post with your friends, I am sure they will appreciate it.

Content Protection by DMCA.com
2018-11-16T06:13:19+00:00

Leave A Comment