How to Make a Histogram in Google Sheets (Step-by-Step)

A Stepwise Guide to Constructing a Histogram in Google Sheets

Histograms serve as a remarkably valuable method for visualizing the distribution of data within a broader range, depicting its groupings.

Unlike categorizing data based on distinct values, histograms sort them into clusters of closely linked values. Google Sheets presents an effortlessly accessible built-in tool for generating histogram graphs.

Rather than just enumerating how frequently a particular test score was achieved, a histogram arranges scores within relative intervals.

To illustrate, if we have the scores of 10 students in a class, a histogram allows us to categorize and observe the number of students falling within the 51-60 marks range, 61-70 range, 71-80 range, and so forth.

Histograms also prove their significance in the business realm, particularly in the service industry, and they commonly make appearances during boardroom presentations.

Within this tutorial, I will illustrate the process of forming histograms in Google Sheets.

Table of Contents

The Process of Crafting a Histogram in Google Sheets

Before embarking on creating a histogram in Google Sheets, it’s imperative to possess data to work with.

For the forthcoming illustration, we shall employ the ages of 15 hypothetical survey respondents.

Here’s how to go about generating a histogram in Google Sheets:

Highlight the dataset that you intend to employ for populating the histogram, inclusive of the heading text. In this example, we have highlighted the “Age” header along with the 15 values (as depicted by the yellow rectangle).
Next, access the “Insert” dropdown and opt for “Chart” from the array of choices (as indicated by the blue arrows).
On the “Setup” tab of the chart, direct your attention to the dropdown menu beneath “chart type,” unless Google Sheets has automatically chosen “Histogram.”
Select the Histogram graph type from the roster under the “Other” segment (as highlighted by the yellow rectangle).
Congratulations! You have now successfully produced a histogram. However, there remains room for further customization to more effectively communicate the data’s nuances.

Nota Bene — In case you need to access the chart Setup/Customization menu, follow these steps:

Click on the graph.
Navigate to the icon resembling a hamburger at the upper right corner.
Opt for the “Edit chart” choice from the ensuing dropdown menu.
Tailoring the Histogram in Google Sheets

Google Sheets extends an extensive selection of customization and formatting possibilities to enhance the presentation of information. We shall provide a succinct overview of each aspect of the chart editor’s functionalities.

The Setup Tab

Since we’ve already touched upon this tab while setting the chart type, several options on it prove instrumental in expanding the histogram’s scope. Other features aid in aligning the data to better interpret the columns and rows.

Data Range: This feature proves advantageous when dealing with multiple columns and rows in the histogram. For instance, you could modify this parameter to “A1:A16,B1:B16” to encompass rows 1 through 16 in both columns A and B.
Series – Adding Series: This feature proves valuable for introducing additional columns or rows within the histogram. To utilize it, choose “Add Series,” highlight the series you wish to append, and confirm with “OK.” This action results in your histogram chart containing two side-by-side data series.
Switch Rows/Columns: Employ this option to toggle between vertical and horizontal data orientation.
Use Row 1 as Headers: By activating this setting, you establish the top row’s value as a header for the chart’s legend.
Use Column A as Labels: This selection designates column A values as both the header and the key for the chart.

The Customize Tab

Within this realm, Google Sheets furnishes numerous options for customizing the histogram chart.

Most of these options are fairly intuitive, and experimentation will provide clarity regarding their functions.

We shall also delve into some options that may prove particularly useful but require some elucidation.

Histogram

Despite comprising only three choices, the Histogram options section emerges as the most potent tool in Google Sheets for data formatting.

Show Item Dividers (Checkbox): Ticking this box introduces lines between each item on the chart, enhancing the representation of data distribution.
Bucket Size: This parameter empowers you to determine the value range assigned to each bucket. Google Sheets integrates an automatic sorting feature. Your bucket sizes can be set in increments of 1, 2, 5, 10, 25, and 50.
Outlier Percentile: This option facilitates grouping data outliers with the nearest relevant bucket.

Identifying the Optimal Bucket Size

Selecting the suitable number of buckets and their size constitutes a blend of scientific analysis and artistic intuition. Various techniques exist for calculating the ideal number of buckets.

Nevertheless, an approach that resonates with human cognition often involves adapting the bucket sizes to align with how people naturally comprehend data divisions.

A mathematical estimation for determining the number of buckets relies on the square root of the data count, rounded up.

If there are 10 data points, the rounded-up square root is 4. With 100 data points, the rounded-up square root equals the number of data points.

However, when dealing with data ranges that possess significant increments (such as grade ranges, speed limits, or time intervals), it’s preferable to adhere to those increments instead.

Leveraging the square root method and then harmonizing the bucket intervals with common measurements can prove effective. Grouping ages into intervals of 7 or 11 years might not be intuitive, but opting for intervals of 5, 10, or 15 makes more sense.

Furthermore, the later-discussed adjustments to the Vertical Axis and Horizontal Axis’s minimum and maximum ranges can provide fine-tuning opportunities for the histogram.

Chart & Axis Titles

This submenu proves invaluable for adjusting the presentation of the chart title, subtitle, horizontal axis title, and vertical axis title. Each of these can be configured as follows:

Text: This denotes the text displayed for the selected feature. For instance, the chart title could read “Age Histogram” or “Histogram of Respondent Ages.”
Font: Choose the desired font family.
Font Size: Determine the size of the displayed text.
Format: Opt for bold, italics, and alignment options.
Text Color: Assign a color to the text.

Series

This tab empowers you to select distinct bar colors for each series within your histogram. Its utility shines particularly when dealing with a histogram that compares multiple series.

Modifying series colors involves the following steps:

Select the series you intend to modify from the primary dropdown menu (indicated by the upper black arrow).
Choose the color that represents the series from the “Color” dropdown menu (shown by the lower black arrow).
Legend

The “Legend” submenu affords control over its namesake (depicted in the blue rectangle above). The available options enable the following adjustments:

Position: This relocation feature places the legend atop, below, to the left, or on the right relative to the graph. Opt for “None” to eliminate the legend or “Inside” to position it atop the chart.
Legend Font: Alter the font style used for the legend.
Legend Font Size: Adjust the font size of the legend text.
Legend Format: Apply bold or italics formatting to the legend text.
Text Color: Set the text color for the legend.

Horizontal Axis & Vertical Axis

Although these two sections serve the same overarching purpose, they cater to the horizontal axis and vertical axis individually.

These options permit you to adjust the graph’s range, thereby furnishing essential context to the histogram. Additionally, they offer opportunities to alter the appearance of the axis-related information.

Min and Max (Range Setting): These prove exceptionally useful for imbuing the graph with context. They allow you to stipulate both the lowest (min) and highest (max) values represented in the histogram.

In our provided data example, Google Sheets divided age groups in a manner incongruous with how individuals typically conceive of age ranges. The default segmentation appears as follows:

The data spans from 18 to 65. Google Sheets’ default segmentation results in ranges of 15-26, 36-37, 37-48, 48-59, and 59-70.

Given a choice, it’s improbable that anyone would opt to categorize age groups in this manner.

Stepping back, it becomes evident that adjusting the bucket size to an interval that aligns with human thinking would be prudent. Altering the “Bucket Size” to 10 splits the age groups into decades:

This alteration enables the use of “Min” and “Max” values to represent decade-based age groups, commencing from 0. For this example, we have set the “Min” value to 10 and the “Max” value to 70.

Consequently, the histogram data is presented in a way that resonates with human sensibilities. Depending on the context, it’s possible to further divide the data into different age ranges like 5, 15, or 20.

Additional options concern the formatting of axis text:

Label Font: Modify the font style for the axis labels.
Label Font Size: Specify the text size.
Label Format: Opt for bold or italics formatting.
Text Color: Alter the color of the text.
Slant Labels: Angle the axis labels for improved readability, especially when dealing with densely populated graphs.

The given example showcases the horizontal axis with an 18-point Arial font that’s both bold and italicized, slanted at a 30-degree angle:

And thus, the procedure for creating a histogram in Google Sheets unfolds, offering the flexibility to tailor its appearance. Always bear in mind that context plays a pivotal role in interpreting a histogram.

While Google Sheets accomplishes adept formatting of histograms, a bit of customization can greatly enhance their communicative power.

This concludes the guide on crafting a histogram chart in Google Sheets, complete with diverse customization options to shape it according to your preferences.