How To Do Frequency Distribution in Excel

Mon-Apr-2024
14

Creating Frequency Distribution in Excel

There are two main approaches to creating frequency distributions in Excel: using the Data Analysis ToolPak (for histogram creation) and using a PivotTable. Here’s a breakdown of both methods:

Method 1: Using Data Analysis ToolPak (For Histograms)

Note: This method requires the Data Analysis ToolPak to be enabled. If you don’t see it, you’ll need to enable it following your specific version of Excel’s instructions (search online for “Enable Data Analysis ToolPak” and your Excel version).

  1. Prepare Your Data:

    • Have your data set in a single column. This could be exam scores, product sales figures, etc.
    • Ideally, your data should be numerical.

  2. Set Up Bins (Optional):

    • Bins are the categories you’ll group your data into. Decide on an appropriate bin size based on the range and distribution of your data (usually 5-15 bins).
    • In a separate column, list the upper limits for each bin. For example, if your data ranges from 10 to 100 and you choose 10 bins, your bin limits could be 10, 20, 30, …, 90, 100.

  3. Access the Data Analysis Tool:

    • Go to the Data tab.
    • Click on Data Analysis (if enabled).
    • Select Histogram from the list and click OK.

  4. Input Data:

    • In the Input Range, select the range of your data set (the single column).
    • If you created bins, select the range of your bin limits in the Bin Range box. Otherwise, leave it blank.
    • Choose the output location for your frequency table by clicking in the Output Range box and selecting a cell where you want the results to appear.

  5. Generate the Histogram (Optional):

    • Check the Chart output box if you want Excel to automatically create a histogram chart based on your frequency distribution.

  6. Click OK.

Excel will generate a table showing the bin classes (categories), frequencies (how many data points fall into each bin), and percentages (optional). If you selected chart output, a histogram will also be created on a new sheet.

Method 2: Using PivotTables (For Frequency Tables Only)

  1. Prepare Your Data:

    • Have your data set in a table format. This can include multiple columns, but you’ll be focusing on the numerical column for the frequency distribution.

  2. Create a PivotTable:

    • Select your data table.
    • Go to the Insert tab.
    • Click PivotTable.
    • Choose where you want to place the PivotTable (new worksheet or existing one).

  3. Set Up the PivotTable:

    • Drag the field you want to analyze (the numerical column) to the Rows area of the PivotTable Fields pane.
    • Drag the same field again (or another field for grouping, if desired) to the Values area.

  4. Change Value Calculation to Count:

    • Click on any cell within the Sum of column in your PivotTable.
    • Right-click and select Value Field Settings.
    • Change the Summarize values by option to Count.
    • Click OK.

  5. Optional Grouping (For Bins):

    • Right-click on the field name in the Rows area.
    • Select Group.
    • Set up your desired bin ranges by specifying the starting point, ending point, and by value (bin size) in the dialogue box.
    • Click OK.

Your PivotTable will now display the frequency distribution of your data, with rows representing the categories (bins if grouped) and columns showing the frequency counts.

Choosing the Right Method:

  • Use the Data Analysis ToolPak with histograms if you need a visual representation of the data distribution.
  • Use PivotTables if you only need a frequency table and want the flexibility to group or filter your data further.