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

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 in Google Sheets. Worry not, as this guide will explain each type and when to use it!

Definition of a chart

But first, what is a chart? The words chart and graph are often interchanged. Are they different? Are they the same? Let us consult the Cambridge English Dictionary Online:

Chart: a drawing that shows information in a simple way, often using lines and curves to show amounts (CHART | definition in the Cambridge English Dictionary

Graph: a picture that shows how two sets of [numerical] information or variables are related, usually by lines or curves (GRAPH | definition in the Cambridge English Dictionary

As you can see, chart is a broader term that encompasses all graphical methods of describing information. Graphs, however, are more specific. Graphs are designed to describe how the values of two numerical variables vary together, often shown on two-dimensional areas with a x and y axis. Therefore, all graphs are charts, but not all charts are graphs. For our tutorials, we will use the word chart

To further emphasize the point, one of the most famous charts combines numerical data with geographical and historical data to effectively visualize an important historical event:

This chart created by Charles Joseph Minard in 1869 visualizes the annihilation of Napoleon’s army during its Russian campaign in 1812. The reader can easily see how, starting from half a million troops, the army was slowly destroyed by a combination of disease, bad weather, and attrition and harassment from the Russian army. Minard was well-known for pioneering what are now called Sankey diagrams, useful for visualizing the flow of resources through a system.

Fortunately, we do not always need to invent a new type of chart to visualize our information. We can choose from a selection of chart designs that fit our data the best.

What type of chart should you choose?

Choosing the correct chart type better illuminates the patterns in metrics and variables and makes analysis easier. Google Sheets offers a diverse range of charts to choose from. The main consideration in choosing a chart type is your purpose. Below are a few questions to help you determine the correct chart to fit your purpose.

Do you want to analyze trends in your data set over time?

The simplest thing that charts do is show how a variable changes over time. For example, you can see how your annual sales improve year after year. These variables are called discrete variables, as you get one value per time period set, and you can represent them using a bar or column chart

Other values change continuously over time, such as the total number of a product sold over the years. For those cases, line charts and area charts work best as they illustrate the trend over time. Scatter charts also work by setting the x-axis to mark the time period, and these are preferred in technical settings.

If you want to visualize changes to a value over time, especially due to various factors, waterfall charts are the best as they highlight the changes instead of the whole values.

Do you want to compare a metric or two over different datasets?

Insights can also be discovered when you compare the same variable describing different things. For example, you can compare the annual sales of each branch and get insights into their performance.

Most chart types can be used for comparison. Obvious choices for comparing values are column and bar charts. Line charts and area charts can also be used if you want to compare the same variable describing different things as they change over a time period. Radar charts can be used for comparing the values of related variables describing the same thing. Geo charts work well if you are comparing geospatial data. Finally, combo charts help you compare the value of two different but possibly related variables to get more insights.

Do you want to visualize the composition of a dataset?

Certain types of charts show composition visually as parts of a whole. The relative sizes of these visual parts of a whole can easily demonstrate parts of a whole, which can be helpful in  illustrating expenses and revenues of a business. Pie charts are ideal when dealing with 5 components or less. If there is a hierarchy on the composition or that there are more than 10 components, tree map charts are the better choice.

You can also show how composition changes over time. If you have discrete values such as total annual sales, you can use 100% stacked column and bar charts. If you have continuous values that can be illustrated using line graphs, use 100% stacked area charts

Waterfall charts are useful for showing  the composition of the change in the value due to different factors.

Do you want to examine the distribution of your data?

There are certain cases where a variable describes a large amount of items/persons but the distribution of possible values is not random. One good example of that is the age of your customers. The values of their ages will probably cluster around certain values, depending on whether your main customers are teenagers, young adults, or retirees.

The best chart type to analyze a distribution of values is the histogram. It is a specific type of column chart that is designed for visualizing distribution of values. The possible values are often divided into segments of equal size. In the case of the distribution of ages, one can opt to divide the values by 5: from 10 to 15 yrs old, 16 to 20 yrs old, 21 to 25 yrs old, and so on.

If the distribution is not among the possible range of values, you can still use bar and column charts

Do you want to uncover a potential relationship between two variables?

When you are presenting information about two numerical variables that are or may be related to each other, you present them together using charts that will highlight any relationship between them. A correlation describes the existence of a relationship between two or more numerical variables. Correlation is an important property that can hint at the possibility that a certain numerical variable influences another variable, but take note that it is a common mistake to think that correlation between two variables imply that one of them influences the other one

The best chart type to use in this instance is the scatter plot. In fact, drawing from my experience in college studying technical fields, that’s the only one I recommend as scatter plots adequately visualize possible relationships between two variables without adding unnecessary information that may lead to misinterpretation. Line charts cannot be used, as the line or curve of a line chart can be misinterpreted as the variable in the y-axis changing over time on initial look.

Types of charts in Google Sheets

Let's now explore the major types of charts in 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 to visualize 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. Simple area charts are essentially line charts with shaded areas underneath the lines.

Column 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 between different groups. They only differ in the orientation of the boxes: column charts have vertical boxes while bar charts have horizontal boxes. ‍

Bar charts

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

Pie charts

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

Scatter charts and bubble charts

Scatter charts and bubble charts consist of points representing different data points in an x-y plane. A bubble chart works similar to a scatter chart, but it visualizes another set of data as the size of the “point,” or the bubble. Thus, bubble charts visualize three sets of related data.

Geospatial charts

Geospatial charts are a hybrid of charts and maps. If you are dealing with location-based data, geospatial charts highlight potential geographic relationships in your data.

Waterfall charts

The waterfall chart is used to visualize the cumulative effects of various sources or factors on a specific metric or variable. These are not necessarily plotted chronologically and can also be plotted by category.

Histograms

The histogram chart, more commonly known as the histogram, 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 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.

‍

Gauge charts are a type of chart that visualizes a set of data and how it compares against a range of possible values.

Scorecard charts

A scorecard chart is specifically designed to highlight a specific KPI or metric alongside a change in its value with respect to a specified baseline value. Scorecard charts are specifically designed for use with dashboards made in Google Sheets.

Candlestick charts

Candlestick charts are prominently used in financial markets to visualize the movement of prices of financial products such as securities, derivatives, and currencies. For a given time period, it visualizes the maximum price and the minimum price as the ends of the thin lines, while the range between the opening and closing price is marked by a solid rectangle. Gains and losses are indicated with green and red shades, respectively.

Organizational chart

An organizational chart is a type of chart that visualizes the relationship between different items. This is different from other types of charts that we have, as organizational charts visualize qualitative information instead of quantitative information.

Treemap charts

Treemap charts are essentially improved pie charts. Treemap can visualize two tiers of information about the composition of a whole: the specific items and their groupings. Treemaps are ideal for cases where there are several items, at least by tens, but that can be grouped by their type.

Timeline charts

Timeline charts are a special form of line charts in Google Sheets that are designed to plot data marked with dates. This is a specific chart because Google Sheets stores dates in their own format; a timeline chart can take care of this aspect in plotting the values.

Table charts

Table charts are designed to highlight certain values by placing them in a chart that has a different format compared to the rest of the sheet. Additionally, it keeps the data from being accidentally modified by a stray click.

Other types of charts that can be done in Google Sheets

With a little creativity, you can create other types of charts in Google Sheets. Some of these types are listed here: the funnel chart and the Gantt chart.

Funnel charts

A funnel chart is used to show the progress of connected stages in a specific process. The chart is like a funnel or inverted pyramid, which starts in a broad head and ends in a narrow neck. It is commonly used in the business or sales process where the starting number of users or customers are tracked until it goes down on every stage.

Gantt chart

Gantt charts are used to visualize project schedules by allotting a bar for each stage of the project. The length of each bar depends on the length of time allotted for each step; thus showing which stages are expected to take long and which ones are expected to be easily completed.

References

CHART | definition in the Cambridge English Dictionary

GRAPH | definition in the Cambridge English Dictionary

What's the difference between a graph, a chart, and a plot? - English Language & Usage Stack Exchange

Data Visualization 101: How to Choose the Right Chart or Graph for Your Data

3 responses to “The Do's and Don'ts of Chart Making”

Data Visualization 101: How to Choose a Chart Type

How to Choose the Best Types of Charts For Your Data

Library Guides: Data Visualization: Choosing a Chart Type

Best Business Charts: The 6 Most Devastatingly Effective Types of Business Charts & How To Use Them

44 Types of Graphs & Charts [& How to Choose the Best One]

A Complete Guide to Funnel Charts | Tutorial by Chartio

Gauge Charts

====