- Bar and Column Charts

Before you begin, be sure to review the pros and cons of designing charts with Google Sheets in the prior section. In this section, you’ll learn how to create bar and column charts, the most common visualization methods to compare values across categories. We’ll focus on why and how to create three different types: grouped, split, and stacked. For all of these, we blend the instructions for bar and column charts because they’re essentially the same, though oriented in different directions. If your data contains long labels, create a horizontal bar chart, instead of a vertical column chart, to give them more space for readability.

Grouped Bar and Column Charts

A grouped bar or column chart is best to compare categories side-by-side. For example, if you wish to emphasize gender differences in obesity across age brackets, then format the male and female data series together in vertical columns in your Google Sheet, as shown in Figure 7.12. Now you can easily create a grouped column chart to displays these data series side-by-side, as shown in Figure 7.13.

To create a grouped bar or column chart, format each data series vertically in Google Sheets.

Figure 7.12: To create a grouped bar or column chart, format each data series vertically in Google Sheets.

Figure 7.13: Grouped Column chart: Explore the interactive version. Data from StateOfObesity.org.

To create your own interactive grouped column (or bar) chart, use our template and follow these steps.

  1. Open our Grouped Column chart template in Google Sheets with US obesity data by gender and age. Sign in to your account, and go to File > Make a Copy to save a version you can edit to your own Google Drive, as shown in Figure 7.14.
Make your own copy of the Google Sheet template.

Figure 7.14: 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 7.15.
Float cursor in top-right corner of the chart to make the 3-dot kebab menu appear, and select Delete.

Figure 7.15: 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 7.16. Software developers must be hungry.

Distinguish between the hamburger verus kebab menu icons.

Figure 7.16: Distinguish between the hamburger verus kebab menu icons.

  1. Format your data to make each column a data series (such as male and female), as shown in Figure 7.12, which means it will display as a separate color in the chart. Feel free to add more than two columns.

  2. 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 7.17.

Select your data and Insert the Chart.

Figure 7.17: Select your data and 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 7.18. Or select Horizontal bar chart if you have longer labels.
Change the default to Column chart, with Stacking none.

Figure 7.18: 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 7.19. Also, you can select the chart and axis titles to edit them.
Select Customize to edit title, labels, and more.

Figure 7.19: 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 7.20. 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 7.20: 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 7.21. In the next screen, select Embed and press the Publish button. See Chapter 10: 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.

Figure 7.21: Select Publish Chart to embed an interactive chart on another web page.

Unfortunately Google Sheets functionality is very limited when it comes to displaying error bars, or uncertainty. You can only assign either constant numbers or percent values as error bar values to individual series (not data points). To do so, in Chart editor, select Customize tab, scroll down to Series and select a series from the dropdown. Check Error bars, and customize its value as either percent or a constant value, as shown in Figure @ref(fig:images/07-chart/chart-error-bar.png). This setting will then be applied to all data points from that series.

Google Sheets is very limited when it comes to setting error bars.

Figure 7.22: Google Sheets is very limited when it comes to setting error bars.

Since there is no easy way to cite or link to your source data inside a Google Sheets chart, you will need to add this information to the text of the web page that contains your interactive chart. Remember that citing your data sources adds credibility to your work.

Split Bar and Column Charts

A split column (or bar) chart is best to compare categories in separate clusters. For example, imagine you wish to emphasize calorie counts for selected foods offered at two different restaurants, Starbucks and McDonalds. Format the restaurant data in vertical columns in your Google Sheet, as shown in Figure 7.23. Since food items are unique to each restaurant, only enter calorie data in the appropriate column, and leave other cells blank. Now you can easily create a split bar (or column) chart that displays the restaurant data in different clusters, as shown in Figure 7.24. Unlike the grouped column chart previously shown in Figure 7.13, here the bars are separated from each other, because we do not wish to draw comparisons between food items that are unique to each restaurant. Also, our chart displays horizontal bars (not columns) because our some data labels are long.

To create a split bar (or column) chart, format each data series vertically, and leave cells blank where appropriate.

Figure 7.23: To create a split bar (or column) chart, format each data series vertically, and leave cells blank where appropriate.

Figure 7.24: Split bar chart: Explore the full-screen interactive version. Data from Starbucks and McDonalds.

Create your own version using our Split Bar Chart in Google Sheets template with Starbucks and McDonalds data. Organize each data series vertically so that it becomes its own color in the chart. Leave cells blank when no direct comparisons are appropriate. The remainder of the steps are similar to the grouped column chart tutorial above.

Stacked Bar and Column Charts

Stacked column (or bar) charts are best to compare subcategories, or parts of a whole. For example, if you wish to compare the percentage of overweight residents across nations, format each weight-level data series in vertical columns in your Google Sheet, as shown in Figure 7.25. Now you can easily create a stacked column (or bar) chart that displays comparisons of weight-level subcategories across nations, as shown in Figure 7.26. Often it’s better to use a stacked chart instead of multiple pie charts, because people can see differences more precisely in rectangular stacks than in circular pie slices.

To create a stacked column (or bar) chart, format each data series vertically in Google Sheets.

Figure 7.25: To create a stacked column (or bar) chart, format each data series vertically in Google Sheets.

Figure 7.26: Stacked column chart: Explore the interactive version. Data from WHO and CDC.

Create your own version using our Stacked Column Chart in Google Sheets template with international weight-level data. Organize each data series vertically so that it becomes its own color in the chart. In the Chart Editor window, choose Chart Type > Stacked column chart (or choose Stacked bar chart if you have long data labels). The rest of the steps are similar to the ones above.

To change the color of a data 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. Then choose the appropriate series from the dropdown menu, and set its color in the dropdown menu, as shown in Figure 7.27.

To edit a column color, select Edit Chart - Customize - Series.

Figure 7.27: To edit a column color, select Edit Chart - Customize - Series.