How to Create a HISTOGRAM in Excel – Step by Step

It’s a step by step tutorial to create a HISTOGRAM in Excel in different ways in a different version of Excel.

Excel Histogram Chart

A histogram is a column or a bar chart that represents values using a range. Think of a class teacher who wants to present marks of students using a category range (Bins). In the class, she has 50 students and to present the performance of the class, she can use the distribution of students according to their marks using a histogram.

steps to create a histogram in excel all version

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…

  • 2016
  • 2013
  • Mac
  • Pivot
  • Other

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 the FREQUENCY function.

=FREQUENCY(InputDataRange,BinRange)

using freuency function to create a dynamic histogram in excel

? Note: Make sure to 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 the gap width to 0% from “Format Data Series” options.

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

When You Need to Create a Histogram

Creating a histogram in Excel is not rocket science, but you need to aware of the situations where it can be helpful and useful.

1. To Present Large Data

Remember the example of the students? Sometimes it’s hard to present data in a normal chart (Line, Column, or Bar). But with a histogram, you can make bins and present large data in a simple chart.

2. To Understand the Distribution

A histogram not only distributes data into bins but also helps you to understand the distribution. When you look at a below histogram you can understand that most of the students scored between 40 to 80. And 23 students scored above 60.

3. To Help in Decision Making

By using a histogram you can easily decide the students who need to work on their performance. Once you know the numbers in distribution you can separately focus on each distribution.

What are Bins?

Bin (Bucket) is a range of values in which you distribute the frequency of occurrence. Normally, when someone makes a histogram he/she needs to create bins before but in while making a histogram in Excel you don’t need to create bins.

In the end

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.

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.