What is best Steps for Creating Histogram chart in Excel

  Excel Interview Q&A

What is a histogram in Excel?

A histogram is a common 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.

Have you ever created a bar or column chart to represent some numerical data? I bet everybody has. A histogram is a typical use of a column chart where each column represents the frequency of elements in a certain range. In other words, the histogram displays consecutive non-overlapping intervals, or the number of elements within the bins.

For example, you can display the number of days between 61-65, 66-70, 71-75, etc. degrees, number of sales with quantities between $ 100- $ 199, $ 200- $ 299, $ 300. To make a histogram. – $ 399, 41–60, 61–80, number of students with test scores between 81–100, and so on.

The following screenshot gives an idea of ​​what an Excel histogram might look like:

excel-histogram

There are various ways by which you can create a histogram in Excel:

  • If you are using Excel 2016, there is a built-in histogram chart option that you can use.
  • If you are using Excel 2013, 2010 or earlier versions (and even in Excel 2016), you can create a histogram using the Data Analysis Toolpack or using the FREQUENCY function (later in this tutorial got involved)

Let us see how a histogram is created in Excel.

Create histogram in excel 2016

Excel 2016 received a new addition to the Chart section where a histogram chart was added as an inbuilt chart.

If you are using Excel 2013 or earlier versions, see the next two sections (on creating a histogram using the Data Analysis Tupac or Frequency Formula).

Suppose you have a dataset as shown below. It carries 40 students marks (out of 100) in a subject.

Students-Marks-to-create-histogram-in-Excel-2016

Some steps to create a Histogram chart in Excel 2016:

  1. Select the entire dataset.
  2. Click the Insert tab.

Insert-Tab-in-the-Excel-Ribbon

  1. Now in the Charts group, click on the ‘Insert Static Chart’ option.

Insert-Static-Chart-Option-in-the-Ribbon

  • In Histogram group, click on the Histogram chart icon.

Histogram-Chart-Option-in-the-Ribbon

Now Result is below

Resulting-Histogram-Chart-in-Excel-2016

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

Bin-Options-in-the-Histogram-Chart-in-Excel-2016

Here are some things you can do to customize this histogram chart:

  • By Category: This option is used when you have text categories. This can be useful when you have repetitions in categories and you want to know the sum or count of the categories. For example, if you have sales data for items such as a printer, laptop, mouse, and scanner, and you want to know the total sales of each of these items, you can use the By category option. This is helpful in our example because all our categories are different (student 1, student 2, student 3, and so on.)
  • Automatic: This option automatically decides what bins to create in a histogram. For example, in our chart, it was decided that there should be four bins. You can change this using the ‘bin width / number of bin’ option (bottom cover).
  • Bin Width: Here you can define how big the bin should be. If I enter 20 here, it will create bins like 36-56, 56-76, 76-96, 96-116.

Specifying-Bin-width-in-histogram-chart

  1. Number of bins: Here you can specify how many bins you need. This will automatically create a chart with multiple bins. For example, if I specify 7 here, it will create a chart as shown below. At a given point, you can specify the bin width or the number of bin (not both).

Specifying Number of Bins in histogram chart

  1. Overflow bin: If you want all values above a certain value clubbed together in a histogram chart, use this bin. For example, if I want to know the number of students scoring more than 75, I can enter 75 as the overflow bin value. It will show me something as shown below.

Overflow-Bin-Value-in-Histogram-Excel-2016

  1. Underflow Bin: Similar to the Overflow Bin, if I want to know the number of students scoring less than 40, I can enter 4o as the value and show a chart as shown below.

Underflow-Bin-Value-in-Histogram-Excel-2016

Create a histogram using the data analysis tool pack

The method included in this section will also work for all versions of Excel (including 2016). However, if you are using Excel 2014, I recommend you use the inbuilt histogram chart (as covered below).

To create a histogram using the Data Analysis Tool Pack, you must first install the Analysis Toolpack add-in.

Load the Analysis Toolpack Add-in

To add a Data Analysis Add-in to your Excel, follow these steps:

  • In Excel 2010, Excel 2013, Excel 2016 and Excel 2019, click File> Options. In Excel 2007, click the Microsoft Office Button and then click Excel Options.
  • In the Excel Options dialog, click Add-ins on the left sidebar, choose Excel Add-ins in the Manage box and click the Go button.

excel-options

  • In the Add-in dialog box, check the Analysis Toolpack box, and click OK to close the dialog.

If Excel shows a message that the Analysis Toolpack is not currently installed on your computer, click Yes to install it

enable-analysis-toolpak

Now, the Analysis Toolpack is loaded into your Excel, and its command is available in the Analysis group on the Data tab.
Specify excel histogram bin range

Before creating a histogram chart, there is another preparation to add bins to a separate column.

Bins are numbers that represent intervals in which you want to group source data (input data). The interval should be continuous, non-overlapping and usually the same size.

Excel’s Histogram tool contains input data values ​​in bins based on the following arguments:

  • A value is included in a fixed bin if it is greater than the lowest bound and less than the largest limit for that bin.
  • If your input data has a value greater than the highest bin, all such numbers will be included in the peacock range.
  • If you do not specify a bin range, Excel will create a set of bins evenly distributed between the minimum and maximum values ​​of your input data range.

Keeping the above in mind, type in the bin numbers you want to use in a separate column. The bins must be entered in ascending order, and your Excel histogram bin range should be limited to the input data range.

In this example, we have serial numbers in column A and approximate distribution in column B. In our Excel histogram, we want to display the number of items delivered in 1–5 days, 6–10 days, 11–15 days, 16–. 20 days and over 20 days. So, in column D, we enter the bin range from 5 to 20, with an increase of 5 as shown in the screenshot below:

excel-histogram-bins

Make a histogram using Excel’s Analysis ToolPak

With the Analysis ToolPak enabled and bins specified, perform the following steps to create a histogram in your Excel sheet:

  1. On the Data tab, in the Analysis group, click the Data Analysis button.

excel-data-analysis

  1. In the Data Analysis dialog, select Histogram and click OK.

make-histogram-excel

In the Histogram dialog window, do the following:

Specify the input range and bin range.

To do this, you can place the cursor in the box, and then using the mouse select the corresponding range on your worksheet. Alternatively, you can click the Collapse Dialog button, select Range on the sheet, and then click the Dialog button again to return to the Histogram dialog box.

Tip. If you include a column header when selecting input data and bin range, select the Label check box.
Choose the output option.

To place the histogram on a single sheet, click on the output range and then enter the upper-left cell of the output table.

To paste the output table and histogram into a new sheet or new workbook, select New Worksheet Ply or New Workbook, respectively.

  • Finally, choose any additional options:
  • To present the data to the output table in descending order of frequency, select the Pareto (sorted histogram) box.
  • To include the cumulative percentage line in your Excel histogram chart, select the Cumulative Percent box.
  • To create an embedded histogram chart, select the chart output box.

For this example, I configured the following options:

excel-histogram-options

And now, click OK, and review the output table and histogram graph:

excel-histogram-created

As you have just seen, it is very easy to create a histogram in Excel using the Analysis Toolpack. However, this method has an important limitation – the embedded histogram chart is static, meaning that you will need to create a new histogram each time the input data is changed.

To create an automatically updateable histogram, you can use the Excel function or build a PivotTable as shown below.

Creating a Histogram using FREQUENCY Function

If you want to create a histogram that is dynamic (that is, updates when you change data), then you have to resort to formulas.

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

Again, taking the student’s digit data, you need to create a data interval (bins) in which you want to show the frequency.

Create-Histogram-in-Excel-Bins-frequency

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

= Frequency (B2: B41, D2: D8)

Since this is an array formula, you have to use Control + Shift + Enter instead of just enter.

Here are the steps to ensure that you get the right result:

  • Select all cells adjacent to the box. In this case, these are E2: E8.
  • Press F2 to enter editing mode for cell E2.
  • Enter frequency formula: = FREQUENCY (B2: B41, D2: D8)
  • Press Control + Shift + Enter.

Histogram-in-Excel-frequency-function

With the result that you get, you can now create a histogram (which is nothing but a simple column chart).

Histogram-in-Excel-chart

Here are some important things to be told when using the FREQUENCY function:

  • The result is an array and you cannot delete a part of the array. If you need to, remove all cells that have a frequency function.
  • When a bin is 35 years old, the frequency function returns a result that includes 35. So 35 means score up to 35, and 50 means score over 35 and up to 50.

In addition, suppose you want to keep the specified data interval to 80, and you want to group all results above 80 together, then you can use the FREQUENCY function. In that case, select one more cell by the number of bins. For example, if you have 5 boxes, select 6 cells as shown below:

Histogram-in-Excel-select-one-more-than-bin

The FREQUENCY function will automatically count all values above 80 and return the count.

Histogram-in-Excel-result-when-one-more-bin-is-selected

LEAVE A COMMENT