Question

Create Histograms with Excel: Step-by-Step Visual Guide

Answer 1

What are histograms?
How to make a histogram in Excel
Create a histogram with the Excel add-in
Histogram as a chart type
Create a histogram with different bar widths

---

Create Histograms with Excel: Step-by-Step Visual Guide

They look like ordinary bar charts, but histograms are more than that: they can represent how the frequencies of the data are distributed. This task is a lot of work when it is done from mathematical formulas, but there is also a very obvious help: with Excel it is possible to create histograms quickly and easily. We explain how it works below..

Index

1. What are histograms?
2. How to make a histogram in Excel
   1. Create a histogram with the Excel add-in
   2. Histogram as a chart type
   3. Create a histogram with different bar widths

What are histograms?

Histograms represent the distribution of frequencies , so this type of diagram is used mainly in the field of statistics. In them it is possible to read how often certain values ​​appear in a class (a group of values). For this, both the width and the height of the bars are used. From the width of the bars it is possible to deduce the size of a class, which is one of the advantages of histograms: when you create a chart of this type, it is possible to determine the size of the class independently.

Example: suppose that we want to represent graphically, with the help of a histogram, the results of some throws in a children's sporting event. Those responsible measure different launch distances that they will later represent graphically. To do this, they divide the measurement values ​​into different classes. These should not be presented uniformly: the width of the bar indicates the size of each class on the histogram..

However, it is recommended to maintain uniformity at least in the middle of the diagram, as this strengthens the understanding of the graph. A class can be composed, for example, of launch lengths between 30 and 34 meters. The individual data is then divided into classes and the frequency of the classes is determined in this way .

To determine the height of the bars it is also necessary to calculate the so-called density. To do this, the number of values ​​belonging to a class is divided by the width of the class. In our example, with a class containing pitches between 30 and 34 meters, the width is 4 (because the interval is 4). For throws between 35 and 40 meters, the class width is 5..

Now suppose that 8 children have achieved results in the range of 30 to 34 meters. The class density would therefore be 2 (8 divided by the class width 4). In this way, a rectangle of width 4 and height 2 is created in the histogram. The observer of the graph can now read the number of entries from the height and width , since for this it is only necessary to multiply the two lengths of the songs.

advice

You can determine yourself how many classes there are and how wide they are. But select the values ​​in such a way that the diagram can provide relevant information.

How to make a histogram in Excel

Microsoft's spreadsheet program doesn't save you all the work, but it saves a lot of the steps. Excel uses, among other things, an add-in, that is, an extension of the standard functions. It is necessary to have the? Tools for analysis ?. To activate the plugin (or to check if it is already active), open the? Options? Excel and go to the menu "Add-ins". There are also other possibilities of representing the frequency distribution in a graph with the help of Excel.

Create a histogram with the Excel add-in

If the plugin is activated, create a table with all the measurement data in one column and with the classes you have selected in another. In the latter, always enter a limit value. If you want to integrate all the values ​​from 30 to 34 in one class, create a class 29 and a class 34. Everything that falls below 30 belongs to the first class and everything that exceeds it belongs to the third class.

First, create a table with the measurement data and class sizes.

To determine the frequency of the classes , now use the plugin. Go to the tab? Data? and click on the button? Data Analysis ?. In the list that appears, select the option? Histogram ?. An input mask will then open: in the? Input range? selects the column in which the measurement data is located. In? Output range ?, enter the area in which you have defined the classes. If you have labeled the columns in the first line, activate the option? Labels ?.

With the data analysis input mask you can create a histogram in Excel very quickly.

After deciding where the data analysis should be plotted (in a new or existing worksheet), Excel creates a frequency analysis . Now it is possible to read in the new table how much measurement data appears in the corresponding classes. To create the histogram itself, you must activate the option? Create graph ?. Once you confirm your entry, Excel creates the chart right away.

With just a few clicks you can calculate the frequency and create a histogram in Excel.

Note

With this method you can only create histograms with class ranges of identical size, that is, with exactly equal bar widths. In this way it is not possible to represent an uneven distribution of the widths.

Histogram as a chart type

Excel also recognizes histograms as a chart type. With this function you will have several options at your disposal to choose the arrangement of the classes . In order to make use of these options, you must use the list of original measurement data. Mark them and, in the tab? Insert? (in the? Charts? area), click the? Histogram ?. From the data, Excel creates a distribution with the classes. The bars are also evenly distributed with this method. Clicking with the right mouse button on the X axis and then on the point? Format axis ??, new display options are opened.

Note

If you follow the steps described above you can create histograms with the same method used in Excel to create graphs.

With the exception of the automatic distribution carried out by the program, in Excel you can also take advantage of two other very interesting possibilities: it is possible to define a class width so that Excel calculates how many classes are obtained or you can tell the program how many classes you want so that Excel determines by itself the width of the bars. Furthermore, it is also possible to overflow and underflow the range : in this case, these are classes that define the edges of the histogram with greater precision. You can therefore enter values ​​that you consider to be the desired minimum and maximum values, that is,? All below this value? and? all above this value ?. Depending on the accumulated values, you will be able to carry out a more useful distribution into classes.

The selection of the graph type? Histogram? offers several options for class distribution.

Create a histogram with different bar widths

In order to correctly represent the width of a class on the graph, a little detour is required. There is no standard function for this, so you have to use a little trick with an auxiliary table instead. Before you have to determine the classes and, with them, their width. The maximum common divisors are calculated from these class widths . Next, you need to determine how often the divisor appears in each of the classes. Then use the data analysis again to calculate the frequencies.

advice

To determine what is the greatest common divisor between different values, you can use the formula? = GCD ?.

Now create the auxiliary table : if, for example, there is a class in which the greatest common divisor fits twice, include these positions twice. If the divisor fits three times, create the three corresponding entries. This frequency of classes only doubles; the values ​​remain the same.

With an auxiliary table and a simple bar chart it is also possible to create a histogram in Excel.

From this table, you can create a bar chart . Several bars of equal height appear next to each other on the graph. Now you just have to format the appearance of the chart. To do this, first, right-click on one of the bars and select the option? Format data series ...? . Here you can adjust the width of the range. If you set the slider to 0, the bars will touch each other, as they would in a histogram. If, in addition, you configure the colors of the bars in such a way that the related bars stand out from the others, you have created a true histogram.

After you have made a couple of adjustments to the format, the diagram looks like a histogram.

---