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:
Do you want to compare values?
Do you want to show the composition of something?
Do you want to understand the distribution of your data?
Are you interested in analyzing trends in your data set?
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:
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:
Column and bar charts
Let's explore what each of these mean...
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 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.
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.
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.
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:
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.
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).
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.
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:
Scatter charts consist of points representing different data points in an x-y plane. The scatter charts are used in two ways:
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.
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.
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 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:
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.
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.
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.
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.
Summary, plus a sample sheet to check
As a reminder, the chart type available on Google Sheets are the following:
Column and bar 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!