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, line charts, and pie 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 appear again.
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 in the data range and put them in the chart.
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 the 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. Google Sheets makes suggestions depending on the type of data present. You can as well choose a specific chart type. We have a pillar article that you can check out here.
For our example, let’s change the chart type to a stacked column chart. Go to the Chart type as we have just outlined above, and then look for a Stacked column chart.
Voila! We have now changed the chart type.
Since Google Sheets automatically generates 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 more accurate labels:
Step 1. Click the Customize tab in the Chart editor.
The Customize tab in the Chart editor lists options for customizing your chart.
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 in 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 textbox 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 the range 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 the Setup tab. The Data range is located below the Chart type and Stacking options.
Step 3. To change the data range, use one of the following methods:
Method A: Click the textbox of the Data range option and type the new data range
Method B: Click the box symbol on the right end of the textbox. 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 promptly update.
For our example, we have reduced the data from several branches down to a single branch.
Our chart currently looks like this:
Our chart almost looks perfect! However, the Total series was included in the chart, when it should not. What should we do?
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 the 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. In our example, 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. For our example, we will remove the Johnstown district series.
Our chart now looks like this:
This is not the only way to accomplish this task. 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!
In the same sidebar, you can add back data series that were 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 to our chart, 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 of the visualized data. For our example, let us change the range of the vertical axis of our chart. To do so, follow these 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 textboxes labeled Min and Max, respectively.
Step 2. Type on the boxes the values for minimum and maximum value.
It’s possible that a data 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 to the range of horizontal axes, but this change is most applicable to 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.
By default, the axis scale used in Google Sheets is the linear scale. This means that the distance between each gridline is the same.
There are other scales that can be used. To change the scale, go to the Chart editor sidebar, then Customize tab, then Vertical axis options. Scroll down a bit to find the Scale factor.
When you click the drop-down box, the following options will appear.
The options serve as factors to the values present. For our example above, if we choose 1,000,000, then the original labels were divided by that factor, and you will see 1.00, 2.00, and so on. These options are useful when dealing with very large and very small values, but it needs to have a corresponding change in the vertical axis label. Note that these options are still all in the linear scale.
Another option is to change the linear scale to a log scale. The log scale helps highlight certain types of changes in the values, such as values that increase exponentially. To use the log scale, simply tick the Log scale checkbox underneath the drop-down box of the Scale factor options.
The chart will look different; notice the change in the values labeled in the vertical axis.
If you don’t want the scale or size of the charts created by Google Sheets, you can also resize the chart for better visualization. Here are the steps:
Step 1. Double click on the chart you want to resize.
Step 2. Drag the blue markers to resize the chart.
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. 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, and the chart will be downloaded it that format.
We also have standalone tutorials that can help you further improve your charts in Google Sheets. They are listed below:
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!
