Spreadsheets
|
January 2, 2021
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!
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!
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 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 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 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 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 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 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 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:
Using this quick guide, you can make a good judgment on the type of chart to use to best visualize your data!
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.
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.
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:
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.
Our chart currently looks like this:
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:
Feel free to implement one of these solutions for your chart as well!
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:
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.
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:
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.
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.
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.
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:
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!
Subscribe to get more data and analytics tips!