Spreadsheets

|

January 8, 2021

Selecting the Correct Chart Type in Google Sheets [Ultimate 2021 Guide]

Google Sheets logo with charts in the background
SECTIONS
  1. Basic uses of charts
  2. Line charts
  3. Area charts
  4. Column and bar charts
  5. Pie charts
  6. Scatter charts
  7. Other chart types
  8. Summary, plus a sample sheet to check
SECTIONS
  1. Basic uses of charts
  2. Line charts
  3. Area charts
  4. Column and bar charts
  5. Pie charts
  6. Scatter charts
  7. Other chart types
  8. Summary, plus a sample sheet to check

If you are planning to make charts for a certain purpose, such as compiling reports or assembling a real-time dashboard to track the growth and performance of your business, you may find yourself overwhelmed with the variety of chart types that you can make in Google Sheets. Worry not, as this guide aims to help you learn these types of charts and make good choices on which one to use!

Basic uses of charts

Charts can be a simple but powerful tool for visualizing patterns in metrics and variables.

The correct chart type will illuminate these patterns better and make our analysis easier, so choosing the correct chart type is important.

The basic uses of charts can be expressed in the form of questions that will also help you determine the right chart type for your data:

  1. Do you want to compare values?
  2. Do you want to show the composition of something?
  3. Do you want to understand the distribution of your data?
  4. Are you interested in analyzing trends in your data set?
  5. Do you want to better understand the relationship between value sets?

The correct type of chart also depends on the data type available. Here are the data types and the corresponding chart types that match, as outlined by Visme:

Types of Data corresponding with Types of charts
The resulting chart you can use depends on the type of data that you are going to visualize. Image Source


People have been more creative in visualizing data, so there are more chart types beyond the types listed above. 

The main chart types available in Google Sheets, which we will cover in this article, include the following:

  1. Line charts
  2. Area charts
  3. Column and bar charts
  4. Pie charts
  5. Scatter charts
  6. Waterfall charts
  7. Histogram charts
  8. Radar charts

Let's explore what each of these mean...

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 to visualize changes in the value of a metric over time. 

Google Sheets offer three types for the line charts, which are the simple line chart, the smooth line chart, and the combo chart (consists of line charts and a bar chart).

The icons showing the types of line charts. From left to right: simple line chart, smooth line chart, and combo chart.
The icons showing the types of line charts. From left to right: simple line chart, smooth line chart, and combo chart.

The only difference between the simple line chart and the smooth line chart is that for the simple line chart, the data points are connected by straight lines while for the smooth line chart, a little bit of more processing is done to make the lines smooth curves. 

How to format your data for a line chart

Each column represents a data series (aka a line in the line chart). Use the first row as the label for your series. You can set the first column for the time or date the data was recorded. If the data is not formatted this way, you can either transpose it using the TRANSPOSE() function or you can switch the rows and columns through the Chart editor. 

A line chart showing the change in the population of New England from 1790 to 2010. 
A line chart showing the change in the population of New England from 1790 to 2010. 
A smooth line chart showing the change in the population of New England from 1790 to 2010.
A smooth line chart showing the change in the population of New England from 1790 to 2010.

For the combo chart, you can have several series plotted as a line but only a single one as a bar. The uppermost in the list of series in the Setup tab in the Chart editor takes precedence as the one plotted as a column chart. 

A combo chart showing the change in the population of New England from 1790 to 2010. Individual states are represented as line charts while the total population is represented as the gray bar chart. 
A combo chart showing the change in the population of New England from 1790 to 2010. Individual states are represented as line charts while the total population is represented as the gray bar chart. 

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. Simple area charts are essentially line charts with shaded area underneath the lines. 

The area charts offered by Google Sheets can either be a smooth area chart or a stepped area chart. Smooth- and stepped-types differ on how the lines that mark the area are plotted: the lines for smooth area charts are plotted like line charts while for the stepped area charts they form an outline of columns drawn together. The smooth area charts offer a sense of continuity while the stepped area charts emphasize that the data are valid on the date they are recorded. 

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.
The icons showing the types of area charts. On the first row, from left to right: simple area chart, stacked area chart, and 100% stacked area chart. On the second row, from left to right: simple stepped area chart, stacked stepped area chart, and 100% stacked stepped area chart. 
The icons showing the types of area charts. On the first row, from left to right: simple area chart, stacked area chart, and 100% stacked area chart. On the second row, from left to right: simple stepped area chart, stacked stepped area chart, and 100% stacked stepped area chart. 

How to format your data for an area chart

Each column represents a data series, a line in the line chart. Use the first row as the label for your series. You can set the first column for the time or date the data was recorded. If the data is not formatted this way, you can either transpose it using the TRANSPOSE() function or you can switch the rows and columns through the Chart editor. 

Here are the examples of area charts:

An area chart plotting the New England population from 1790-2010.
An area chart plotting the New England population from 1790-2010.
A stacked area chart plotting the New England population from 1790-2010. The areas are stacked to allow the reader to compare the populations of the states. 
A stacked area chart plotting the New England population from 1790-2010. The areas are stacked to allow the reader to compare the populations of the states. 


A 100% stacked area chart plotting the New England population from 1790-2010. The areas are adjusted to highlight the contributions of individual states to the total population of New England from 1790 to 2010. 
A 100% stacked area chart plotting the New England population from 1790-2010. The areas are adjusted to highlight the contributions of individual states to the total population of New England from 1790 to 2010. 


A stepped area chart plotting the New England population from 1790-2010.
A stepped area chart plotting the New England population from 1790-2010.


A stepped stacked area chart plotting the New England population from 1790-2010. The areas are stacked to allow the reader to compare the populations of the states. 
A stepped stacked area chart plotting the New England population from 1790-2010. The areas are stacked to allow the reader to compare the populations of the states. 


A 100% stepped stacked area chart plotting the New England population from 1790-2010. The areas are adjusted to highlight the contributions of individual states to the total population of New England from 1790 to 2010. 
A 100% stepped stacked area chart plotting the New England population from 1790-2010. The areas are adjusted to highlight the contributions of individual states to the total population of New England from 1790 to 2010. 

Column and 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.

They only differ in the orientation of the boxes: column charts have vertical boxes while bar charts have horizontal boxes. Column and bar charts come in three forms: simple, stacked, and 100% stacked.

The icons showing the types of column and bar charts available in Google Sheets. On the first row, from left to right: simple column chart, stacked column chart, and 100% stacked column chart. On the second row, from left to right: simple bar chart, stacked bar chart, and 100% stacked bar  chart. 
The icons showing the types of column and bar charts available in Google Sheets. On the first row, from left to right: simple column chart, stacked column chart, and 100% stacked column chart. On the second row, from left to right: simple bar chart, stacked bar chart, and 100% stacked bar  chart. 

How to format your data for a column or a bar chart

The same method from the line and area chart should work, especially if you use stacked charts. If you plan to compare different items of the same category, you can format the first column as the label for these items so the second one contains the values. If the data is not formatted this way, you can either transpose it using the TRANSPOSE() function or you can switch the rows and columns through the Chart editor. 

Below are examples of the types of column charts. The corresponding bar charts would look the same, except with the x and y axises flipped (horizontal columns).

A simple column chart comparing the population of the states of New England in 1960. 
A simple column chart comparing the population of the states of New England in 1960. 


A simple column chart comparing the population of the states of New England in 1960. 1970, 1980, 1990, 2000, and 2010. 
A simple column chart comparing the population of the states of New England in 1960. 1970, 1980, 1990, 2000, and 2010. 


A stacked column chart plotting the New England population from 1790-2010. The areas are stacked to allow the reader to compare the populations of the states. 
A stacked column chart plotting the New England population from 1790-2010. The areas are stacked to allow the reader to compare the populations of the states. 


A 100% stacked column chart plotting the New England population from 1790-2010. The areas are adjusted to highlight the contributions of individual states to the total population of New England from 1790 to 2010. 
A 100% stacked column chart plotting the New England population from 1790-2010. The areas are adjusted to highlight the contributions of individual states to the total population of New England from 1790 to 2010. 

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. 

Google Sheets offer pie charts in three forms: the simple 2d pie chart, the doughnut chart, and the 3d pie chart. There is a growing consensus to avoid 3d pie charts because they can distort perception of relative contributions of different sources, so you are encouraged to use either the 2d pie chart or the doughnut chart. By default, Google Sheet adds the numerical values of these portions in terms of percentage. 

The icons showing the different pie chart types offered by Google Sheets. From left to right: pie chart, doughnut chart, and 3d pie chart.


How to format your data for a pie chart

You should format the first column as the label for these items so the second one contains the values. If the data is not formatted this way, you can either transpose it using the TRANSPOSE() function or you can switch the rows and columns through the Chart editor. 

Here are some examples of pie charts:

A simple pie chart showing the relative portions of the New England population from the states in 1960.
A simple pie chart showing the relative portions of the New England population from the states in 1960.


A doughnut chart showing the relative portions of the New England population from the states in 1960. 
A doughnut chart showing the relative portions of the New England population from the states in 1960. 


A 3d pie chart showing the relative portions of the New England population from the states in 1960.
A 3d pie chart showing the relative portions of the New England population from the states in 1960.

Scatter charts

Scatter charts consist of points representing different data points in an x-y plane. The scatter charts are used in two ways:

  1. An independent variable in the x-axis and a dependent variable in the y-axis - Here, the scatter chart would be similar in purpose to line charts. In fact, their only difference would be in aesthetics. Depending on your field, there would be preference to using either the line chart or the scatter chart. 
  2. Two variables independent of each other - These data points align to form a pattern that relates the two different variables or metrics represented by the axes.
In general, scatter charts are therefore used to visualize the correlation between two metrics.

Besides the scatter chart, Google Sheets also offers the bubble chart. The bubble chart works similar to scatter chart, except that it visualizes another set of data as the size of the “point”, or the bubble. Thus, bubble charts visualize three sets of related data. 

The icons of the scatter chart types offered by Google Sheets. From left to right: scatter chart, bubble chart.

How to format your data for a scatter chart

Each column represents a data series, a progression of points in the scatter chart. Use the first row as the label for your series. You can set the first column for the time or date the data was recorded

The bubble chart is more complicated. It asks you to identify four columns of data: x-axis, y-axis, series, and size. 


The columns you need to specify for the bubble chart.
The columns you need to specify for the bubble chart.

The addition of Series makes it possible to include more than one series of data in the bubble chart. Add a column specifying the Series of your data. 

If the data is not formatted this way, you can either transpose it using the TRANSPOSE() function or you can switch the rows and columns through the Chart editor. 

Here are some examples of scatter charts:

A scatter chart showing the population of New England from 1790 to 2010. 
A scatter chart showing the population of New England from 1790 to 2010. 


A bubble chart showing the change in the population of New England from 1790 to 2010 with the bubble size showing the population at that year.
A bubble chart showing the change in the population of New England from 1790 to 2010 with the bubble size showing the population at that year.

Other chart types

Three of the other chart types offered by Google Sheets are included in this section. These are the waterfall chart, the histogram chart, and the radar chart. 

Waterfall chart

The waterfall chart is used to visualize the cumulative effects of various sources or factors to a specific metric or variable.

These are not necessarily plotted chronologically but can also be plotted per category.

When plotting a waterfall chart, the category should be on the first column, with the values on the second column. A row for the final value is not necessary; Google Sheets automatically calculates it and visualizes it. 

A waterfall chart showing the cumulative total of New England population by state in 2010.
A waterfall chart showing the cumulative total of New England population by state in 2010.

Histogram chart

The histogram chart is used to visualize the distribution of frequency of certain values in a sample data.

Google Sheets will automatically tally the frequency of certain values and then align them accordingly in the chart.

The data to be plotted in the histogram chart may encompass several columns; make sure the form of data is consistent throughout the data range.

A histogram chart visualizing the distribution of scores. 
A histogram chart visualizing the distribution of scores. 

Radar chart

The radar chart is used to visualize the values of different variables describing the same thing.

These variables often follow the same range of values, thus making their values comparable. When the variables are properly arranged around the radar, the resulting shape of the plot further helps in analyzing the data.


A radar chart visualizing an employee’s scores in nine attributes.
A radar chart visualizing an employee’s scores in nine attributes.

Summary, plus a sample sheet to check

As a reminder, the chart type available on Google Sheets are the following:

  1. Line charts
  2. Area charts
  3. Column and bar charts
  4. Pie charts
  5. Scatter charts
  6. Waterfall charts
  7. Histogram charts
  8. Radar charts

The first four listed (line charts, area charts, column and bar charts, and pie charts) are considered the most basic types of charts, often encompassing many data visualization needs. However, every chart type has its own strengths and weaknesses, so it is possible that you use all of them for your data analytics for your business!

To help you, here is a sample sheet to help you see how these charts work.

SECTIONS
  1. Basic uses of charts
  2. Line charts
  3. Area charts
  4. Column and bar charts
  5. Pie charts
  6. Scatter charts
  7. Other chart types
  8. Summary, plus a sample sheet to check

Subscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.