Skip to main content

Two Methods to Building Histogram in Excel

Two Methods to Building Histogram in Excel: Building charts on Excel is a great way to show your data and create a visual impression. This is a better approach than showing tables that are full of numbers. And one of the most popular types of graphs is the histogram.

A histogram is a column chart. The column represents frequencies of each category or grouping. In advanced statistics, it is a representation of what is commonly called a bell curve, which shows whether the data spread is statistically closer to the 6-sigma level, skewed to the right, or skewed to the left.

But hey, you do not have to be a statistician to do this. All you need is an Excel spreadsheet, and we can show you how to make a histogram.

In this example, we will use a university. Let us say that you want to know how many students are within the age brackets of 16-18, 19-20, 21-22, 23-24, and 25-26.

METHOD 1: Doing it Manually

STEP 1

The first thing you need to do is to have your data set up. An example is shown below for 20 students with varying age. If there is no Histogram tool in your Excel version, you must do it the long way like what is shown below.

BIN means grouping or categories.

NameAgeBIN
Student 11616 to 18
Student 21816 to 18
Student 31919 to 21
Student 42522 to 24
Student 52422 to 24
Student 62625 to 27
Student 72322 to 24
Student 82222 to 24
Student 91716 to 18
Student 101816 to 18
Student 111816 to 18
Student 121919 to 20
Student 132222 to 24
Student 142119 to 21
Student 152322 to 24
Student 162322 to 24
Student 172422 to 24
Student 182119 to 21
Student 191716 to 18
Student 201919 to 20

 

STEP 2

The next thing to do is to count the number of times each data point appeared. You can count this manually, or use the countif function to make it easier.

 

If you want it to be automatic, you can also use the pivot function to make your data appear like this one.

Age BracketCount
16 to 186
19 to 213
22 to 248
25 to 271

 

STEP 3

The next step is to highlight the data, click Insert, then click the Clustered Column.

excel consultant

 

As you can see, now you have a histogram. The only thing left to do is to make adjustments on the aesthetics. You can change the name of the graph by clicking on it. You can also change the column color, remove the lines, add a gradient background, and so much more.

 

METHOD 2: Use the Instant Histogram Chart

To be able to do this, you must have the Analysis Tool Pak in your Excel.

 

STEP 1

Click on File > Options > Add-Ins. You will get to this screen or dialog box.

excel expert

STEP 2

Click on Analysis Tool Pak then click OK.

excel experts near me

 

 

STEP 3

Arrange your data and create a BIN range. This is the last digit in each of your age brackets.

 

NameAgeBIN
Student 11618
Student 21821
Student 31924
Student 42527
Student 524
Student 626
Student 723
Student 822
Student 917
Student 1018
Student 1118
Student 1219
Student 1322
Student 1421
Student 1523
Student 1623
Student 1724
Student 1821
Student 1917
Student 2019

 

 

STEP 4

Click on Data > Analysis Tool Pak > Histogram > then OK.

excel experts near me

 

STEP 5

Choose the Input range. This is the age. Next, choose the BIN; this is the grouping. Then click OK, and you will get the data below.

excel experts near me

 

BinFrequency
186
215
247
272
More0

 

STEP 6

The last thing to do is to create the same histogram in the same way we did it in Method 1.

 

Summary

Easy, right? Some Excel versions allow you to just highlight the entire data set, click Insert, click Histogram, and it will automatically create a histogram chart for you.

But if it doesn’t, now you know how to make one from scratch.

See our Blog page for other tutorials,

 

HOW’S YOUR DATA WORKING FOR YOU?

Have you ever wonder how to blow up your business’ productivity?  You want a data expert that identifies your problems and make you look good!

If you’re still struggling to unlock all your data potential has to offer? Get in touch with us — we’d love to talk data with you.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.