How to Make a Histogram in Excel – Step By Step Guide

A histogram is a visual representation of the frequency tables of discrete or continuous statistics. Although histograms resemble vertical bar charts, they are not the same. Histograms, on the other hand, are employed to display data distributions, whilst the implementation of bar graphs are analyzing multiple data. Unlike a bar chart, a histogram has no spaces between the columns. This article will explain to you how to make a Histogram in Excel.

You can make use of a Histogram chart in order to plot the frequency table of collecting data in Excel. You may make a histogram in Excel by using the Data Analysis ToolPak or an installed histogram graphic.

The Data Analysis Toolpak must be installed before you can use it

Excel does not come with a Histogram tool normally. To use it, you’ll need to download and install the Analysis ToolPak Add-in for Excel. The Histogram will appear in the column of Analysis Tools or in the charts section once the Add-in is enabled.

  • Select the ‘File’ button in Excel to add the Analysis ToolPak Add-in.
  • Choose ‘Options’ in the Excel backstage window.
  • Hit the ‘Add-ins’ button on the left side of the ‘Excel Options’ window.
  • You can edit and see your Microsoft Excel Add-ins there. Choose ‘Go’ after selecting ‘Excel Add-in’ from the ‘Manage:’ drop-down at the bottom left of the screen.
  • Next, in the Add-ins dialogue box, select the ‘Analysis ToolPak’ option and tap on ‘OK.

Now that Excel has a histogram feature, let’s look at how to make one.

The Process of Using Charts to Make a Histogram

  • Generate a database and pick the number of cells that contain the statistics that will be shown as a histogram.
  • Build a dataset for a group of students in ten classes, for instance.
  • Visit the ‘Insert’ tab and choose the range of cells you want to input. The histogram chart format is now accessible in the Insert tab’s ‘Charts’ category.
  • Pick your histogram template by clicking on the histogram logo.
  • This would result in a histogram with the dataset (marks) organized into bars.
  • After you’ve produced the histogram, you may tweak it in Excel’s ‘Design’ box to match your needs. You may customize the chart by adding items, changing the color of the columns, changing the chart layout, and changing the rows and columns.
  • Right-click somewhere on the axis and choose ‘Format Axis’ from the menu bar to structure the X-Axis and Y-axis of the chart.
  • On the top right of your Excel window, a formatting panel will appear. You may further tweak your axis here to meet your requirements. You may alter bin width, bin grouping, and the number of bins on the graph, among other things.
  • As we generated the chart, Excel, for example, immediately divided the data into three bins. If you enhance the number of bins to six then it will divide the data into six bins.

Make Use of a Data Analysis Tool to Create a Histogram

Another option is to use the Data Analysis Toolpak plugin for Excel to create a histogram. To make a histogram, we must first generate a data collection, followed by data ranges (bins) at which we wish to discover the data frequency.

  • Columns A and B in the given example include the data set, whereas column D comprises bins or mark periods. These bins must be specified differently.
  • Then, under the Excel Ribbon, navigate to the ‘Data’ tab and choose ‘Data Analysis’.
  • Choose ‘Histogram’ from the list in the Data Analysis dialogue option and press ‘OK.’
  • A dialogue window for the histogram will display. The Input range, Bin range, and Output range must all be defined in the Histogram dialogue window.
  • Pick the B2:B16 line from the ‘Input range’ field (which consists of Marks). Next, in the ‘Bin Range’ field, pick the D2:D9 range (which consists of data intervals).
  • Select a cell in the Output Range area where you wish the set of data to display. Then select ‘Chart Output’ from the drop-down menu and press ‘OK.’
  • A Frequency Distribution Table and a Histogram graphic are now constructed in the provided cell location.

You may enhance the histogram even more by altering the chart design, adjusting the chart caption, and substituting the default Bins and Frequency with more appropriate axis labels. You may also arrange this graph just as any other graph.

What advantages does a histogram provide?

Histograms allow consumers to review data quickly and simply, and they perform well with a wide variety of data. They also offer a more tangible form of constancy. Because the spacing is always uniform, data transmission from frequency distribution tables to histograms is simple.

In Excel, what is the purpose of bins?

Bins are numerals that denote the periods in which the original dataset should be divided (input data). If you don’t provide a bin range, Excel will generate a set of equally spaced bins between the input data range’s upper and lower limits.

What are the Components of a Histogram?

  1. The title: The histogram’s data is characterized in the title.
  2. X-axis: The X-axis is made up of spaces that represent the set of parameters that the measurements fall inside.
  3. The multitude of times the entries happened inside the intervals indicated by the X-axis is shown on the Y-axis.
  4. The bars: The amount of times the values happened during the period is represented by the height of the bar. On the other hand, the width of the bar represents the interval occupied. The width of all bars in a histogram with equivalent bins should be the same.

What are the Importance and Value of Histograms?

A histogram is a pictorial depiction of the distribution of data. Histograms may show a lot of data as well as frequency. The Frequency Function belongs to the Excel Statistical Functions category. It may be used to calculate the frequency of attributes in the data.

The MEDIAN Function belongs to the Excel Statistical Functions category. The middle value of a collection of integers will be calculated using this function. The middle number in a bunch of numbers is known as the median.