Step-by-Step Guide on How to Make a Histogram in Excel

how to make a histogram in excel
how to make a histogram in excel

In Excel, a histogram is a typical diagram for data analysis. It is utilized to display the distribution of numbers within predetermined parameters and resembles a column chart.

There are several various ways to build a histogram in Excel. This article will demonstrate two distinct methods and outline their benefits and drawbacks. You need to know all the details about how to make a histogram in excel.

 

What is a Histogram?

A graph that displays the frequency analysis of a group of numbers is called a histogram. These variables’ frequency distributions are organized into defined ranges called bins.

For instance, a histogram can organize performance ratings into categories, numbers into different ages, or survey results into age ranges. One type of information visualization is the Histogram. This guide will demonstrate to you everything there is to know about data visualization, and it will also show you some samples.

how to make a histogram in excel
Image Credit: blog.crossjoin.co.uk

What Distinguishes a Histogram from a Column Chart?

A column chart and a histogram are similar, yet they are not identical. The two graphs are different in just a few ways, including:

  • A histogram displays frequency analysis, such as the number of exam results classified into ranges. For instance, comparing values using column charts, the combined total revenue of six different items.
  • A histogram’s X axis must be arranged according to size, for example, 1–5, 6–10, and 11–15. A column chart does not have to be arranged in a certain way. The goal determines the procedure.
  • In this process of how to make a histogram in excel histogram, the X-axis is made up of intervals or bins. Text values on this axis, such as city names, are frequently in column charts.
  • In a typical histogram, the columns are all filled in.
  • A histogram must contain all numbers in such a data collection to the fullest extent possible. I used a column chart to analyze just the top five goods.

 

How to Use the Histogram Chart in Excel

Using Excel’s created histogram chart is the simplest way to make a histogram.

If you are using an early form of Excel, you can use the correct method in this post. This graph is featured in Excel 2016 and after.

In this collection of data, there seem to be 41 scores. We choose to generate a histogram that divides the rankings across periods of 10, beginning at 40 and going up to 100.

  • Choose the range A1–B42.
  • Select Histogram by selecting Insert > Insert Statistical Chart.

The Histogram that follows is added. The values have been divided into four categories.

  • We must modify the axis choices as this is not what we want.
  • Select Format Axis by right-clicking on the categories axis (x-axis).
  • Select Axis Choices from the menu.
  • For the Underflow box or bin, use type 40. The place to start for the boxes will be this number.
  • For the Overflowing box, type 90. 91 or higher points will be counted toward this last box.
  • Set a box width of 10 pixels. It generates bins starting at 40 in intervals of 10.
  • Using the choice to define the necessary quantity of bins would be a substitute for selecting a bin size.

The Histogram is currently showing well. It’s crucial to understand that a bin’s higher number has been included, but not its lower number (except for the first bin).

How to make a histogram in excel
Image Credit: careerfoundry.com

Therefore, if a value is 60, it belongs in the (50, 60) box rather than the (60, 70) box. Although the viewer is not made aware of this, it is indicated by the various frames in the axis.

In this process, how to make a histogram in excel the histogram graph can be better handled by altering the column colors, inserting different data types, and modifying the chart name.

You need to know that since the purpose of this article is to create the Histogram, these methods are not explored. There are numerous alternatives for chart styling.

The benefit of this approach to generating a histogram is that we don’t have to build complicated calculations or bins in preparation. The created graph and the possible choices are all that are in control.

The drawbacks are that you will be limited to the functionality of the designed table and that it is just accessible in Excel 2016 or after. It isn’t very versatile.

 

How to Use an Excel Formula to Generate a Histogram

Your Histogram can be more flexible if you use a formula to calculate the frequency of every bin. This approach, which functions in every Excel edition, is my preferred one.

Rather than selecting the histogram option, enter a line graph and design it to resemble a typical histogram. The test result information from the preceding example will be utilized; however, two results have been modified to be lower than 40.

We must first produce the source information for our graph. The boxes and the proportion of examination results for each category are shown here.

The source produced in this example may be seen in the image below. The COUNTIFS method is utilized to record the appearances of the values for every box once the bins are entered into the ranges G4:G10.

=COUNTIFS($B$2:$B$42,”>=”&E4,$B$2:$B$42,”<=”&F4)

The COUNTIFS method has benefited from using the numbers E4:E10 and F4:F10. If the bins’ length alters in the future, it will be simpler to modify. Because no one wishes to look at these columns, they will be concealed.

The Histogram is made by:

  • Choose G4:H10.
  • Select Clustered Column by selecting Insert > Add Column or Bar Chart.

The column chart that follows is added. Even though it now looks fantastic, we will add a few fundamental changes before closing the space between every column.

how to make a histogram in excel
Image Credit: careerfoundry.com

The column chart has undergone the following modifications from me.

  • The chart’s title has been changed to “Exam Score Distribution.”
  • The columns were styled with a green color.
  • Got rid of the number axis (y-axis)
  • The horizontal gridlines were eliminated.
  • Data names have been provided to every column.

This strategy gave us how to make a histogram in excel additional versatility by allowing us to make our desired bins. Scores under forty are placed during the first bin. The bins are then spaced apart in ten increments. The created histogram chart’s stiffness restricts this freedom.

We simply need to alter the numbers in the underlying information’s cells and change this graph later.

 

Conclusion

The distribution of the data that is grouped in periods, or “bins,” is displayed using histograms. In Excel, there are various ways to make a histogram. Your data will influence your choice of approach, the amount of flexibility needed, and also how frequently your data is updated. I hope you like this information on how to make a histogram in excel, so stay here for another post.