- 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 four different types: grouped, split, stacked, and histograms. 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 6.10. Now you can easily create a grouped column chart to displays these data series side-by-side, as shown in Figure 6.11.
To create your own interactive grouped column (or bar) chart, use our template and follow these steps.
- 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 6.12.
- 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 6.13.
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 6.14. Software developers must be hungry.
Format your data to make each column a data series (such as male and female), as shown in Figure 6.10, which means it will display as a separate color in the chart. Feel free to add more than two columns.
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 6.15.
- In the Chart Editor, change the default selection to Column chart, with Stacking none, to display Grouped Columns, as shown in Figure 6.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 6.17. Also, you can select the chart and axis titles to edit them.
- 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 6.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 6.19. In the next screen, select Embed and press the Publish button. See Chapter 9: Embed on the Web to learn what to do with the iframe code.
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/06-chart/chart-error-bar.png). This setting will then be applied to all data points from that series.
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 6.21. 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 6.22. Unlike the grouped column chart previously shown in Figure 6.11, 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.
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 6.23. Now you can easily create a stacked column (or bar) chart that displays comparisons of weight-level subcategories across nations, as shown in Figure 6.24. 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.
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 6.25.
A histogram is a specific type of bar or column chart that is best for showing the distribution of raw data, with the number of values in each bucket (or bin). To build a histogram, you need to assign each data point, whether numerical or categorical, into one of the non-overlapping buckets. For example, imagine that you wish to track the number of customers each hour in a local coffee shop. Format the raw customer data series in a vertical column in your Google Sheet, as shown in Figure 6.26. Now you can easily create a histogram column chart that displays the number of customers per hour, as shown in Figure 6.27, which resembles the “popular times” format for businesses in Google Maps. This coffee shop experiences a morning rush and an afternoon rush, but the middle of the day and late evenings are relatively quiet.
Create your own version using our Histogram Chart in Google Sheets template with coffee shop customer data. Google Sheets considers histograms to be regular column charts, so in the Chart Editor window, choose Chart Type > Column chart and follow the rest of the directions in the Bar and Column Chart tutorial above. Alternatively, you could choose to sort customers into larger bins, such as time of day (morning, afternoon, evening), as shown in the second tab of the template.
Tip: We set a custom number format to display 8 AM and other times as desired in the Hour column. In Google Sheets, select a column, then go to Format > Number > More Formats > Custom Number Formats to define your preferred format.