How to Create a Histogram Chart in Google Sheets

Published on

Creating a histogram chart in Google Sheets is straightforward. However, it is equally important to understand what a histogram represents and how it helps analyze data.

When dealing with a large number of data points, a histogram chart organizes the data into a frequency distribution, making it easier to interpret patterns and trends.

What Is a Histogram?

In a histogram, values are grouped into classes or bins, displayed consecutively along the x-axis. For example, if you have student marks, you can group them into bins like 50–60 for one group, 60–70 for another, and so on. The height of each column represents the frequency (the number of data points in each bin), making it easier to identify distributions and patterns in the dataset.

In Google Sheets, you don’t need to manually calculate frequencies using formulas like:

=SUMPRODUCT(ISBETWEEN(range, lower_bound, upper_bound, TRUE, FALSE))

or

=FREQUENCY(data, classes)

Google Sheets automates this process when you create a histogram chart, simplifying data visualization significantly. Select your data, choose the histogram chart type, and Google Sheets will group your data into bins automatically. You can also customize bin sizes and other settings as needed.

Examples of Histogram Charts in Google Sheets

1. Daily Online Income from Advertisements and Affiliate Marketing

Many bloggers monitor their blogs by placing ads or affiliate links to generate income for covering expenses like hosting and maintenance. These earnings can fluctuate due to factors such as weekends, weekdays, and variations in site traffic.

For instance, if daily earnings range from $50 to $90, a blogger could group these into bins like 50–55, 55–60, 60–65, and so on. If earnings on a particular day are $68, they would fall into the 65–70 range. A histogram chart would show which ranges occur most frequently, helping to visualize trends.

In the example below, you can see that earnings fell within the $60–65 range 11 times.

Histogram chart example 1 – Daily Online Income from Ads and Affiliate Marketing

2. Student Marks Distribution

Teachers can use a histogram chart to visualize the distribution of student marks across different ranges. For example, if 100 students’ marks are grouped into bins of 5 marks each (e.g., 65–70, 70–75, etc.), the histogram will show how many students scored within each range.

Histogram chart example 2 – Student Marks Distribution

In this case, 22 students scored between 65–70.

How to Create a Histogram Chart in Google Sheets

Here’s a step-by-step example:

  1. Data Setup
    Suppose column A contains the days of the month (1–31), and column B contains daily earnings. The data range is A1:B32 (including headers).
  2. Steps to Create a Histogram Chart
    • Select B1:B32.
    • Go to Insert > Chart.
    • Google Sheets will likely default to a histogram chart. If not, under the Setup tab in the chart editor, choose “Histogram” as the chart type.
    • In the Customize tab, click on Histogram and set the bucket size (bin size). For example, setting it to 5 will create bins like 55–60, 60–65, and so on.

In histograms, the lower bounds are inclusive, while the upper bounds are exclusive. For example, the range 60–65 includes values ≥60 and <65.

Using a Column Chart to Mimic a Histogram

If you want to use custom bin sizes or categories, a column chart can mimic a histogram. Here’s how:

Lower bound, upper bound, category, and frequency distribution
  1. Set Up Bins
    Enter lower bounds in column D and upper bounds in column E.
  2. Calculate Frequencies
    • In G2, enter: =SUMPRODUCT(ISBETWEEN($B$2:$B$32, D2, E2, TRUE, FALSE))
      Drag it down to G7.
    • Alternatively, clear G2:G8 and enter: =FREQUENCY(B2:B32, E2:E7)
  3. Create Categories
    Combine the lower and upper bounds in column F for the x-axis labels by entering the following array formula in cell F2: =ArrayFormula(D2:D7&"-"&E2:E7)
  4. Insert a Column Chart
    Select F2:G7, then go to Insert > Chart > Column Chart.
Column Chart as an Alternative to Histogram

A column chart allows you to customize bin sizes and categories, offering flexibility for specific data needs.

That’s how you can create a histogram chart in Google Sheets or use a column chart as an alternative. Enjoy exploring your data!

Sample Sheet

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.