Learn How to Make a Histogram in Google Sheets

Creating visualizations of your data can help extract vital information. It can let you see the bigger picture and understand your data more deeply.

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 essential 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 customize it.

What is a Google Sheets Histogram?

A histogram is a chart showing 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. Basically, this means that a histogram shows the frequency of how often a value appears in a range or data. It looks similar to a bar graph, but there are a lot of differences between the two. The vertical axis often represents the frequency.

For example, in a census with a range of age values from 18 to 35, you can use a histogram to show often the ages appeared. It can also be used with a data range, for example, how often the ages 10-20 appeared in the census.

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 Google Spreadsheet histogram is primarily different from a bar graph in terms of the application.

A Google Sheets histogram is used to understand the data distribution, 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.

Histograms in Google Sheets work the same way as all other histograms. In this tutorial, we will show you how to create a histogram in Google Sheets to visualize your data and how to further customize the histogram according to your requirement. You can also use a line of best fit together with a histogram.

How to Make a Histogram in Google Sheets

To understand how to create a Google Sheet histogram, we will 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 know how to create histograms in Google Sheets 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 after creating a histogram in Google Sheets.

Customizing the Histogram in Google Sheets

Now that you know how to make a histogram in Google Sheets, 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 Google Sheet 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 and the titles for both the 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 setting 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.

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:

Histogram 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 not to have any ticks at all.

Gridlines and ticks in histogram

Histogram Google Sheets FAQ

How Do I Make a Histogram in Google Sheets?

  1. Highlight the data you want to make a histogram with
  2. Navigate to File>Chart or click the Chart shortcut button
  3. In the Chart menu, change the Chart type to Histogram

How Do You Change the Interval on a Histogram in Google Sheets?

  1. Navigate to the chart menu
  2. Under Histogram find Bin sizes and adjust the figure to the desired interval

How Do You Title a Histogram in Google Sheets?

If it isn’t automatically added, there should be a space at the top of your chart you can click and add text. Or, you can use the Chart menu under Title.

How Do You Make a Relative Frequency Histogram in Google Sheets?

Relative frequency histograms charts use percentages in the y-axis to represent the frequency while the horizontal axis shows the class. It is an easy way to analyze data.

The first step will be to make a regular data histogram by highlighting your data points and then go to Insert > Chart and in Chart type, choose a Histogram chart in the options. You can customize your histogram however you wish in the chart format window and a chart title if you wish.

From there, we can create a relative frequency table first. To get the frequency of the data,

We use the formula

=FREQUENCY(data,class)

Here is an example:

=FREQUENCY(B2:B20,F2:F5)
Adding a frequency chart

To get the relative frequency, we convert the frequencies into percentages by dividing them by the sum of the values. To do this in our example, we input the formula:

=G2/SUM($G$2:$G$5)
Relative frequency chart

Now we can convert the relative frequencies into percentages. Select the column, and on the toolbar, click Format as percentages.

Relative frequency chart

Now we have a relative frequency table that we can use to create our relative frequency histogram. Select the class column then hold the ctrl button and select the Relative frequency table.

Highlighting two data sets with ctrl

Go to Insert > Chart > Column chart

Relative frequency histogram

Now you have your relative frequency histogram in Google Sheets. It has the bar chart format, but the information is represented and can be used as a histogram. You can use the normal histogram to compare with the relative frequency histogram to ensure the data is accurate. You can do a lot with this histogram, like adding error bars.

If you’re having some trouble with this, you can:

Download Our Template

How Do I Make a Double Histogram in Google Sheets?

A double histogram contains two data distributions that are compared together. For example, if you have data on the sports that male students and female students participate in and you would like to compare the two, you can use a double histogram. Creating a double histogram in Google Sheets is a very simple process.

Sample data for a double histogram

To make a double histogram on Google Sheets, simply select your data set and go to Insert > Chart. In the charts tab choose the column chart and customize it as you see fit. With that you have your double histogram. That’s all on how to make histogram on Google Sheets with two data sets.

A double histogram

If you would like to use our example table, you can access it here.

The Bottom Line

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

We also showed you how to make 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:

Most Popular Posts

Sumit

Sumit

Google Sheets and Microsoft Excel Expert.

Leave a Comment