Watch Video – 3 Means to Create a Histogram Chart in Excel

A histogram is a mutual data analysis tool in the business world. It's a column chart that shows the frequency of the occurrence of a variable in the specified range.

Co-ordinate to Investopedia, a Histogram is a graphical representation, similar to a bar chart in structure, that organizes a grouping of data points into user-specified ranges. The histogram condenses a data series into an easily interpreted visual by taking many data points and grouping them into logical ranges or bins.

A simple example of a histogram is the distribution of marks scored in a subject. You can easily create a histogram and see how many students scored less than 35, how many were between 35-l, how many between 50-threescore and and so on.

There are unlike means you tin can create a histogram in Excel:

• If you're using Excel 2016, there is an in-built histogram chart selection that you tin utilize.
• If you're using Excel 2013, 2010 or prior versions (and even in Excel 2016), you tin create a histogram using Data Analysis Toolpack or by using the FREQUENCY function (covered later in this tutorial)

Let's see how to brand a Histogram in Excel.

Creating a Histogram in Excel 2016

Excel 2016 got a new improver in the charts section where a histogram chart was added as an inbuilt nautical chart.

In case you lot're using Excel 2013 or prior versions, check out the next ii sections (on creating histograms using Data Analysis Toopack or Frequency formula).

Suppose you accept a dataset every bit shown below. It has the marks (out of 100) of twoscore students in a subject.

Here are the steps to create a Histogram chart in Excel 2016:

1. Select the entire dataset.
2. Click the Insert tab.
3. In the Charts group, click on the 'Insert Static Nautical chart' choice.
4. In the HIstogram group, click on the Histogram chart icon.

The above steps would insert a histogram chart based on your data set (as shown below).

Now yous can customize this chart by right-clicking on the vertical axis and selecting Format Axis.

This will open up a pane on the right with all the relevant axis options.

Here are some of the things yous tin can do to customize this histogram chart:

1. By Category: This option is used when you accept text categories. This could be useful when you have repetitions in categories and you lot want to know the sum or count of the categories. For example, if you have sales data for items such equally Printer, Laptop, Mouse, and Scanner, and you want to know the total sales of each of these items, y'all can use the By Category option. It isn't helpful in our case as all our categories are dissimilar (Student i, Student two, Student3, and so on.)
2. Automated: This option automatically decides what bins to create in the Histogram. For example, in our chart, it decided that there should be four bins. Y'all tin can change this past using the 'Bin Width/Number of Bins' options (covered below).
3. Bin Width: Here you tin define how big the bin should exist. If I enter 20 here, it will create bins such as 36-56, 56-76, 76-96, 96-116.
4. Number of Bins: Here you can specify how many bins you want. It volition automatically create a chart with that many bins. For example, if I specify 7 hither, it will create a chart as shown below. At a given signal, you lot can either specify Bin Width or Number of Bins (non both).
5. Overflow Bin: Utilize this bin if you lot want all the values to a higher place a certain value clubbed together in the Histogram chart. For case, if I want to know the number of students that take scored more than 75, I tin enter 75 as the Overflow Bin value. It volition show me something as shown below.
6. Underflow Bin: Similar to Overflow Bin, if I want to know the number of students that take scored less than 40, I tin can enter 4o as the value and show a nautical chart as shown below.

Once you have specified all the settings and have the histogram nautical chart you want, you tin can further customize information technology (irresolute the title, removing gridlines, changing colors, etc.)

Creating a Histogram Using Data Analysis Tool pack

The method covered in this section will likewise work for all the versions of Excel (including 2016). Still, if yous're using Excel 2016, I recommend you apply the inbuilt histogram nautical chart (every bit covered beneath)

To create a histogram using Data Analysis tool pack, you start demand to install the Analysis Toolpak add-in.

This add-in enables yous to quickly create the histogram past taking the information and information range (bins) as inputs.

Installing the Data Analysis Tool Pack

To install the Data Analysis Toolpak add-in:

• Click the File tab and and so select 'Options'.

• In the Excel Options dialog box, select Add-ins in the navigation pane.

• In the Manage drib-downwardly, select Excel Add-ins and click Go.

• In the Add-ins dialog box, select Analysis Toolpak and click OK.

This would install the Assay Toolpak and you tin access information technology in the Data tab in the Analysis grouping.

Creating a Histogram using Information Analysis Toolpak

Once you lot take the Analysis Toolpak enabled, you can use it to create a histogram in Excel.

Suppose you have a dataset every bit shown below. It has the marks (out of 100) of twoscore students in a subject field.

To create a histogram using this data, we demand to create the data intervals in which we want to find the data frequency. These are called bins.

With the above dataset, the bins would be the marks intervals.

You need to specify these bins separately in an additional column as shown below:

Now that we have all the information in place, permit'due south see how to create a histogram using this data:

• Click the Data tab.
• In the Analysis group, click on Information Analysis.

• In the 'Data Assay' dialog box, select Histogram from the list.

• Click OK.
• In the Histogram dialog box:
  • Select the Input Range (all the marks in our case)
  • Select the Bin Range (cells D2:D7)
  • Leave the Labels checkbox unchecked (y'all need to bank check it if you included labels in the data pick).
  • Specify the Output Range if you desire to become the Histogram in the same worksheet.  Else, choose New Worksheet/Workbook selection to go information technology in a split worksheet/workbook.
  • Select Chart Output.

• Click OK.

This would insert the frequency distribution table and the chart in the specified location.

Now there are some things you need to know about the histogram created using the Analysis Toolpak:

• The kickoff bin includes all the values below information technology. In this instance, 35 shows 3 values indicating that there are three students who scored less than 35.
• The last specified bin is xc, all the same, Excel automatically adds another bin – More. This bin would include any information point which lies after the last specified bin. In this example, it means that there are 2 students who have scored more than than ninety.
  • Note that even if I add the concluding bin every bit 100, this additional bin would still be created.
• This creates a static histogram chart. Since Excel creates and pastes the frequency distribution as values, the nautical chart would not update when you change the underlying data. To refresh it, you'll have to create the histogram again.
  • The default chart is not always in the best format. You lot can change the formatting like any other regular nautical chart.
• Once created, yous can not use Control + Z to revert information technology. You'll have to manually delete the table and the nautical chart.

If y'all create a histogram without specifying the bins (i.e., you get out the Bin Range empty), it would still create the histogram. Information technology would automatically create six every bit spaced bins and used this data to create the histogram.

Creating a Histogram using FREQUENCY Function

If you lot desire to create a histogram that is dynamic (i.due east., updates when you lot modify the data), y'all need to resort to formulas.

In this section, you'll learn how to use the FREQUENCY function to create a dynamic histogram in Excel.

Again, taking the student's marks information, you demand to create the data intervals (bins) in which you want to show the frequency.

Here is the function that will calculate the frequency for each interval:

=FREQUENCY(B2:B41,D2:D8)

Since this is an array formula, yous need to use Control + Shift + Enter, instead of just Enter.

Here are the steps to make certain you get the correct outcome:

• Select all cells adjacent to the bins. In this case, these are E2:E8.
• Press F2 to get into the edit mode for cell E2.
• Enter the frequency formula:=FREQUENCY(B2:B41,D2:D8)
• Hit Command + Shift + Enter.

With the result that you go, you can now create a histogram (which is nothing but a uncomplicated cavalcade chart).

Here are some important things you need to know when using the FREQUENCY office:

• The result is an array and yous can not delete a office of the array. If you need to, delete all the cells that accept the frequency function.
• When a bin is 35, the frequency function would return a result that includes 35. So 35 ways score up to 35, and 50 would mean score more than 35 and upwardly to 50.

Too, let's say you want to have the specified data intervals till 80, and you want to group all the result above 80 together, yous tin can do that using the FREQUENCY role. In that example, select one more than cell than the number of bins. For case, if you have 5 bins, then select vi cells equally shown below:

FREQUENCY part would automatically calculate all the values above eighty and return the count.

You May Also Like the Following Excel Tutorials:

• Creating a Pareto Chart in Excel.
• Creating a Gantt Chart in Excel.
• Creating a Milestone Chart in Excel.
• How to Brand a Bell Curve in Excel.
• How to Create a Bullet Chart in Excel.
• How to Create a Heat Map in Excel.
• Standard Deviation in Excel.
• Area Chart in Excel.
• Advanced Excel Charts.
• Creating Excel Dashboards.