MA321 Lab 2

Frequency Tables and Charts

Excel Tables

Table is a primary way to store data. In general, a column of a table represents a variable and a row stores data associated to an object being observed.

In Excel, a table is not just an array (rectangular block) of data in a spreadsheet, but a special object with many useful properties, such as,

  1. always visible column headers,
  2. dynamic graphs/charts,
  3. calculation using column headings,
  4. Automatically formula filling in.

Creating a Table in Excel

To create a table from an array (rectangular block) of data in Excel is very easy. Simply select the array and then go to Insert choose the Table function.


Creating a Frequency Table

In Excel, to create a frequency table for a data array, we need a bin array which is used to split the date set into smaller intervals. The values in a bin array in Excel are (upper) boundaries of intervals. With a data array and a bin array, we can use the Excel function FREQUENCY (data_array, bins_array) to create a frequency table.

In this formula, the value in a bin array is the upper bound of a interval. That is, if the bin array consists of 30, 40, and 50, then the corresponding intervals will be (−,30], (30, 40], (40, 50],(50,  + ], where and + represent respectively numbers smaller and bigger than the minimum of the data set.

Example: Create a frequency table for the test scores 50 56 56 57 58 63 64 65 68 69 69 69 70 71 71 71 73 74 80 87 using the bin array 50 60 70 80 90 100.

Suppose the test scores are in column A and the bin array is in column B. Here is how to create a frequency table using the function FREQUENCY (data_array, bins_array):

  1. In column C, right to the smallest value of the bin array enter =FEQUENCY(
  2. select the test scores
  3. in the formula bar, enter the symbol comma ,
  4. select the bin array
  5. in the formula bar, enter ).

Hit the Enter, you will get a frequency table.


Note: If you are using an older version of Excel, the formula must be entered by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you.

Creating Charts in Excel

Excel has many built-in chart functions. To create a charts,

  1. Select the data array/table
  2. Under the Insert tab, click on an appropriate chart in the Charts command set.

The appearance of chart can be changed after being created.

Example: Create a Pie Chart for the following frequency table.

Bins Frequency
0-50 1
51-60 4
61-70 8
71-80 6
81-90 1
91-100 0
  1. Select the frequencies.

  2. Under the Insert tab, click the Insert Pie or Doughnut Chart.

  3. Using bins as the legend:

    1. Right click the legend (the bottom part of the chart) and choose Select Data

      1. In the popup windows, click Edit horizontal axis label

        1. Select bins and click OK and then OK again.

  4. Add data labels or format them

    1. Right click the pie and click Add Data Labels.

    2. Right click the pie again and click Format Data Labels, you can then choose formats for data labels.


Create Simple Histogram Charts in Excel

  1. Select the data

  2. On the Insert tab, in the Charts group, from the Insert Statistic Chart dropdown list, select Histogram:


    Note: The histogram contains a special first bin which always contains the smallest number. This is different from many textbooks.

To format the histogram chart is similar to format a Pie chart. For example, you can change bin width from Format Axis.

  1. Right-click on the horizontal axis and choose Format Axis in the popup menu:

  2. In the Format Axis pane, on the Axis Options tab, you may try different options for bins. See the following gif for an example.



Lab Assignment #2

The following data set consists of 20 test scores.

89 62 40 42 9 16 88 73 39 72 71 51 40 37 72 81 36 13 10 70

Using the data set to

  1. Create a frequency table.
  2. A pie chart using the frequency table.
  3. A histogram with bin width 9.
  4. Describe the shape of the distribution.