Today we will learn how to make a graph on google sheets…
The spreadsheet is one of the essential tools for managing and organizing data, especially large data sets. Although, these large data sets sometimes can get quite overwhelming to comprehend and process. Fortunately, a very useful function in Google sheets helps you insert a graph or chart (the terminology used in Google sheets) very easily, which can help you to represent data visually, making it easier to understand.
Before we dive into the step-by-step guide on creating these charts in Google sheets, it is important to understand the different types of graphs and charts used in spreadsheets, which vary according to the type of data you are working on. Therefore it is critical to know which chart to use when.
While Google sheet already comprises a feature that automatically suggests us a default chart according to our data type, it need not necessarily be accurate every time.
Below we’ll see the most commonly used graphs and when to use which graph
Types of Graphs:
1) Line chart:
The line graph, also known as a line chart, is generally used to compare trendlines between a category over time. Line charts are suitable for a continuous data type. For example, the line chart is appropriate to showcase the life expectancy rate of a country or countries over a period of time, say, from 1960 to 2018.
2) Bar graph:
Bar graphs are used for the categorical types of data, where we would want to understand how the distribution of different categories wagered, which would help us understand which group ranked higher. For example, to understand if the survey participants were dominantly male or female, we would use a bar graph.
3) Pie chart:
A pie chart, like a Bar graph, is appropriate for nominal and categorical data types. The pie charts are suitable for showing the composition of different parts. For example, in a cake recipe, we can use a pie chart to display how much of an ingredient (in %) was added to make the cake.
4) Scatter plot:
The scatter plot chart is used to draw out the relationship between an independent variable (which will be depicted on the x-axis/vertical axis) and a dependent variable (which will be depicted on the y-axis/horizontal axis) on a Cartesian plane, and look for any correlation between them. For example, drawing out the relationship between height and age of students or height and weight.
There are many other graphs and charts in addition to the ones mentioned above, such as a Histogram- similar to the bar charts but used to represent continuous data wherein each bar is stacked closer to each other, geo chart, population pyramid, radar chart, etc.
We have seen what type of graph is used for which type of data; now, let us look at how to create a graph in Google Sheets.
How to insert a chart in Google sheets:
- Open a Google sheet from your browser by heading to the Google Apps section from the Home tab. Alternatively, you can type in “sheet. new” in the URL bar, and it will open a new spreadsheet for you immediately.
- Select the cells of the data you would like to represent.
- From the top toolbar above your spreadsheet, click the Insert tab.
- You will see the Chart option, click on it, and you will notice that Google Sheets has automatically suggested a default chart based on your data type.
If it’s not what you are looking for, you will notice a side panel on the right-hand side of your screen titled Chart editor. If the Chart editor option is not visible, double-click on the chart and the Chart editor should appear.
You can change and edit your graph in the Chart editor according to your preferences.
In the chart editor, you will find two tabs, i.e., the Setup tab and Customize tab. Using these two tabs, you can choose a different chart type, customize your chart’s appearance, edit the chart title and axis titles, change the date range, etc., to make a chart easier to read or understand. Let us have a bit more in-depth look at each tab’s functions.
- In this tab, you can change the chart you would like to choose for your data set. You will see a list of suggested ones at the top by Google sheet in the drop-down menu of Chart type, followed by a Line chart, Area chart, Column chart, Bar chart, Pie chart, Scatter chart, Map, and Other chart types in the same drop-down menu.
- You can also find the data range section, wherein you will find the data range of the selected cells used for the graph; if you want to change it, click on it and select Add another range to add another data range manually, or you can choose from the ones already being suggested by Google sheets from the existing data set.
- Next, you will see the Label option, which tells us which column from the data range is used in the chart. Since here I am using a Pie chart for this demonstration, we can see only the label, i.e., Age because we can only add one column in a Pie chart.
- The Value option lets you add a range of values to your chart from the data range. But Pie chart does not require use for this option.
- Below you will notice checkboxes for Switch Rows/Columns, which transposes your data from the current axis to the other one and vice-versa. The Use row 2 as header checkbox is very useful for separating the header when you select all the cells of a data range.
- Under the customize tab, you will notice the Chart style drop-down menu, where you can change the chart’s layout, such as the chart’s background colour, the chart, the font of the chart, and even the chart border colour. You will also see the checkboxes to Maximize it, and if you want to make it appear in 3-D
- We’ll see the Pie chart and Pie slice drop-down menu, which are exclusive to the Pie chart. Here, you can customize the pie slices depending on how you want the slice labels to appear, where you want the data points to be placed, the colour of the slices, etc.
- Then we have the Chart and axis titles drop-down menu which lets us change the title text and create axis titles. Since there is no horizontal axis and vertical axis in the Pie chart, the option isn’t visible.
- Adding a legend to your chart can make the data labels easily identifiable.
Note: The options in these tabs may vary slightly according to the chart you use.
After you are finished with customizing the chart, click on the chart, and three dots should appear. You will find the option to edit, delete, and download the chart. The spreadsheet you are working on will automatically be stored in your Google drive. Another advantage of using Google sheets is that you can access your spreadsheet anytime by logging into your Google drive.
Now that we have looked at how to create a chart/graph in google sheets let us briefly go through some examples of some commonly used charts in google sheets.
How to create a column chart in google sheets
Column charts are not much different from bar charts. While bar charts are displayed horizontally, column charts are displayed vertically, and both are used for similar data types.
For this demonstration, I use a Mall Customer data set, wherein I am given CustomerID, Gender, Age, Annual Income, and Spending score (1-100). I have already reorganized the data into a pivot table, making it easier to represent it meaningfully visually.
- Select the data from the pivot table
- Click insert, select chart, and the Google sheets automatically give me a column chart.
- Edit the horizontal axis title and the vertical axis title accordingly, and give a chart title too.
- I can also remove the gridlines to make them look cleaner, but I’d like to keep them for this graph.
- I have also added the data point on top of each column, making it easier to understand their value.
- From the graph, we can see that even though the Male section has a higher annual income, the Female section’s spending score seems to be higher.
How to create a scatter chart in google sheets
I will use the same data set for this chart also.
- Since a scatter chart is used to find a correlation between two variables, I will take data from only two columns- Age and Annual income and draw the relationship between them.
- Two select more than one column from a data range, select the first column and then hold the Ctrl key(For Mac, hold the command key) while selecting the second column.
- After the data range has been selected, repeat the same process we previously discussed, and google sheets automatically creates a scatter plot.
- From this graph, we can now understand that the correlation between Age and annual income is negative.
How to create a stacked column chart in google sheets
Stacked column charts can come in handy when you have multiple series of data of various categories. I am using data from a survey done by a soft drink company wherein they asked the participants who drink they preferred more- theirs or the competitors. The data included Gender, Age range, and preference.
- I have again made use of the pivot table to use it for analysis.
- I want to know the age-wise breakdown of females who preferred “Our brand” vs “Competing brand.”
- After selecting the data range from the table, I will select the stacked column chart.
- Looks like most Females between 40 and 60 and teenagers prefer “Our brand.”
How to create a line chart in google sheets
I have data wherein the number of visitors from 2014 through 2017 is recorded for various museums. We will use the line chart to find which museum recorded the highest number of visitors throughout the years.
- Select the data range and repeat the process; google sheets automatically insert the line chart.
- After making the necessary edit and customizing the legends, the chart is done.
- You will see that initially, there is a spike for the Firehouse museum, but over the years, Avila Adobe has consistently received more visitors than other museums.
Note: The charts are dynamic; therefore, when entering new data or altering the existing chart data, the change automatically gets updated in the chart.
Data-driven decisions have never been easier and more meaningful to construct with the help of charts and graphs. Using google sheets, you not only get charts suggested automatically based on your data type, but you can also change the chart style and customize it to make it more personalized and clean according to one’s preference.
We have only covered the most commonly used graphs and charts in this article and how to create them. However, this was only the tip of the iceberg; Google sheets offer more complex and interactive graphs and charts for various data types. Explore and do let us know your favourite ones.