Excel How To: Grouped Frequency Table & Bar Graph Using Analysis ToolPak’s Histogram Function Instead of PivotTable

Examples of Frequency Table, Bar Graph, Grouped Frequency Table

Here’s an example of a frequency table that shows the frequency of a variable. The variable was my happiness scores measured out of a 1-10 scale from the past 10 days.

Possible Happiness Scores	Days
1	1
2	0
3	0
4	0
5	2
6	0
7	1
8	4
9	2
10	0

The above frequency table can be turned into a bar graph as shown below:

Here is an example of a grouped frequency table (where values are grouped) regarding my sleep hours during the same 10-day period.

Sleep Hours	Days
0 ~ 1	0
1 ~ 2	0
2 ~ 3	0
3 ~ 4	1
4 ~ 5	2
5 ~ 6	1
6 ~ 7	2
7 ~ 8	2
8 ~ 9	0
9 ~  10	0
10 ~ 11	2
11 ~ 12	0

A bar graph created based on the grouped frequency table above.

Here’s the sample dataset that was used to create the graphs and tables above.

Date	Happiness (1~10, 10 being the happiest)	Sleep Hours
12/24/2020	8	6
12/25/2020	7	4.5
12/26/2020	9	11
12/27/2020	8	7.5
12/28/2020	8	7
12/29/2020	8	7
12/30/2020	5	5
12/31/2020	1	3.5
1/1/2021	5	8
1/2/2021	9	10.5

Step-by-Step Instructions for (Grouped) Frequency Table with 4-min Video Instructions.

*Followed by Instructions for a Grouped Frequency Table

• Let’s pretend that we want to create a frequency table based on my happiness scores in the dataset (the second column) that has 10 possible values (1~10, the higher the happier).
  • The following instructions would make more sense once you read Frequency Analysis & Frequency Table (6 min).
  • But in case you want the Excel Instructions right away, here we go:

• Step 1. Open Excel and copy and paste the above sample data (anywhere: for example, at the upper left corner of the spreadsheet).
• Step 2. Make sure you had activated Excel’s Data Analysis ToolPak.
• Step 3. List all the possible values of the variable either vertically or horizontally (i.e., 1~10).
  • List the values in order (i.e., 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 or alphabetically if the values are words).
  • Optional step: You can label the variable simply using the variable’s name itself (i.e., happiness or happiness scores).
    • Note, these individual values (1~10) will be refered to/used as “Bins” by Excel Analysis ToolPak (i.e., It will ask you to select “Bin Range” and you can select these values). So, if you want, you can label them Bins.
      • Bins typically refer to a groups of scores (e.g., 1~3 as one group, 4~6 as another group, etc.) that especially makes sense in the context of grouped frequency table. In the context of a regular frequency table, each individual value of a variable (e.g., 1, 2, 3, and so on) forms a bin.
• Step 3. Click/Select as follows:
  • [Data menu – Data Analysis (at the end of the menu ribbon)]
    • [Histogram – OK]
      • 1. [Input Range – Click the up-arrow icon – Select the raw data or variable based on which you want to create the frequency table (in this example, the second column of the sample dataset) – Click the down-arrow icon].
        • Make sure to use the raw data (Not the bins you just created maually).
        • You can either include or exclude the column heading (label), for example, Happiness (1~10, 10 being the happiest).
          • Label Option 1: If you had included the label (recommeded so that the label can be used in the analysis outcome), then, (after you select the bins – see below) make sure to check out the “Labels” box between the Input menus (i.e., Input Range and Bin range) and the Output options of the pop-up window.
          • Lable Option 2. If you had not included the label, then, leave the “Labels” box unchecked.
      • 2. [Bin Range – click the up-arrow icon – select the bins)]
        • Select the cells you created in Step 3 (all the possible values of the variable listed vertically or horizontally in order).
          • You can either include or exclude the column heading (label), for example, Happiness (1~10, 10 being the happiest).
            • Label Option 1: If you had included the label (recommeded so that the label can be used in the analysis outcome), then, (after you select the bins – see below) make sure to check out the “Labels” box between the Input menus (i.e., Input Range and Bin range) and the Output options of the pop-up window.
            • Lable Option 2. If you had not included the label, then, leave the “Labels” box unchecked.
      • 3. Optional step: [Output Range – click the up-arrow icon – Select a cell approximately where you want to have the frequency table and its bar graph.
        • The outcomes will be shown on the right side of the cell that you select at this step.
      • 4. Check [Chart Output] (the last menu):This is for the bar graph.

If you want to create a grouped frequency table, then, follow the same steps as above with one slight change about the bins.

• Bins refer to the target elements Excel counts from the raw data or variable to create the frequency table. The first column of the frequency table that Excel creates will be the bins that you manually create and the second column will show how many times each individual bins were found in the raw data.
• Therefore, if your bin has 1, 2, 3, then, Excel will count how many 1s, 2s, and 3s are there in the raw data.
• What if what you want Excel to count is groups of values, such as 1~3 as a group, and 4~6 as another group, 7~9 as another group, and so on?
• Then, it is tempting to set the bins as 1~3, 4~6, 7~9, and so on.
• However, when I use ~ to indicate a range, you may want to use -, while others want to say from 1 to 3.
• To avoid these issues, Excel requires you to put a single numeric value only to refer to a range: the upper limit of the range.
• For example, if you put 3, 6, 9 in the bins column, then, excel automatically (although this is an invisible process to you) creates grouped values of “from a minimal value in the variable to less than or equal to 3”, “from greater than 3 to less than or equal to 6”, “from greater than 6 to less than or equal to 9” and so on.
• In sum,
• Here’s the key difference of the bin setup in frequency table creation using Analysis ToolPak between regular frequency table and grouped frequency table.
  • Regular frequency table: The bin column shows all the possible individual values in order.
  • Grouped frequency table: The bin column should show only the upper limits (not the lower limit) across the groups of values.
• [Star Q] Where a value at the border between bins (e.g., 3 when the upper limit of a bin is 3) included?
• [Star A] Such values will be included in the bin that has the value as the upper limit (e.g., 3 will be included in the bin that has 3 as the upper limit).

Creating a Frequency Table Using Pivot Table Function of Excel

Finally, if you use the Pivot Table function of Excel (which is not in the Analysis ToolPak), you can easily make both the regular frequency table and grouped frequency table.

Excel How To: (Grouped) Frequency Table Using Pivot Table