Excel How To: (Grouped) Frequency Table Using Pivot Table Function Instead of Analysis ToolPak’s Histogram Function

You can create a (grouped) frequency table using the Histogram function of Analysis ToolPak. However, one downside of this method is that you need to manually decide the bins. For example, you need to manually list all the possible values for a regular frequency table or manually enter the upper limits of the bins (grouped scores) for a grouped frequency table.

Pivot table makes this step quite automatic.

Here’s the sample dataset that will be used to create graphs and tables.

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 Regular (not Grouped) FrequencyTable Using Pivot Table with 4-min Video Instructions.

• Let’s pretend that we want to create a grouped frequency table based on my sleep hour scores in the dataset (the third column).

• Step 1. Open Excel and copy and paste the above sample data (anywhere: for example, at the upper left corner of the spreadsheet).
• Note the following steps are what you would need if you used the Histogram Function of the Analysis ToolPak.
• 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 2. Click/Select as follows:
  • [Insert menu – Pivot Table]
    • 1. [Choose the data that you want to analyze – Select a table or range – Table/Range – Click the up-arrow icon – Select the whole table including headings or the third column only including heading]
    • 2. Optional: [Choose where you want the Pivot Table report to be placed – Existing Worksheet – Location – Choose 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.
    • Click OK.
• Step 3. Move the Sleep Hours variable from the top box to the “Rows” field (lower left corner box).
• Step 4. Move the Sleep Hours variable from the top box to the “Values” field (lower right corner box).
  • Click “Sum of Sleep Hours” – “Value Field Settings” (at the bottom of the pop-up list) – Choose “Count” among the options (the default is Sum).

Step-by-Step Instructions for Grouped FrequencyTable Using Pivot Table with 4-min Video Instructions.

• 1. After Step 3 in the above, you would see a column created showing the values of the variable. Hover your cursor on any of the values and right click.
• 2. In the middle of the pop-up list, you would see Group, which you would want to select.
• 3. A pop-up window (Grouping) will show the minimum and maximum values of the variable at “Starting at:” and “Ending at:”, respectively. Adjust the Starting at and Ending at values as you wish (e.g., 3 and 12 for each, respectively).
• 4. For “By:” enter the bin size or width (the range of scores each group would include). For example, 3.
• Finally, in the PivoTable Field (right-hand side menu), drag the same target variable from the Top filed to the Values field (the lower right box).

Note, unlike the grouped frequency table created using the Histogram function of Analysis ToolPak, Pivot table does not include the upper-limit value of each bin into the bin. For example, when the bin says 3 – 6, 6 is not included in this but include in the bin of 6 – 9.