Column and Bar Charts with Google Sheets

Column and bar charts are some of the most common types of charts in data visualization (column charts are just vertical bar charts). They are used to compare values across categories.

In this tutorial, we will use three small datasets to build interactive separated, grouped, and stacked bar charts in Google Sheets:

  • Obesity in the US (by US CDC and StateOfObesity.org project)
  • High-Calorie Fast-Food Items
  • Global Database on Body Mass Index by World Health Organization

Grouped Column and Bar Charts

Figure 5.10 shows differences in obesity between men and women, grouped together in three age brackets to allow for easier gender comparisons across the same ages. In the interactive web version, hover over columns and see tooltips with data.

Figure 5.10: Grouped column chart with data from StateOfObesity.org. Explore the full-screen interactive version.

The following steps will help you recreate an interactive grouped column (or horizontal bar) chart.

  1. Open Google Sheet Column chart with grouped data template in your browser.

  2. Sign in to continue to Google Sheets (which is part of Google Drive). If you don’t already have a Google account, you can create one.

  3. Select File > Make a Copy to save your own version to your Google Drive, as shown in Figure 5.11.

Make your own copy of the Google Sheet template.

Figure 5.11: Make your own copy of the Google Sheet template.

  1. To remove the current chart from your copy of the spreadsheet, float your cursor to the top-right corner of the chart to make the 3-dot (kebab) menu appear, and select Delete, as shown in Figure 5.12.
Float cursor in top-right corner of the chart to make the 3-dot (kebab) menu appear, and select Delete.

Figure 5.12: Float cursor in top-right corner of the chart to make the 3-dot (kebab) menu appear, and select Delete.

Note: Another name for the 3-dot menu symbol is the “kebab menu” because it resembles Middle Eastern food cooked on a skewer, in contrast to the three-line “hamburger menu” on many mobile devices, as shown in Figure 5.13.

You should be able to distinguish kebab from hamburger menu icons.

Figure 5.13: You should be able to distinguish kebab from hamburger menu icons.

  1. Format your data to make each column a data series, as shown in Figure 5.14, which means it will display as a separate color in the chart.
Format data in columns to make colored grouped columns in your chart.

Figure 5.14: Format data in columns to make colored grouped columns in your chart.

  1. Use your cursor to select only the data you wish to chart, then go to the Insert menu and select Chart, as shown in Figure 5.15.
Select your data and then Insert the Chart.

Figure 5.15: Select your data and then Insert the Chart.

  1. In the Chart Editor, change the default selection to Column chart, with Stacking none, to display Grouped Columns, as shown in Figure 5.16. Or select Horizontal bar chart if you have longer labels.
Change the default to Column chart, with Stacking none.

Figure 5.16: Change the default to Column chart, with Stacking none.

  1. To customize title, labels, and more, in the Chart Editor select Customize, as shown in Figure 5.17.
Select Customize to edit title, labels, and more.

Figure 5.17: Select Customize to edit title, labels, and more.

  1. To make your data public, go to the upper-right corner of your sheet to click the Share button, and in the next screen, click the words “Change to anyone with the link,” as shown in Figure 5.18. This means your sheet is no longer Restricted to only you, but can be viewed by anyone with the link. See additional options.
Click the Share button and then click Change to anyone with the link to make your data public.

Figure 5.18: Click the Share button and then click Change to anyone with the link to make your data public.

  1. To embed an interactive version of your chart in another web page, click the kebab menu in the upper-right corner of your chart, and select Publish Chart, as shown in Figure 5.19. In the next screen, select Embed and press the Publish button. See Chapter 7 Embed on the Web to learn what to do with the iframe code.
Select Publish Chart to embed an interactive chart on another web page, as described in Chapter 7.

Figure 5.19: Select Publish Chart to embed an interactive chart on another web page, as described in Chapter 7.

Note: Currently, there is no easy way to cite or link to your source data inside a Google Sheets chart. Instead, cite and link to your source data in the text of the web page. Remember that citing your sources adds credibility to your work.

Separated Column and Bar Charts

When you visualize independent categories of data, and you don’t want them to appear grouped together, then create a chart with separated columns (or horizontal bars, if you have long data labels). For example, Figure 5.20 is a separated bar chart of calorie counts of fast food items for two restaurant chains, Starbucks and McDonald’s. Unlike the grouped column chart in Figure 5.10, here the bars are separated from each other, because we do not need to make comparisons between sub-groups.

Figure 5.20: Separated bar chart with data from Starbucks and McDonalds. Explore the full-screen interactive version.

The only difference between making a grouped versus a separated chart is how you structure your data. To make Google Sheets separate columns or bars, you need to leave some cells blank, as shown in Figure 5.21. The rest of the steps remain the same as above.

Create a separated column or bar chart by leaving some cells blank.

Figure 5.21: Create a separated column or bar chart by leaving some cells blank.

To create your own separated column or bar chart using the fast-food example, make a copy of Google Sheet Separated Bar Chart template.

Stacked Column and Bar Charts

Stacked column and bar charts can be used to compare subcategories. They can also be used to represent parts of a whole instead of pie charts. For example, the stacked column chart in Figure 5.22 compares the percentage of overweight residents across nations, where colors allow for easy comparisons of weight-group subcategories across nations.

Figure 5.22: Stacked column chart with data from WHO and CDC. Explore the full-screen interactive version.

To create a stacked column or bar chart, structure your data so that each column will become a new series with its own color, as shown in Figure 5.23. Then in the Chart Editor window, choose Chart Type > Stacked column chart (or Stacked bar chart). The rest of the steps are similar to the ones above.

Create a stacked column or bar chart by structuring your data as shown.

Figure 5.23: Create a stacked column or bar chart by structuring your data as shown.

To create your own stacked column or bar chart using the international weight level example, visit the Google Sheets Stacked Column Chart template and make a copy of the spreadsheet.

To change colors of series (for example, to show Overweight category in red), click the kebab menu in the top-right corner of the chart, then go to Edit Chart > Customize > Series. There, choose the appropriate series from the dropdown menu, and set its color from the Color dropdown menu that appears.

Histograms

Histogram is a type of bar chart that represents distribution of items, whether numerical or categorical. To build a histogram, you need to assign each data point to one of the non-overlapping buckets (or bins).

Let’s say you want to know what time of day you are more likely to get an email. One approach would be to download metadata about all emails you received in 2020, and assign them to a bucket between 0 and 23 according to the email hour. Hours will become your bins, and email counts will be your frequency data. Then your final dataset would look something like this:

| Hour | Emails |
| ---- | ------ |
| 0    | 12     |
| 1    | 11     |
| 2    | 7      |
.................
| 22   | 34     |
| 23   | 22     |

You can now make a histogram. The good news is, Google Sheets considers histograms to be regular column charts, so you should be able to use a previous tutorial to make one.

Select two columns with the data you want to visualize, and go to Insert > Chart. In the Chart editor window, in the Setup tab, select Chart type > Column chart. See the result in Figure 5.24

Figure 5.24: Histogram chart with fictitious source data. Explore the full-screen interactive version.

If you wish to use our fictional email dataset to create your own histogram, you can make a copy of the Histogram Chart template.

Bins in a histogram should span (in other words, “cover”) the entire range of values of your dataset. This way you don’t leave out any data. We recommend you use bins of the same size (like 24 1-hour bins, or four 6-hour bins) to ensure readers can compare across bars. For example, if you want to create a less detailed histogram, you can combine hours into larger bins, such as Morning, Afternoon, Evening, and Night to cover the hours of 6–11, 12–17, 18–23, and 0–5, respectively. Then your dataset will look like:

| TimeOfDay | Emails |
| --------- | ------ |
| Morning   | 353    |
| Afternoon | 497    |
| Evening   | 279    |
| Night     | 37     |