Histogram in Excel

How Histogram Works in Excel? (Definitive Guide)

How Histogram Works in Excel? (Definitive Guide)

Histogram in Excel

What is Histogram Chart?

Histogram  was first used by Karl Pearson and It is a tool which helps to understand the data points and its distribution among different groups.

In layman language, this chart divides the data into multiple range groups with same interval i.e. 1-5, 5-10… and then plot the data into these groups to find the distribution among these group. So that you may understand, what are the major areas which has large impact or your major data fall. This helps the user to understand the nature of your data.

Histogram is different from bar chart as bar charts works with two data variables however Histogram works with one data variables (numeric). The intervals/groups used in this graphs are called bins/buckets and this bins are created with same interval or width. These bins are consecutive and non overlapping intervals.

This Histogram will look like as below:

Histogram Charts in Excel

Histogram Excel – Things To Know

  • Histogram works with one data range/variable and group the data into multiple bins. Bins basically can be called as Range Interval i.e. 5 to 10, 10 to 15, 15 to 20…
  • Histogram helps to estimate the probability distribution of data into different categories
  • This is different from bar chart and Excel has inbuilt chart for this. There are different ways to use this in Excel depending on the Excel Version (Detailed Guide Below)
  • Histogram create the group with same width and intervals. It overlaps each other, so there is no gap in columns
  • Helps to analyze the data structure and its occurrence into different categories

How to make Histogram Chart in Excel?

There are different ways to create Histogram chart in excel depending on the excel version you are using:

Histogram Options For Excel:

  • Create Histogram in Excel 2016/Above versions or Office 365
  • Create Histogram in Excel 2010/2013 or prior versions

Create Histogram in Excel 2016 and above versions

Suppose we have sales number data for sales rep against the target of 100. Now we want to know the common target achieved in different categories

Histogram Data in ExcelSteps to Create Histogram Chart in Excel 2016 and above versions

Select your excel Data and Go To Insert Menu on the top Excel Menu

 – Once you click on Histogram chart, you will see a chart on your window. Right click on the horizontal Axis (x-Axis) and click format Axis:

Format Axis Excel Histogram – Once you select format Axis, you can change the chart parameters i.e. Bin Width, Bin Numbers, Overflow Bin, Underflow Bin etc

  • Category: If you want to show your data category wise instead of Group intervals. This gonna help you. This is similar to column chart
  • Bin Width: It helps the user to define the bin Width which is an interval for each Bin. So for example, you want to group the data by interval size 10. This will look like as 5-15, 15-25, 25-35 etc. You can change the group size by using this option
  • Number of Bins: This helps the user to create the number of bins as when you insert a histogram chart, it automatically creates 3-4 bins however you can change the bin numbers by using this option
  • Number of Bins: This helps the user to create the number of bins as when you insert a histogram chart, it automatically creates 3-4 bins however you can change the bin numbers by using this option
  • [Overflow bin]: This is an optional feature which you may use to restrict the last bin to consolidate all data points if flows after defined value i.e. 75<
  • [Underflow bin]: This is an optional feature which you may use to restrict the first bin to consolidate all data points if falls before user defined value i.e. <10

Histogram in Excel

Create Histogram in Excel 2013/Prior Versions

For this we need to install an ADD INN which is available along with MS Office installation “Analysis ToolPAK”. You may learn How to install Excel ADD INN in order to install “Analysis ToolPAK”.

Excel Anaysis ToolPAKOnce you activate the above ADD INN. Then you will be able to see this data analysis option under “Data” menu and click on Data Analysis

Now you select “Histogram” and click Ok. Now you need to create bin starting point with equal intervals. See below:

Histogram

Input Range: Provide range for the Data Points

Bin Range: Provide range for frequency or intervals

Click to Labels

Output Options: Select Output Range (Give cell value) or New Worksheey Ply or New Workbook

Click to Chart Output

Click to OK

Histogram

Histogram Chart will be prepared. If we see the summary, Data points are categorized according to the Bins provided. For Example:

– Data point which is less than or equals to 10 has total 1 frequency(Yellow Highlighted)

– Data points which are less than or equals to 30 has total 3 frequencies (Light Orange)

– Data points which are less than or equals to 50 has total 6 frequencies (Blue)

– Data points which are less than or equals to 70 has total 7 frequencies (Light Green)

– Data points which are less than or equals to 90 has total 2 frequencies (Orange)

– One Data point, not falling within any Bin/Categories i.e. 91 is shown under “More” section

Histogram

Hope you liked this article !!

Subscribe our blog for new amazing excel tricks.

Click to below for some more interesting tricks and learning:

Please leave your valuable comments in Comments section:

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.

Leave a Reply

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

Are you a Data Enthusiast?

Join us for a ride on your data and automate your stuffs