How to Make a Histogram in Google Sheets (An Easy Guide)

Creating visualizations of your data can help extract vital information. It can help you see the bigger picture and obtain a deeper understanding of your data.

With the help of visualization tools like charts, graphs, maps, etc. you can easily understand the dynamics, trends, and relationships among data items and draw important inferences.

A histogram is one such helpful visualization tool that helps you understand the distribution of your data.

In this tutorial, I will show you how to make a histogram in Google Sheets and how to customize it.

What is a Histogram?

A histogram is a kind of chart that shows how a variable is distributed. It divides the range of your data into intervals, displaying how many of the data values fall into each interval.

Histogram chart in Google Sheets example

Each of these intervals is displayed in the form of ‘bins’ or ‘buckets’. Visually, the bins may look like bars of a bar graph, but a histogram is actually quite different from a bar graph.

How are Histograms different from Bar Graphs?

A Histogram is primarily different from a bar graph in terms of the application.

A histogram is used to understand the distribution of data, while a bar graph is used to compare variables. The kind of data plotted by histograms and Bar Graphs is also different.

Histograms mainly plot quantitative data. So you plot how data of a single category is distributed. Bar graphs, on the other hand, plot categorical data. So you plot the quantity or frequency of data in different categories.

In this tutorial, we will show you how to use Google Sheets to create a histogram to visualize your data and how to further customize the histogram according to your requirement.

How to Make a Histogram in Google Sheets

To understand how to create a histogram, we are going to use the data shown in the image below:

Data for creating the histogram

This dataset contains scores of students in an exam. We want to create a histogram to understand how the student scores in the exams were distributed.

To make the histogram for the above data, follow these steps:

  1. Select the data you want to visualize in your histogram. You can also include the cell containing the column title. In our example, let’s select the cell range A1:A12.
  2. Click the Insert menu from the menu bar.Click on Insert
  3. Select the Chart option.Click on the chart option
  4. This will display a chart on the worksheet and a Chart editor sidebar on the right side of the window.Chart and the chart editor
  5. Google usually tries to understand your selected data and displays the chart it thinks as the best representation for it. Ideally, it should display a histogram. However, if you see some other kind of chart, go to step 6.
  6. Select the Setup tab from the Chart editor sidebar and click on the dropdown menu under “Chart type”. From the chart options that you see, select the “Histogramchart”. It should be visible under the “Other” category.Select Histogram in chart setup

You should now see a histogram on your worksheet.

Histogram in Google Sheets

Google Sheets performs its own calculations on your data and displays what it believes to be the optimal number of bins for your histogram.

Its calculations, however, are usually far from perfect. As such, you will usually feel the need to customize the histogram to give it the look and functionality you want.

Customizing the Histogram in Google Sheets

Google Sheets doesn’t just let you create a generic histogram, it also lets you customize it to your liking. Usually, the Chart editor has a ‘Customize’ tab that lets you enter all your specifications.

However, sometimes the Chart editor goes away after your histogram has been created.

To make it appear again and to customize your histogram, do the following:

  1. Click on the graph.
  2. You should see an ellipsis (or hamburger icon) on the top right corner of the box containing the graph.Click on the three dots in the histogram chart
  3. Click on the ellipsis and select “Edit the chart” from the drop-down menu.Click on the Edit Chart option
  4. This will make your Chart editor sidebar appear again.Chart Editor setup
  5. Click on the Customize tab. You can now make your required customizations.Click the customize tab

Changing the Chart Style

The Chart style category in the Chart editor lets you set the background color, border color, font style, and size of your chart.

In our example, we changed the background color to “light green 3”, and allowed the other settings to remain the same.

Select the histogram background color

 

Adjusting Histogram Bin Sizes

You can use the Histogram category of the Chart editor to adjust the bin sizes to your requirement. For example, the intervals of scores displayed along the x-axis have very arbitrary sizes.

Distributing exam scores into these intervals does not really make much sense in practice.

Histogram in Google Sheets

So it would be better if the distributions were in intervals of 10.

For this, we need to change the ‘Bucket sizes’ to 10, as shown below:

Select the bucket size for the histogram

Your chart should then display student score distributions in intervals of 10:

Bucket size of 10

The outlier percentile drop-down lets you group data outliers with the closest relevant bucket. Besides this, the Show item dividers checkbox lets you add a line between each item in the chart.

This could sometimes help make the histogram easier to read and understand.

Show item divider in histoogram in Google Sheets

Chart and Axis Titles

This category lets you provide the text and formatting for the chart title and subtitle as well as the titles for both x and y axes.

For example, you can use it to give a title for the vertical axis, by selecting the “Vertical axis title” option from the dropdown menu and then set the title as “Student Count”.

Vertical axis title

Your histogram would then look like this:

Y Axis in Histogram in Google Sheets

Series

This category lets you choose the colors for the bars (or bins) of your histogram. For example, you can use it to give your bins a “light red berry” color.

Chart style options

Your histogram would then look like this:

Histogram with red bars

This becomes even more helpful when you want to compare different variable distributions in one histogram. Then you could have different colors for different series.

For example, if you had to compare the distribution of marks for two different classes, you could use one color for grade 6 and another for grade 7 (say).

Multicolored histogram chart

Legend

The “Legend” category, as its name suggests, lets you provide settings and formatting for the histogram legend. Using this, you can provide the following settings for the legend:

  • Position of the legend, relative to the graph. If you don’t want to keep a legend, select “None”.
  • Legendfont to set the display font for the legend.
  • Legend font size to set the font size for the legend.
  • Legend format to make the legend bold and/ or italicized.
  • Text color to set the legend’s text color.

In our example, we don’t really need a legend, since there’s just one variable. So we can set the legend position to none.

Set the legend to none

Horizontal and Vertical Axes

You can use this category to change the range of the histogram. For example, you might want to reduce the range of values within which you want the bins to be distributed.

In our example, it would make sense to distribute the scores between 0 and 100.

For this, you will need to change the min and max values for the Horizontal axis category to 0 and 100 respectively.

Min and max value for horizontal axis

Adjusting the min and max inputs really helps you provide context to your histogram.

Some other settings available under these categories include:

  • Label font to change the font for the horizontal and/or vertical axis.
  • Label font size to set the font size for the x and/or y-axis values.
  • Label format to make the x and/or y-axis values bold and/or italicized.
  • Text color to change the text color of the
  • Slant labels to display the axis labels at a particular angle. For example, you might want to display the labels at an angle of 90 degrees from the horizontal axis as shown below.Slant Labels

Your histogram would then look like this:

Historam with slant labels

Gridlines and Ticks

Finally, you can format the histogram to contain major and/or minor gridlines. You can also set what colors you want the gridlines to be, or choose to not have them at all.

This category also lets you set and format major and/or minor ticks on your histogram’s vertical and horizontal axes. As before, you can choose to not have any ticks at all.

Gridlines and ticks in histogram

With that, we end this tutorial. We showed you why and how you can use a histogram.

We also showed you how you can create a histogram in Google Sheets and customize its various components to gain full control over its format and settings.

We hope this tutorial has been helpful to you.

Other Google Sheets tutorials you may like:

Leave a Comment