Histogram and frequency analysis in Excel - Step-by-Step Guide

Introduction

A histogram helps you see how often numbers appear in groups. Frequency analysis counts how many times values fall into these groups. This makes it easy to understand patterns in your data, like test scores or sales amounts.

When you want to see how many students scored within certain grade ranges in a test

When you need to analyze sales amounts grouped by price ranges

When you want to understand the distribution of ages in a survey

When you want to check how often different temperature ranges occur in weather data

When you want to summarize how many products fall into different weight categories

Steps

Step 1: Select

- Data tab on the Excel ribbon

The Data tab options become visible

💡 Make sure your data is in one column without empty cells

Step 2: Click

- Data Analysis button in the Analysis group

The Data Analysis dialog box opens

💡 If Data Analysis is not visible, you need to add the Analysis ToolPak add-in

Step 3: Select

- Histogram from the list in the Data Analysis dialog

Histogram options appear

💡 Scroll down if Histogram is not immediately visible

Step 4: Enter

- Input Range box, select the cells with your data

Input range is set for analysis

Step 5: Enter

- Bin Range box, select or type the cells that define your groups (bins)

Bin range is set to group data into intervals

Step 6: Check

- Chart Output option

Excel will create a histogram chart after analysis

Step 7: Click

- OK button

Excel creates a frequency table and histogram chart on a new worksheet

Before vs After

Before

A column with 50 test scores ranging from 40 to 100 with no summary

After

A frequency table showing how many scores fall into ranges like 40-49, 50-59, etc., plus a histogram chart visualizing these counts

Settings Reference

Input Range

📍 Histogram dialog box

Defines the data to count frequencies for

Default: None

Bin Range

📍 Histogram dialog box

Defines the intervals to group data into

Default: None

Output Range

📍 Histogram dialog box

Where the frequency table and chart will appear

Default: New Worksheet Ply

Chart Output

📍 Histogram dialog box

Creates a histogram chart to visualize frequency

Default: Unchecked

Cumulative Percentage

📍 Histogram dialog box

Adds a cumulative percentage column to the output

Default: Unchecked

Common Mistakes

Not defining a bin range before running the histogram

Excel cannot group data without bin limits, so the output may be incorrect or missing

Create a column with bin numbers that cover the data range before running the histogram

Selecting empty or non-numeric cells in the input range

Non-numeric or blank cells cause errors or wrong frequency counts

Select only the cells with numeric data for analysis

Not enabling the Analysis ToolPak add-in

The Data Analysis button will not appear, so you cannot access the histogram tool

Go to File > Options > Add-ins, then enable Analysis ToolPak and restart Excel

Summary

Histogram groups data into ranges and counts how many values fall into each group.

Use the Data Analysis tool in Excel to create frequency tables and charts easily.

Always prepare bin ranges and numeric data before running the histogram tool.

Practice

1. What does the FREQUENCY function in Excel do when given a data array and bins array?

easy

A. Finds the maximum value in each bin

B. Sorts the data array in ascending order

C. Calculates the average of numbers in each bin

D. Counts how many data points fall into each bin range

Solution

1. Step 1: Understand FREQUENCY function purpose

   The FREQUENCY function counts how many values from the data array fall into each bin range defined by the bins array.

2. Step 2: Analyze the function output

   It returns an array where each element shows the count of data points within each bin interval.

3. Final Answer:

   Counts how many data points fall into each bin range -> Option D

4. Quick Check:

   FREQUENCY counts data per bin [OK]

Hint: FREQUENCY counts items per bin, not sorts or averages [OK]

Common Mistakes:

• Thinking FREQUENCY sorts data
• Assuming FREQUENCY calculates averages
• Confusing FREQUENCY with MAX or MIN functions

2. Which of the following is the correct way to enter the FREQUENCY function as an array formula in Excel for data in A2:A10 and bins in B2:B5?

easy

A. =FREQUENCY(A2:A10, B2:B5)

B. =FREQUENCY(A2:A10; B2:B5)

C. =FREQUENCY(A2:A10 B2:B5)

D. =FREQUENCY(A2:A10, B2:B5) entered normally

Solution

1. Step 1: Check FREQUENCY function syntax

   The correct syntax uses a comma to separate arguments: =FREQUENCY(data_array, bins_array).

2. Step 2: Understand array formula entry

   In modern Excel, pressing Enter is enough; older versions require Ctrl+Shift+Enter, but syntax remains the same.

3. Final Answer:

   =FREQUENCY(A2:A10, B2:B5) -> Option A

4. Quick Check:

   Correct syntax uses comma and proper ranges [OK]

Hint: Use commas between arguments; no semicolons in standard Excel [OK]

Common Mistakes:

• Using semicolons instead of commas
• Missing comma between arguments
• Entering formula without array selection in old Excel

3. Given data in cells A2:A7 as {2, 5, 7, 3, 8, 10} and bins in B2:B4 as {3, 6, 9}, what is the result of =FREQUENCY(A2:A7, B2:B4) entered as an array formula?

medium

A. {1, 3, 1, 1}

B. {1, 2, 2, 1}

C. {2, 1, 2, 1}

D. {3, 1, 1, 1}

Solution

1. Step 1: Identify bins and count data per bin

   Bins are 3, 6, 9. Count how many data points fall ≤3, >3 and ≤6, >6 and ≤9, and >9.

2. Step 2: Count data points in each bin

   Values: 2 (≤3), 3 (≤3), 5 (>3 ≤6), 7 (>6 ≤9), 8 (>6 ≤9), 10 (>9). Counts: ≤3 -> 2 values (2,3), >3 ≤6 -> 1 value (5), >6 ≤9 -> 2 values (7,8), >9 -> 1 value (10).

3. Final Answer:

   {2, 1, 2, 1} -> Option C

4. Quick Check:

   Count per bin matches {2,1,2,1} [OK]

Hint: Count data points in each bin range carefully [OK]

Common Mistakes:

• Miscounting values on bin edges
• Ignoring the last bin for values above highest bin
• Confusing bin intervals as inclusive/exclusive

4. You entered =FREQUENCY(A2:A10, B2:B5) but the result shows only one number instead of an array. What is the likely cause?

medium

A. You pressed Enter instead of Ctrl+Shift+Enter in an older Excel version

B. The bins array is empty

C. The data array contains text values

D. The formula syntax is incorrect

Solution

1. Step 1: Understand array formula behavior in older Excel

   In Excel versions before dynamic arrays, FREQUENCY must be entered as an array formula using Ctrl+Shift+Enter to return multiple values.

2. Step 2: Identify cause of single value output

   If only Enter is pressed, Excel returns only the first element of the frequency array, showing one number.

3. Final Answer:

   You pressed Enter instead of Ctrl+Shift+Enter in an older Excel version -> Option A

4. Quick Check:

   Old Excel needs Ctrl+Shift+Enter for array formulas [OK]

Hint: Use Ctrl+Shift+Enter for array formulas in old Excel [OK]

Common Mistakes:

• Not entering as array formula in old Excel
• Assuming single number means formula error
• Ignoring Excel version differences

5. You have sales data in A2:A20 and want to create a histogram with bins B2:B6. You want to show the frequency counts in C2:C7. Which formula should you enter in C2:C7 to get correct counts, and how should you enter it?

hard

A. Enter =COUNTIF(A2:A20, "<="&B2) in C2 and drag down

B. Select C2:C7, enter =FREQUENCY(A2:A20, B2:B6), then press Ctrl+Shift+Enter

C. Enter =FREQUENCY(A2:A20, B2:B6) in C2 and press Enter

D. Select C2:C6, enter =FREQUENCY(A2:A20, B2:B6), then press Ctrl+Shift+Enter

Solution

1. Step 1: Determine output size for FREQUENCY

   FREQUENCY returns an array with one more element than bins, so for 5 bins, output needs 6 cells (C2:C7).

2. Step 2: Enter FREQUENCY as array formula

   Select C2:C7, type =FREQUENCY(A2:A20, B2:B6), then press Ctrl+Shift+Enter (in older Excel) to fill all cells with counts.

3. Final Answer:

   Select C2:C7, enter =FREQUENCY(A2:A20, B2:B6), then press Ctrl+Shift+Enter -> Option B

4. Quick Check:

   Output range size = bins + 1, enter as array formula [OK]

Hint: Output range must be bins count + 1, enter as array formula [OK]

Common Mistakes:

• Selecting wrong output range size
• Not entering as array formula in old Excel
• Using COUNTIF which counts differently