How to Create a Histogram in Google Sheets

Key Insights

Google Sheets offers two primary methods for creating histograms: the built-in chart tool for quick visualizations and the FREQUENCY function for granular control over bin ranges
Unlike bar charts that compare categories, histograms display continuous data distribution—making them essential for identifying patterns, outliers, and the shape of your data
Custom bucket sizes dramatically affect histogram interpretation; too few bins hide patterns, too many create noise—aim for 5-20 bins depending on your dataset size

Introduction to Histograms

Histograms are one of the most misunderstood chart types in spreadsheet software. People confuse them with bar charts constantly, but they serve fundamentally different purposes. A bar chart compares discrete categories—sales by region, votes by candidate. A histogram shows how continuous numerical data distributes across ranges, revealing the underlying shape of your dataset.

When you need to answer questions like “What’s the most common response time?” or “Are my exam scores normally distributed?” or “Do I have outliers skewing my averages?"—you need a histogram.

Common use cases include analyzing test score distributions, understanding customer response times, examining salary ranges across an organization, and identifying data quality issues. If you’re doing any serious data analysis in Google Sheets, histograms should be in your toolkit.

Preparing Your Data

Histograms require numerical data in a single column. That’s it. No categories, no labels in adjacent columns—just raw numbers.

Here’s a sample dataset representing exam scores for 60 students:

Before creating your histogram, clean your data:

Remove blank cells within your data range—they can cause calculation errors
Convert text to numbers if values imported as text (look for the green triangle warning)
Decide on outlier handling—extreme values can compress your histogram and hide patterns

For outliers, you have options: remove them, note them separately, or use logarithmic scales. The right choice depends on whether outliers represent data errors or legitimate extreme values.

Method 1: Using the Built-in Chart Tool

The fastest path to a histogram uses Google Sheets’ native chart functionality. Here’s the exact process:

Step 1: Select your data range including the header (e.g., A1:A61 for the sample data above)

Step 2: Navigate to Insert → Chart

Step 3: In the Chart Editor panel, click the “Chart type” dropdown

Step 4: Scroll to the “Other” section and select “Histogram chart”

Google Sheets automatically determines bucket sizes based on your data range. For the exam scores example, it might create buckets of width 5 (65-70, 70-75, etc.).

The default histogram appears immediately, showing frequency counts on the Y-axis and score ranges on the X-axis. You’ll see the classic bell-curve shape if your data is normally distributed, or skewed patterns if it isn’t.

Quick customization in Chart Editor:

Setup tab: Verify data range, toggle “Use row 1 as headers”
Customize tab → Histogram: Adjust bucket size (more on this shortly)
Customize tab → Chart & axis titles: Add meaningful labels

This method works for 90% of use cases. But when you need precise control over bin boundaries, you need the FREQUENCY function.

Method 2: Using the FREQUENCY Function

The FREQUENCY function counts how many values fall within specified ranges. It gives you complete control over bin edges—essential when you need bins aligned to meaningful thresholds (like grade boundaries) rather than arbitrary intervals.

Setting up bins:

First, create your bin boundaries in a separate column. For exam scores with letter grade cutoffs:

Column C (Bins)
60
70
80
90
100

These represent the upper boundary of each bin. Values ≤60 go in the first bin, 61-70 in the second, and so on.

Applying the FREQUENCY function:

The syntax is =FREQUENCY(data_array, bins_array). FREQUENCY is an array function—it returns multiple values simultaneously.

=FREQUENCY(A2:A61, C2:C6)

Critical step: FREQUENCY returns an array with one more element than your bins (to capture values above the highest bin). You must enter it correctly:

Select cells D2:D7 (one more row than your bin count)
Type the formula: =FREQUENCY(A2:A61, C2:C6)
Press Ctrl+Shift+Enter (Windows) or Cmd+Shift+Enter (Mac)

You’ll see curly braces appear around the formula in the formula bar: {=FREQUENCY(A2:A61, C2:C6)}

The result shows counts for each range:

C           D
Bins        Count
60          2
70          8
80          18
90          22
100         10
(overflow)  0

Now select your bins and counts (C2:D6), insert a chart, and choose “Column chart” for a manual histogram with your exact bin boundaries.

Customizing Your Histogram

Default histograms rarely communicate effectively. Customization transforms a generic chart into a clear analytical tool.

Bucket size adjustment (built-in histogram):

In Chart Editor → Customize → Histogram, you’ll find “Bucket size.” This single setting has the biggest impact on your histogram’s usefulness.

Too few buckets (large size): Hides distribution patterns. A bucket size of 30 for exam scores shows almost nothing.
Too many buckets (small size): Creates noise. A bucket size of 1 makes every score its own bar.
Sweet spot: Generally, use 5-20 buckets. For 60 data points, 6-10 buckets usually work well.

Try bucket size 5 for the exam data—you’ll see the distribution shape clearly without excessive granularity.

Visual customization:

Navigate through Customize tab options:

Chart style:
  - Background color: Keep white for clarity
  - Font: Match your document/presentation

Histogram:
  - Bucket size: 5 (for exam data)
  - Show item dividers: Yes (adds lines between bars)

Chart & axis titles:
  - Chart title: "Exam Score Distribution (n=60)"
  - Horizontal axis: "Score Range"
  - Vertical axis: "Number of Students"

Legend:
  - Position: None (unnecessary for single-series histograms)

Horizontal/Vertical axis:
  - Min/Max values: Set explicitly to prevent auto-scaling issues
  - Gridlines: Reduce count for cleaner appearance

Color choices matter: Use a single color for standard histograms. Reserve multiple colors for comparative histograms showing different groups.

Advanced Tips and Troubleshooting

Real-world data creates real-world problems. Here’s how to handle common issues:

Empty cells within data range:

FREQUENCY ignores empty cells, but the built-in histogram may miscount. Clean your data first:

=FILTER(A2:A100, A2:A100<>"")

This creates a clean range without blanks.

Text values mixed with numbers:

Identify non-numeric values with:

=SUMPRODUCT(--(ISNUMBER(A2:A100)))

Compare this count to your expected data points. If they don’t match, you have text values masquerading as numbers.

Manual binning with COUNTIF:

For even more control than FREQUENCY, use COUNTIF to count values in specific ranges:

=COUNTIF(A:A, ">="&B2) - COUNTIF(A:A, ">="&B3)

This counts values greater than or equal to B2, then subtracts values greater than or equal to B3—giving you the count between those boundaries.

For a complete manual bin setup:

B           C                                           D
Lower       Upper                                       Count
60          70      =COUNTIF($A:$A,">="&B2)-COUNTIF($A:$A,">"&C2)
70          80      =COUNTIF($A:$A,">="&B3)-COUNTIF($A:$A,">"&C3)
80          90      =COUNTIF($A:$A,">="&B4)-COUNTIF($A:$A,">"&C4)
90          100     =COUNTIF($A:$A,">="&B5)-COUNTIF($A:$A,">"&C5)

Exporting and sharing:

Right-click your histogram and select “Download” for PNG, PDF, or SVG formats. For presentations, PNG works universally. For print or scaling, use SVG.

To share interactively, publish the entire sheet (File → Share → Publish to web) and embed the chart directly.

Conclusion

Use the built-in chart tool when you need a quick visualization and Google’s automatic bucketing works for your data. This covers exploratory analysis, quick checks, and most presentation needs.

Switch to the FREQUENCY function when you need bins aligned to specific thresholds, when comparing datasets that must use identical bin boundaries, or when the automatic bucketing misrepresents your data’s distribution.

The manual COUNTIF approach makes sense for complex binning logic or when you need to integrate histogram counts into larger calculations.

Whichever method you choose, remember that bucket size selection is where most histogram analysis succeeds or fails. Start with the square root of your data point count as a rough bucket number, then adjust based on what patterns emerge.