.png)
Selecting the Correct Chart Type in Google Sheets [Ultimate Guide]
Learn how to choose between line charts, area charts, column and bar charts, pie charts, scatter charts, and more on Google Sheets. Great for data visualization and analysis.
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.
Click here to learn how to make line charts in Google Sheets.
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.
Click here to learn how to create area charts in Google Sheets.
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.
Click here to learn how to make column charts in Google Sheets.
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.
Click here to learn how to make bar charts in Google Sheets.
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.
Click here to learn how to create pie charts in Google Sheets.
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.
Click here to learn how to create scatter plots in Google Sheets.
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.
Click here to learn how to create geo charts in Google Sheets.
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.
Click here to learn how to create waterfall charts in Google Sheets.
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.
Click here to learn how to make histogram charts in Google Sheets.
Radar charts
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.
Click here to learn how to make radar charts in Google Sheets.
Gauge charts
Gauge charts are a type of chart that visualizes a set of data and how it compares against a range of possible values.
Click here to learn how to make gauge charts in Google Sheets.
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.
Click here to learn how to make scorecard charts 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.
Click here to learn how to make candlestick charts in Google Sheets.
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.
Click here to learn how to make organizational charts in Google Sheets.
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.
Click here to learn how to create tree map charts in Google Sheets.
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.
Click here to learn how to make timeline charts in Google Sheets.
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.
Click here to learn how to make table charts in Google Sheets.
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.
Click here to learn how to make funnel charts in Google Sheets.
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.
Click here to learn how to make Gantt charts in Google Sheets.
Conclusion
Google Sheets offers a wide range of chart types that can bring diversity to your visualization and enhance your meaningful analysis of your data. Additionally, the five criteria set in this pillar page will help you make informed choices in visualizing your data. If you are ready to learn the basics of charts in Google Sheets, head over to our Ultimate Starter Guide to Charts In Google Sheets!
References
CHART | definition in the Cambridge English Dictionary
GRAPH | definition in the Cambridge English Dictionary
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
====
