Google Sheets Bar charts with multiple groups

Google Sheets Bar charts with multiple groups

Google Sheets bar charts

Bar graphs are great when working with multiple groups of data. They are helpful when looking for patterns. Groups of data provide opportunities to look at data from different perspectives.

The data for this chart is shared with you here. Click this link to get a copy and follow along. The second tab in the sample worksheet includes data from multiple campuses.

Sheet with multiple groups of data

Select the headings and data then click the Insert chart button.

Selected data for chart

This bar chart includes more information. This chart includes a legend. The legend in this chart runs across the top. The data in the chart is grouped by campus. The bars for the data appear in the order that came from the table.

Default bar chart

Google tried to help format the titles but they need some work. The horizontal title is missing and we need to change the title from Grades to something else. Change the title from Grades to Campus. Go to the Chart editor panel and change the font size to 16 points.

bar chart horizontal title

Click on the axis and titles selector. Choose the Vertical Axis title.

Vertical axis title

The vertical axis title is empty. Click once in the title field and type Teachers.

Vertical axis title field

Change the font size to 16 points.

16 point font size

Change the title to “Teachers by Campus” and change the font size to 16 points. Change the text alignment to center align.

Updated chart title

Showing the values on each bar would be helpful. Go to the Series section.

Series section

Scroll down a little and place a checkmark in the Data labels option.

Data labels option

The data labels work well with all the values except Preschool. Only one campus has preschool teachers. It throws off the values for the other campuses.

Data labels on bars

We can format each data series. Click on the series selector.

Formatting all series selector

Select the Preschool series.

Select the preschool series

Go to the text color option. Change the value from Auto to white.

Text color

Changing the font color to white forces the color to change across all campuses. The value of zero is still there but we can’t see it because it matches the background color.

Values with zero are not visible

Data can be viewed from different perspectives. The data in our current graph is displaying values for each campus. We can also modify the view so we are looking at the values for each grade level. Switch to the Setup section in the Chart editor.

Chart editor setup section

Scroll down and remove the checkmark from switch rows or columns.

Switch rows and columns of data

The values are now grouped by grade levels. Switching the data grouping changed the formatting. Switching between data groupings causes this issue. The better option is to create two separate charts of the same data. Place a checkmark back on the switch rows or columns box.

view of switched data values

Click once on the chart and click the actions menu. Select Copy chart. The chart is placed in the computer’s memory. Click Edit in the menu and select Paste.

copy chart option

The copy is pasted above the original. Click once on the pasted copy and go to the setup section. Change the switch row or columns box.

copy of original chart

This takes us back to the version that needs formatting.

version in need of formatting

There are some bars without values. Let’s take care of them first. Go to the series section in the customize panel.

series section

Place a checkmark in the Data labels box.

Data labels box

We need to fix the values in Preschool. We can fix this in one of two ways. Let’s take a look at the easiest way first.

fix values in preschool

Click the Text color selector and choose white.

white text color

This works well when the bars are bright colors and the background is white. There is another option that allows us to target our customization. Place the text color back to Auto.

text color is set back to auto

Find the Format data point option and click the Add button.

Format data point

A data point selector dialogue opens.

data point selector

Select a campus and the preschool that has a zero value. Click the OK button. Select white from the data color option.

select data point value

Click the Add button again. Choose the next campus that has zero for the Preschool value. Change the data point color to white.

This option includes several steps but it does offer the flexibility to provide greater customization of text colors. Change the title of the slide to Teachers by Grade Level.