How to Create a Histogram in Google Sheets (Step by Step)

Histograms are an incredibly useful way to visualize data distribution within a larger range. They group data into “blocks” of closely related values instead of sorting them by unique values. Histograms are especially valuable in the business world (especially in the service industry) and are often used in boardroom presentations.

In this tutorial, I will show you how to create histograms in Google Sheets.

Creating a Histogram in Google Sheets

Before you can create a histogram in Google Sheets, you need data to work with. In the following example, we will use the ages of 15 hypothetical people who responded to a survey.

The following steps explain how to create a histogram in Google Sheets:

1. Select the data you want to use to populate the histogram, including the header text. In our example case, we selected the header “Age” and 15 values (see the yellow rectangle).

2. Next, open the “Insert” dropdown menu and select “Chart” from the list of options (see the blue arrows).

3. On the “Chart editor” tab, select the dropdown menu under “Chart type,” unless Google Sheets automatically selected “Histogram.”

4. Select the histogram chart option from the list under the “Other” section (see the yellow rectangle).

5. Congratulations! You have created a histogram. However, you can further customize the histogram to better express the data points.

Note – if you need to access the Chart editor menu, follow these steps:

• Click on the chart.
• Click on the hamburger icon in the top right corner.
• Select “Edit chart” from the dropdown menu.

Customizing the Histogram in Google Sheets

Google Sheets offers many customization and formatting options that you can use to better present information. Let’s take a high-level look at what each chart editor feature does.

Configuration Tab

You are already familiar with this tab as we used it to set the chart type. Some options on this tab help expand the scope of your histogram, while others help orient the data based on how to interpret columns and rows.

• Data range: This feature is useful if you want to include multiple columns and multiple rows in your histogram. For example, you can change this value to “A1:A16,B1:B16” to include rows 1 to 16 in columns A and B.
• Series – Add series: This feature is also used to add additional columns or rows to the histogram. To use it, select “Add series,” highlight the series you want to add, and then click “OK.”

• Switch rows/columns: Select this option to toggle between column and row data orientation.
• Use row 1 as headers: This sets the top row value as the header in the chart legend.
• Use column A as labels: This uses the values in column A as the header and legend for the chart.

Customize Tab

There are many customization options available in Google Sheets to personalize the histogram chart. Most of these options are self-explanatory, and you will know exactly what they do when you test them. I will also cover some useful options that may require some explanation.

Histogram

Although there are only three options, the histogram options section is the most powerful tool in Google Sheets for data formatting.

• Display bin separators (checkbox): Check this box to add a line between each chart element. Depending on the data, this can better represent the distribution.
• Box size: This allows you to choose the range of values for each box. Google Sheets performs automatic sorting. You can set ranges in 1, 2, 5, 10, 25, and 50 increments.
• Outlier percentile: Use this option to group outliers with the nearest relevant box.

What is the Right Box Size?

Choosing the right number and range of values for each box is a combination of science and art. There are techniques to calculate the ideal number of boxes. However, you can adjust the boxes in a way that resembles how a person would group them.

You can mathematically estimate the number of boxes to use by taking the square root of the number of values for the chart and rounding up. For example, if you have 10 numbers, the rounded square root is 4. If you have 100 numbers, the rounded square root (in this particular case) is 10.

However, when working with ranges of numbers that have relevant increments (such as grade ranges, speed limits, and time intervals), it is better to use them instead.

It may be helpful to use the square root method and then match the box intervals to the nearest common measure. Grouping ages into 7 or 11-year intervals wouldn’t make much sense, but 5, 10, or 15 years would.

You can also use the minimum and maximum ranges on the vertical and horizontal axes, which we will discuss later, to fine-tune the histogram.

Chart and Axis Titles

This submenu is useful for adjusting how the chart title, chart subtitle, horizontal axis title, and vertical axis title are displayed. You can adjust the following settings for each of them:

• Text: The displayed text for the selected feature. For example, the chart title could be “Age Histogram” or “Respondent Age Histogram.”
• Font: Select the font style.
• Font size: Choose the text size.
• Format: Bold, italicize, and align the text.
• Text color: Choose the text color.

Series

This tab allows you to choose the bar color for each series in your histogram. This is especially useful if you have a histogram comparing multiple series.

To adjust the series color, follow these steps:

1. Select the series you want to adjust in the main dropdown menu (upper black arrow).
2. Select the color you want to assign to the series from the “Color” dropdown menu (lower black arrow).

Legend

The “Legend” submenu allows you to make adjustments to the chart legend. The options allow for the following adjustments:

• Position: Moves the legend up, down, left, or right relative to the chart. Select “None” to remove the legend or “Inside” to move the legend on top of the chart.
• Legend font: Changes the font style of the legend.
• Legend font size: Adjusts the font size of the legend.
• Legend format: Bold or italicize the legend with these options.
• Text color: Sets the text color of the legend.

Horizontal Axis and Vertical Axis

These two sections of options serve the same purpose but for the horizontal axis and vertical axis, respectively. These options allow you to adjust the range of the chart, which can provide important context to a histogram. Additionally, they allow you to modify the appearance of information on the axes.

Min and Max (Set Range): These options are very useful for adding context to a chart. You can choose both the lowest (min) and highest (max) values represented in the histogram.

In our example data, Google Sheets is grouping age groups in a way that doesn’t align with how people would group ages. The default grouping looks like this:

The data range goes from 18 to 65. Google Sheets automatically divided the data into ranges of 15-26, 36-37, 37-48, 48-59, and 59-70 as the default ranges.

It is unlikely that anyone would choose to group people according to these age groups if given the chance.

Let’s start over and adjust the box size to an interval that makes sense to a person in the histogram submenu. By changing the “Box size” to 10, we are dividing the age groups into decades.

So, we can use “Min” and “Max” to represent age groups in decades, starting from 0. In this example, we set the “Min” value to 10 and the “Max” value to 70.

Now, we display the histogram data in a way that makes sense to someone looking at the data. We can choose to divide the data into different age ranges like 5, 15, or 20 based on the situation.

The other options pertain to formatting axis label text:

• Label font: Change the font of the axis.
• Label font size: Set the text size.
• Label format: Bold or italicize the text.
• Text color: Change the text color.
• Label rotation: Display the axis labels with an angle. This can make it easier to read a crowded chart.

This example shows the horizontal axis with Arial font, font size 18, bold, italicized, and rotated 30 degrees:

That’s how you can create a histogram chart in Google Sheets and use the various customization options to make it your own.

Remember, context is extremely important when interpreting a histogram. While Google Sheets does a good job of formatting a histogram, a little customization can make all the difference.

That’s how you can create a histogram in Google Sheets and customize its display.