How to get frequency histogram in Excel?

A frequency histogram in Excel visually represents the distribution of numerical data, grouping data into "bins" (intervals) and showing how many data points fall into each bin. This powerful tool helps you understand data patterns, identify outliers, and assess data variability. Excel offers several straightforward methods to create a frequency histogram.

How to Get a Frequency Histogram in Excel?

Creating a frequency histogram in Excel can be accomplished through a few effective methods, depending on your Excel version and specific needs. The most common approaches involve using the built-in Chart feature, the Data Analysis ToolPak, or the FREQUENCY array function.

Method 1: Using Excel's Built-in Histogram Chart (Excel 2016 and Later)

This is the simplest and most direct method for modern Excel versions.

1. Select Your Data: Highlight the range of numerical data for which you want to create a histogram.
2. Insert Chart:
   • Go to the Insert tab on the Excel ribbon.
   • In the Charts group, click on the Statistical Charts icon (it looks like a box plot or histogram icon).
   • Select Histogram.
   • Excel will automatically generate a histogram with default bins.

Customizing Your Histogram Bins:

Once the chart is created, you can easily adjust the bin settings:

1. Select the Horizontal Axis: Click on the horizontal (category) axis of the histogram.
2. Access Format Axis: Right-click the axis and select Format Axis..., or go to the Format tab and click Format Selection.
3. Adjust Bin Options: In the Format Axis pane (usually on the right side of your screen), navigate to the Axis Options section (the bar chart icon). You'll find several options for binning:
   • By Category: Creates a bin for each unique value (less common for true histograms).
   • Automatic: Excel determines the optimal bin size and number.
   • Bin Width: Enter a specific numeric value for the width of each bin.
   • Number of Bins: Specify the desired number of bins for your data.
   • Overflow Bin: Defines a bin for all values above a certain number.
   • Underflow Bin: Defines a bin for all values below a certain number.

Choosing the right bin width or number of bins is crucial for effectively visualizing your data's distribution. Experiment to find the best fit.

Method 2: Using the Data Analysis ToolPak (All Versions)

The Data Analysis ToolPak is a powerful Excel add-in that provides various statistical analysis tools, including histograms.

Step 1: Enable the Data Analysis ToolPak

If you haven't used it before, you'll need to enable it:

1. Go to File > Options.
2. In the Excel Options dialog box, click on Add-ins in the left pane.
3. At the bottom of the dialog box, next to Manage: Excel Add-ins, click Go....
4. In the Add-Ins dialog box, check the box for Analysis ToolPak and click OK.

The Data Analysis ToolPak will now appear in the Data tab on the ribbon, in the Analyze group.

Step 2: Prepare Your Data and Bin Range

Before creating the histogram, it's beneficial to define your "bins" or intervals. Bins are the upper limits for each interval into which you want to group your data.

• Input Range: This is your raw numerical data.
• Bin Range: Create a separate column in your worksheet listing the upper boundary for each bin. For example, if your data ranges from 0-100, your bin range might be 10, 20, 30, ..., 100. Excel will automatically create an "overflow" bin for values greater than your highest specified bin.

Example Bin Range Setup:

Step 3: Generate the Histogram

1. Go to the Data tab on the ribbon.
2. Click Data Analysis in the Analyze group.
3. In the Data Analysis dialog box, select Histogram and click OK.
4. Configure the Histogram dialog box:
   • Input Range: Select the range containing your raw data.
   • Bin Range: Select the range containing your predefined bin upper limits. (If left blank, Excel will automatically determine bins, which may not be ideal).
   • Labels: Check this box if your input range or bin range includes header labels.
   • Output Options: Choose where you want the frequency table and histogram chart to appear:
     • New Worksheet Ply: Creates a new sheet for the output.
     • New Workbook: Creates a new Excel file.
     • Output Range: Specifies a cell on the current sheet where the output will start.
   • Chart Output: Crucially, check this box to have Excel generate the histogram chart alongside the frequency table.
5. Click OK.

Excel will then generate a frequency distribution table and a column chart representing your histogram.

Method 3: Using the FREQUENCY Function (Manual Calculation)

While the previous methods directly generate the graphical histogram, the FREQUENCY function allows you to calculate the frequency distribution manually, which can then be used to create a chart. This method is useful when you need the raw frequency counts for further calculations or specific chart designs.

The Excel function to calculate frequency is =FREQUENCY([data range],[bin range]).

Step 1: Define Your Bins

Just like with the Data Analysis ToolPak, you'll need a column for your bin upper limits.

Step 2: Apply the FREQUENCY Array Function

1. Select Output Range: Select a vertical range of empty cells where you want the frequency counts to appear. This range should have one more cell than your [bin range] to account for the "overflow" bin (values greater than the last bin).
2. Enter the Formula: With the entire output range selected, type the FREQUENCY formula into the formula bar:
   =FREQUENCY(A2:A100, B2:B5)
   • Replace A2:A100 with your actual data range.
   • Replace B2:B5 with your actual bin range.
3. Enter as an Array Formula: Press Ctrl + Shift + Enter on your keyboard. Do not just press Enter. This tells Excel to treat it as an array formula, populating all selected cells with the frequency counts.

The FREQUENCY function will calculate how many data points fall into each bin. The last value in the output array will be the count of values greater than the highest bin specified.

Step 3: Create the Histogram Chart

Once you have your frequency counts (calculated by the FREQUENCY function) and your bin labels (or midpoints), you can create a standard Column Chart to visualize the histogram:

1. Select Data: Highlight the frequency counts and their corresponding bin labels.
2. Insert Column Chart:
   • Go to the Insert tab.
   • In the Charts group, select Column chart (e.g., Clustered Column).
3. Adjust Gaps (Optional): For a true histogram appearance, the bars should touch. Right-click on one of the bars, select Format Data Series..., and set the Gap Width to 0%.

Important Note: Since the FREQUENCY function is an array formula, if your source data changes, you might need to re-select the output range for the FREQUENCY formula and press Ctrl + Shift + Enter again to update the frequency counts. Consequently, if the chart is directly linked to these counts, it should update, but sometimes for more complex setups, you may need to recreate the chart to ensure it reflects the latest data accurately, as specified by the reference.

Key Considerations for Effective Histograms

• Bin Definition: Careful selection of bin width or number of bins significantly impacts the insights gained from your histogram. Too few bins can oversimplify, while too many can show too much noise.
• Data Range: Ensure your data covers a reasonable range for analysis.
• Labels and Titles: Always include a clear chart title and axis labels (e.g., "Frequency" for the vertical axis, and "Data Values" or "Bins" for the horizontal axis) for readability.
• Interpreting the Shape: Look for common distribution shapes (bell-shaped, skewed, bimodal) to understand the underlying data characteristics.

By utilizing these methods, you can effectively create and customize frequency histograms in Excel to analyze and present your data distributions.