The Ultimate 2021 Starter Guide to Charts In Google Sheets

Making charts is a key way to visualize information!

Luckily, Google Sheets provides chart-making capabilities comparable to that of existing heavyweights such as Microsoft Excel. There are several types of charts that we can use in Google Sheets, such as bar charts, pie charts, and line charts. This ultimate starter guide will help you grasp the many capabilities of Google Sheets charts!

How to add a chart

It is fairly easy to add a chart in the same sheet as the source data. 

Step 1. Select the range of cells containing the data to be visualized.

Step 2. Click Insert in the main toolbar, then click Chart in the drop-down menu.

Step 3. A column chart is created, with the title, legend, and axes labels automatically generated.

‍

Note that the Chart editor sidebar is automatically loaded. This sidebar will appear as long as you select a chart in the spreadsheet. 

If you have closed the Chart editor, and need it back, simply double click on any element of the chart, and it will return. 

As you have also noticed, the chart automatically detected multiple series of data from the selected data range. Google Sheets can automatically determine the multiple series of data present on the data range and put them in the chart!

The types of charts available in Google Sheets

Google Sheets has a wide variety of chart types that you can use in designing your own charts. However, not all chart types fit with the data that you have. This begs the crucial question:

What chart type should you use?

Here is a quick guide to some of the chart types offered by Google Sheets:

Line charts

Line charts consist of dots, representing data points, connected by dots. The x-axis is defined as the time period while the y-axis is a metric being visualized. Line charts are used in visualizing changes in the value of a metric over time. 

Area charts

Area charts are similar to line charts, where the x-axis is defined as the time period while the y-axis is the metric being visualized, but the area under the line is shaded a certain color.

Often, more than one series of the same metric is visualized as a stack of different colors of different areas under the curve. They are called stacked area charts. Stacked area charts are used to visualize the changes in the contribution of various sources to a certain quantity or metric over time.

Column & bar charts

Column charts and bar charts consist of rectangles representing the values of different items or even the value of a metric over time. Column charts and bar charts are best used for comparison of the values of a certain metric over different items. ‍

Stacked column & bar charts

Stacked column charts and stacked bar charts work similarly to stacked area charts, where they also visualize the contribution of various sources to a certain value. They are best used to compare the composition of various items.‍

Pie charts

Pie charts consist of a circle, a disk, or sometimes a donut, divided into portions representing different sources or different components of something. Pie charts are best used to represent the composition of a single item. ‍

Scatter charts

Scatter charts consist of points representing different data points in an x-y plane. The x-axis and y-axis represent different variables or metrics. Often, these data points align to form a pattern that relates the two different variables or metrics represented by the axes. Scatter charts are therefore used to visualize the relationship between two metrics.

‍Geo charts

Geo charts consist of a world map (or any other map, but Google Sheets currently offers a world map) with the countries’ colors representing a metric or values or even items. Geo charts can be used to do the following: 

1. Compare the values of a metric of each country; in which case, the colors are of different shades of the same major color;
2. See which countries have the same features; in which case, the colors are of different colors to indicate the differences.
   

Using this quick guide, you can make a good judgment on the type of chart to use to best visualize your data!

How to select the chart type

By default, column charts are generated. It is fairly easy to change the chart type, as long as the proper range of values are selected. To do so, 

Step 1. Go to the Chart editor sidebar. 

Step 2. Select Setup tab, then look for the Chart type setting. This should be the first listed item in the sidebar. 

Step 3. Select the drop-down box. 

Google Sheets offer a wide range of chart types, such as line charts, column charts, pie charts, bar charts, scatter plots, and others. However, Google Sheets also offer suggested chart types that fit with the data that you are trying to visualize, and that is shown first. 

For our example, let us change the chart type to a stacked column chart. Let us go to the Chart type as we have just outlined above, and then look for Stacked column chart. 

‍

Voila! We have now changed the chart type.

How to change the title and axes labels

Since Google Sheets automatically generated the title and axes labels, they often do not correspond to what we want them to look like. Here are the steps to change them to something more fitting:

Step 1. Click the Customize tab in the Chart editor.

The Customize tab in the Chart editor lists down options for customizing our charts. 

Step 2. Click the Chart & axis titles. The drop-down box allows you to choose which label to change. It can be the chart title, chart subtitle, or the axes labels.

‍

Step 3: We want to change the chart title to Annual sales by branch. Select Chart title in the drop-down box, and then type Annual sales by branch to the text box below.

Step 4. We can change the title font, font size, title alignment, and the title color in the same tab. Click on the options to change them. 

Step 5. Do the same steps for horizontal and vertical axes labels. Our chart now looks like the following:

Step 6. The default font color, size and alignment are all sufficient, but you can change them in the same area. Below the text box for Title text are the settings for Title font, Title font size, Title format (which includes options for boldface, italicized, and realigning the title to center or right-align), and Title text color. 

‍

Similar settings are also available for x-axis and y-axis labels. 

How to change the data range of the chart

If we want to change the data range, either due to a mistake in selecting them or due to changes in the requirement, we can still do so without starting again from scratch.

Step 1. Take note of the new data range. You can do so by consulting the source sheet and taking note of the range of cells to remain as part of your chart. 

Step 2. On the Chart editor, select Setup tab. The Data range is somewhere below the Chart type and Stacking options.

Step 3. To change the data range, do one of the following ways:

1. Click the text box of the Data range option and type the new data range
2. Click the box symbol on the right end of the text box. A pop-up box will appear.
   

When this pop-up is displayed, you can select the data range using your mouse, and the displayed range is updated with the range of the array you selected. Once the desired data range has been selected, click OK. The chart will be promptly updated.

For our example, we have reduced the data from several branches down to a single branch.

How to change the chart data series without changing the data range

Our chart currently looks like this:

How to remove a series from the chart

We want to remove the Total series and the Johnstown District series without modifying the data range covered by the chart. To do so...

Step 1. Let us go to the Chart editor, then select Setup tab. Scroll down the tab. A part labeled Series appears. This is where all the data series displayed in the chart are listed. 

Step 2. Click on the three dots on the series that you want modified. For our case, we want to remove the Total series. A small drop-down box will appear, where you can either remove the series or add labels. Click Remove.

Step 3. Do the same thing to other series you want removed. 

Our chart now looks like this:

This is not the only way to complete this action. If you are creating a metrics dashboard, for example, you would want this functionality inside the sheet instead of double-clicking on the chart to tweak it. We have prepared two ways of doing so:

• How to Insert a Checkbox in Google Sheets 
• How to Filter Charts Using the Slicer on Google Sheets [5 Simple Steps]
  

Feel free to implement one of these solutions for your chart as well!

How to add back a series previously removed from the chart

In the same sidebar, you can add back series previously removed from the chart. 

Step 1. On the Series portion of the Setup tab of the Chart editor, click Add series. A list of series that can be inserted will appear.

‍

Step 2. Click the name of the series that we want to add back to the chart. For our example, we want to add the Johnstown District series back, so we will click it. 
Our chart looks like this now:

How to change the range of vertical and horizontal axis

Sometimes the range of values displayed by the chart has to be modified in order to better reflect the trends in the data being visualized. For our example, let us change the range of the vertical axis of our chart. To do so, follow the steps:

Step 1. On the Chart editor, click the Customize tab, then click Vertical axis. 

The Vertical axis tab contains several options that include the style of the label. Near the end of its list of options are two text boxes labeled Min and Max, respectively. 

Step 2. Type on the boxes the values for minimum and maximum value. 

‍

It’s possible a series will exceed the maximum limit of the vertical axis. By default, the Allow bounds to hide data is ticked to account for a series or two that will exceed the maximum limit. 

Here is our chart now:

The same steps can be done for changing the range of horizontal axes, but this is most applicable for line graphs, which show the progression of metrics over time. 

How to change the series colors

If we don’t want to default colors used by Google Sheets, we can change the series colors. Our original chart looks like this:

Let’s say we want to change the color of the Johnstown District series. To do so, you can do either of the following:

1. Double-click on a green box representing the data from Johnstown District series
2. Go to Chart editor, then click Customize, then click Series, then click the drop-down box originally displayed as Apply to all series. A drop-down box will appear. Select Johnstown District. 

Either way, you will now be able to change the settings for the Johnstown District series. Below the Format label is the Color setting. Click on the small box with the current series color displayed. 

‍

A palette will appear. You can select the new color from the given set of colors.

Or you can set your own custom color by clicking the plus button below the Custom label. A more sophisticated palette will appear, that will also allow you to enter the code of the color you prefer.

We used the more sophisticated color palette to specify a darker shade of green. Once you have selected a color, click OK. The color for the Johnstown District is now darker.

‍

How to switch rows and columns without using transpose()

There is a preferred layout for the source array so that the data is properly visualized into a chart. Fortunately, Google Sheets has a quick option to switch rows and columns without using the transpose() function.

On the Chart editor, go to the Setup tab, then scroll down to its end. You will see the option Switch rows / columns. Depending on the layout of your data, it is either ticked or unticked. If it is ticked, untick it; if it is unticked, tick it. 

How to download the chart as a separate file

You can save the chart as a separate file to your computer, either as an image or as a pdf file. Here are the steps:

Step 1. Hover the cursor over the chart. Three dots should appear in the upper-right corner. 

Step 2. Click on the three dots. A drop-down menu will appear. Hover to the Download, and another popup-menu will appear. You can download the chart as a PNG file, a PDF file, or an SVG file. Click on the file type of your choice. The chart will be downloaded at that format. 

14 useful tips for creating charts

Now that we are done learning the basics of creating charts, let us now have some tips for creating charts. Here’s some advice from Hands-On Data Visualization: 

1. A good title is short, clear, and tells a story on its own.
2. Make your subtitle less prominent than your title by decreasing its font size, or changing its font style or color, or both.
3. Bar and Column Charts Must Begin at Zero
4. The zero-baseline rule does not apply to line charts.
5. Pie charts represent 100% of the quantity, but avoid pie charts as much as possible.
6. Avoid visual elements that do not carry information
7. For pie charts, arrange slices from largest to smallest, clockwise, and put the largest slice at 12 o’clock.
8. When your column chart has long x-axis labels that have to be rotated (often 90 degrees) to fit, consider turning the chart 90 degrees so that it becomes a horizontal bar chart.
9. Arrange elements logically or alphabetically
10. Do not overload your scales.
11. Keep your typography simple, and use bold type to highlight major insights
12. In most cases, monochromatic (single-hue) charts suffice
13. Consider the rule of complementary colors—opposites in the color wheel—to find color pairs.
14. Stay away from pure saturated colors and instead choose their “earthier” versions, such as olive green instead of bright green, or navy instead of neon blue.
    

Summary, plus a sample sheet to work on

That’s all for our ultimate starter guide to making charts in Google Sheets! I hope you managed to make your own chart in Google Sheets! 

If you are a bit lost, here is a link to the sample sheet.

We will add more tutorials here on the more advanced features of Google Sheets. Stay tuned!