How to Create a Frequency Table in Excel Using Pivot Tables

Written by paul dohrman
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Create a Frequency Table in Excel Using Pivot Tables
(BananaStock/BananaStock/Getty Images)

Pivot tables are data summarisation tools that look like tables except that they allow a quick rearranging of the constituent fields to give you a different view of the data. The feature is available in Microsoft Excel, Microsoft Access, and Lotus 1-2-3. Excel pivot tables have a frequency feature, where the pivot table will display how many times a certain data value occurs. The default is to display sums of the data, for example, sums of revenue. By changing this option to display counts, you can make a frequency table of discrete or grouped continuous data.

Skill level:


  1. 1

    Highlight the cells containing the data, if you’re starting the pivot table from scratch.

  2. 2

    Open the Data drop-down menu and select “PivotTable Report…”. A new frame will open. Click the two “Next” buttons that you see. Click “Existing Worksheet” and select a cell to be the upper-left corner of your pivot table.

  3. 3

    Click “Layout.” A new panel will open. Drag the field name to the square centre of the table that you want to know the frequency of. Double-click it and select “Count.” Drag the same field from the far right into the left column where the row labels go. Click “OK” and then click “Finish.” You will be sent back to the Excel spreadsheet, which will now have a pivot table starting in the cell where you requested it be created.

  4. 4

    Group continuous data by right-clicking your mouse on the column with the field labels. (An example of continuous data is "revenue," while an example of discrete or nominal data is “highest degree achieved.”) Select “Group and Outline” in the menu that pops up and then select “Group.” The range of data will be displayed for you. Select the interval width you want the data grouped into, and enter it in the “By:” field. Select “OK.” The table will now collapse into a more-compressed form and the frequencies listed will go up as data points are added together. If an interval width has no data in it, it won’t be given a row in the table.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.