Histogram chart (Table of Contents)
- Histogram in Excel
- Why the histogram chart is important in excel?
- Types/Shapes of Histogram chart
- Where the Histogram chart is found?
- How to Create a Histogram Chart in Excel?
Histogram in Excel
Histogram Chart in excel is a data analysis tool that is used for showing the periodic rise and drop in the data with the help of vertical columns. We can find the Histogram chart option if we are using Excel 2016 but for the older version MS Excel such as 2013 and 2010 we need to find this option in the Data Analysis option which is available under the Data Analysis option.
Uses of Histogram Chart in Excel
A histogram is a graphical representation of the distribution of numerical data. In a simpler way, A histogram is a column chart that shows the frequency of data in a certain range. It provides the visualization of numerical data by using the number of data points that fall within a specified range of values (also called “bins”).
A histogram chart in excel is classified or made up of 5 parts:
- Title: The title describes the information about the histogram.
- X-axis: The X-axis is the grouped intervals that shows the scale of values in which the measurements lie.
- Y-axis: The Y-axis is the scale that shows the number of times that the values occurred within the intervals set corresponds to the X-axis.
- The bars: This parameter has a height and width. The height of the bar shows the number of times that the values occurred within the interval. The width of the bars shows the interval or distance or area that is covered.
- Legend: This provides additional information about measurements.
Why the histogram chart is important in Excel?
There are many benefits to using a Histogram chart in excel.
- Histogram chart shows the visual representation of data distribution.
- Histogram chart displays a large amount of data and the occurrence of data values.
- Easy to determine the median and data distribution.
Types/Shapes of Histogram Chart
It depends on the distribution of data, the histogram can be of the following type:
- Normal Distribution
- A Bimodal Distribution
- A Right Skewed Distribution
- A Left Skewed Distribution
- A Random Distribution
Now we will explain one by one shapes of Histogram chart in excel.
It is also known as bell-shaped distribution. In a normal distribution, the points are as likely to occur on one side of the average as on another side. This looks like the below image:
A Bimodal Distribution:
This is also called Double peaked distribution. In this dist. there are two peaks. Under this distribution in one data set, the results of two processes with different distributions are combined. The data is separated and analyzed like a normal distribution. This looks like the below image:
A Right Skewed Distribution:
This is also called a positively skewed distribution. In this distribution, a large number of data values occur on the left side and the fewer number of data values on the right side. This distribution occurs when the data has a range boundary on the left-hand side of the histogram. For example, a boundary of 0. This dist. looks like below image:
A Left Skewed Distribution:
This is also called a negatively skewed distribution. In this distribution, a large number of data values occur on the right side and the fewer number of data values on the left side. This distribution occurs when the data has a range boundary on the right-hand side of the histogram. For example, a boundary such as 100. This dist. looks like below image:
A Random Distribution:
This is also called a multimodal distribution. In this dist., several processes with normal distributions are combined. This has several peaks, thus the data should be separated and analyzed separately. This dist. looks like below image:
Where the Histogram Chart is found in Excel?
The histogram chart option found under Analysis ToolPak. The Analysis ToolPak is a Microsoft Excel data analysis add-in. This add-in is not loaded automatically on excel. Before using this, we need to load it first.
Steps to load the Analysis ToolPak add-in:
- Click on File tab. Choose Options button.
- The Excel Options Dialog box will open. Click on Add-Ins button on the left sidebar.
- This will open the below Add-Ins dialog box.
- Select Excel Add-ins option under Manage field and click on Go button.
- This will open an Add-Ins dialog box.
- Choose the Analysis ToolPak box and click on OK button.
- The Analysis ToolPak is loaded in excel now and it will be available under the DATA tab with the name of Data Analysis.
Note: If an error occurs that the Analysis ToolPak is not currently installed on your computer, then click on Yes option to install this.
How to Create a Histogram Chart in Excel?
Before creating a histogram chart in excel, we need to create the bins in a separate column.
Bins are numbers that represent the intervals into which we want to group the data set. These intervals should be consecutive, non-overlapping and of equal size.
There are two ways to create a histogram chart in excel:
- If you are working on Excel 2016, there is a built-in histogram chart option.
- If you are working on Excel 2013, 2010 or earlier version, you can create a histogram using Data Analysis ToolPak.
Creating a Histogram chart in Excel 2016:
- In Excel 2016, under the chart section, a histogram chart option is added as an inbuilt chart.
- Select the entire dataset.
- Click the INSERT tab.
- In the Charts section, click on the ‘Insert Static Chart’ option.
- In the HISTOGRAM section, click on the HISTOGRAM chart icon.
- The histogram chart would appear based on your dataset.
- You can do the formatting by clicking right click on a chart on the vertical axis and choose the Format Axis option.
Creating a Histogram chart in Excel 2013, 2010 or earlier version:
Download the Data Analysis ToolPak as shown in the above steps. You also can activate this ToolPak in Excel 2016 version too.
Examples of Histogram Chart in Excel
Histogram Chart in Excel is very simple and easy to use. Let understand the working of Histogram Chart in Excel with some examples.
Let’s create a dataset of scores (out of 100) of 30 students as shown below:
For creating a histogram, we need to create the intervals at which we want to find the frequency. These intervals are called bins.
Below are the bins or score intervals for the above data set.
Please follow below steps to create the Histogram chart in Excel:
- Click on the DATA tab.
- Now go to the Analysis tab on the extreme right side. Click on Data Analysis option.
- It will open a Data Analysis dialog box. Choose the Histogram option and click on OK.
- A Histogram dialog box will open.
- In the Histogram dialog box, we will enter the following details:
Select the Input Range (as per our example – with the scores column B)
Select the Bin Range ( Intervals column D)
- If you want to include the column headings in the chart, then click on Labels Otherwise leave as it is unticked.
- Click on Output Range. If you want to grab ate a histogram in the same sheet, then specify the cell address or Click on New Worksheet.
- Choose the Chart Output Option and click on OK.
- This would create a Frequency Distribution table and the Histogram chart in the specified cell address.
There are below points which need to keep in mind while creating Bin’s or Intervals:
- The First bin includes all the values below it. For bin 30, frequency 5 includes all the scores below 30.
- The last bin is 90. If the values are higher than last bin, Excel automatically creates another bin – More. This bin includes any data values which are higher than the last bin. In our example, there are 3 values which are higher than last bin 90.
- This chart is called static histogram chart. Means, if you want to make any changes in the source data, you will have to create the histogram again.
- You can do the formatting of this chart like other charts.
Let’s take another example with 15 data points which are the salary of a company.
Now we will create the bins for the above dataset.
For creating the histogram chart in excel, we will follow the same steps as earlier taken in example 1.
- Click on the DATA tab. Select the Data Analysis option from the Analysis section.
- A Data Analysis dialog box will appear. Choose the histogram option and click on OK.
- It will open a histogram dialog box.
- Select the Input Range with the Salary data points.
- Select the Bin Range with the bins column.
- Click on Output Range as we want to insert the chart on the same sheet and will select the cell address where we want to insert the chart.
- Choose the Chart Output option and click on OK.
- The below chart will appear as :
- The First bin $25,000 will include all data points which are less than this value.
- The last bin More will include all the data points which are higher than $65,000. Its created automatically by Excel.
- The histogram is very useful while working with a large amount of data.
- Histogram chart shows the data in a graphical form which is easy to compare the figures and easy to understand.
- Histogram chart is very difficult to extract the data from the input field in the histogram. Means difficult to point the exact number.
- While working with histogram, it creates a problem with multiple categories.
Things to Remember About Histogram Chart in Excel
- A Histogram chart is used for continuous data where the bin determines the range of data.
- The bins are usually determined as consecutive and non-overlapping intervals.
- The bins must be adjacent and are of equal size (but are not required to be).
- If you are working with Excel 2013, 2010 or earlier version, you need to activate the Excel Add-Ins for Data Analysis ToolPak.
This has been a guide to Histogram in Excel. Here we discuss its types and how to create a Histogram chart in Excel along with excel examples and downloadable excel template. You may also look at these useful charts in excel –