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.
The following steps will help you recreate an interactive grouped column (or horizontal bar) chart.
Open Google Sheet Column chart with grouped data template in your browser.
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.
Select File > Make a Copy to save your own version to your Google Drive, as shown in Figure 5.11.
- 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.
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.
- 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.
- 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.
- 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.
- To customize title, labels, and more, in the Chart Editor select Customize, as shown in Figure 5.17.
- 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.
- 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.
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.
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.
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.
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.
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.
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
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 |