2.3 Histograms: a graphical visualisation of frequency tables

In this activity, you will learn how to produce a histogram in Excel by following the instructions that are given below. Once you have produced the histogram in Excel, you can check your answer by clicking ‘Reveal discussion’ below.

• Open the Excel file called JC Electrics [Tip: hold Ctrl and click a link to open it in a new tab. (Hide tip)] , which contains the quarterly data of number of generators sold. The third column C contains information about the number of generators sold in each quarter of the year.
• Find the minimum and maximum value in the data set. You can obtain them through the min (range) and max (range) functions in Excel. Type =MAX(A5:A28) into cell L10 and =MIN(A5:A28) into cell L11. This will give the minimum and maximum values of the data set, which are 7 and 15.
• Next, you need to specify a range of intervals (often called ‘bins’) for which to count the number of observations that fall into each bin. The maximum value is 15 and the minimum value is 7, so you can make the class intervals 7–8, 9–10, 11–12, 13–14, 14–15, 15–16 etc. This means that the first class has the lower value 7 and the maximum value 8 and so on. See Columns C and D in the worksheet in Figure 18.

Maximise

Figure 18 A frequency distribution table in Excel

Show description|Hide description

This figure shows the JC Electrics Quarterly data of units sold, with columns labelled from left to right as: Generators, Class intervals, Lower limit of class interval, Upper limit of class interval and Frequency. From top to bottom, the Generators column contains the values 7, 7, 7, 7, 8, 8, 9, 9, 9, 9, 9, 10, 10, 11, 11, 11, 12, 12, 12, 13, 13, 14, 14, 15. The Class intervals column contains the values 7_8, 9_10, 11_12, 13_14, 15_16. The Lower limit of class interval contains the values 7, 9, 11, 13, 15. The Upper limit of class interval contains the values 8, 10, 12, 14, 16. The Frequency column contains the values 6, 7, 6, 4, 1.

Figure 18 A frequency distribution table in Excel

• There are many ways to calculate the width of the bin in Excel. One of the easiest ways to calculate it is as the width of the bin or class intervals (sample size / range), which is 3 (i.e. 24/8=3). In this example, the bin width is 2.

• Click on ‘Data Analysis’ in the ‘Data’ ribbon. This will bring up a list of some of the statistical analyses that you can perform in Excel.
• Select ‘Histogram’ and click ‘OK’.
• Specify the input range as A5:A28 and the bin range as D5:D9
• Tick the box ‘Chart Output’ and specify the output location as H5, as shown in Figure 19 below.

Figure 19 Histogram dialog box in Excel

Show description|Hide description

This figure shows the histogram dialog box in Excel. On the left is a heading that reads Input, under which are Input range and Bin range, with boxes to the right to add the required information. Under the range tags is an empty tick box named Labels. This is followed by the header Output options, with radio buttons underneath called Output range (which is selected), New worksheet ply, and New workbook. Output range and New worksheet ply have boxes to the right to input values. Under the radio buttons are three tick boxes labelled, from top to bottom, Pareto (sorted histogram), Cumulative percentage, and Chart output. Chart output is selected. To the top right of the window are buttons labelled OK, Cancel, and Help.

Figure 19 Histogram dialog box in Excel

Click ‘OK’. Excel will put the histogram next to your frequency table.

Figure 20 Histogram of number of units sold (Generators)

Show description|Hide description

This figure shows a table to the left and a histogram to the right. The table has two columns headed Bin and Frequency. The values in Bin are 8, 10, 12, 14, 16, More; the values in Frequency are 6, 7, 6, 4, 1, 0. The histogram shows five blue bars. The x-axis is labelled Bin and runs from 8 to More in increments of 2. The y-axis is labelled Frequency and runs from 0 to 8 in increments of 2. The bars represent the values from the table, showing 8 on the x-axis with 6 on the y-axis, followed by 10 (x) with 7 (y); 12 (x) with 6 (y), 14 (x) with 4 (y) and 16 (x) with 1 (y). There is no bar in the More (x) with 0 (y) space.

Figure 20 Histogram of number of units sold (Generators)

• To remove the space between the bars, right click a bar, click Format Data Series, and change the Gap Width to 0%.
• To add borders, right click a bar, click Format Data Series, click the Fill & Line icon, click Border, and select a colour.
• Now click ‘Reveal discussion’ to compare what you have made against the answer.