Histogram and frequency analysis in Excel - Dashboard Guide

Dashboard Mode - Histogram and frequency analysis

Goal

Understand how to analyze data distribution by creating a histogram and frequency table in Excel. This dashboard answers: "How are the sales amounts distributed across different ranges?"

Sample Data

Sale ID	Sales Amount
1	120
2	250
3	310
4	450
5	180
6	90
7	400
8	220
9	330
10	270

Dashboard Components

• Frequency Table: Shows how many sales fall into each sales range (bin).
  Bins: 0-100, 101-200, 201-300, 301-400, 401-500
  Formula for frequency counts (in Excel):
  =FREQUENCY(B2:B11, {100,200,300,400})
  Place this formula as an array formula in cells D3:D7 (or use dynamic arrays in Excel 365+).
  Example output:
  

  Sales Range	Frequency
  0-100	1
  101-200	2
  201-300	3
  301-400	3
  401-500	1

• Histogram Chart: A column chart that visually shows the frequency of sales amounts in each bin.
  Use the frequency table data as the source.
  Steps:
  1. Select the sales ranges and frequency counts.
  2. Insert > Charts > Column Chart.
  3. Label axes: X-axis as "Sales Range", Y-axis as "Number of Sales".

Dashboard Layout

+----------------------+----------------------+
| Frequency Table       | Histogram Chart      |
| (Sales Range & Count)| (Column Chart)       |
+----------------------+----------------------+
| Sample Data Table                          |
| (Sale ID & Sales Amount)                   |
+-------------------------------------------+

Interactivity

Add a filter for sales amount ranges or minimum sales amount. When you change the filter:

• The frequency table updates to count only sales within the filtered data.
• The histogram chart updates to reflect the new frequency counts.
• The sample data table shows only the filtered sales records.

This helps explore how sales distribution changes for different subsets.

Self Check

If you add a filter to show only sales amounts greater than 200, which components update?

• The frequency table will show frequencies only for sales above 200.
• The histogram chart will update to reflect these filtered frequencies.
• The sample data table will display only sales with amounts above 200.

Key Result

Dashboard showing sales distribution with a frequency table and histogram chart for sales amount ranges.