The Ultimate (and Step-by-Step) Guide to Graphs in Google Sheets
Learning how to make a graph in google sheets is not only a great idea for storing and organizing data, but also simplifying the data for better understanding. As a whole, google sheets stores all the data in its numerous cells, which, in itself is a great way of assembling information.
However, reading loads of data through such cells can be tedious. To address this issue, exist the google spreadsheets’ charts.
“Charts” is just another name for graphs on google documents/sheets and are a simple way to visually summarize data. The several different menus and options available allow you to make graphs in google sheets. These graphs are of different types.
Different types of graphs are better suited for different types of data involved. We will be discussing them all in detail subsequently.
Types of Graphs offered in Google Sheets
Before moving on to the step-by-step guide of how to make a graph on google sheets, we are going to introduce you to some of the graph types that are available in google sheets. These are:
- Scatter Graph
- Line Graph
- Area Graph
- Histogram, Column Graph, and Bar Graph
- Pie Graph
- Other Graphs
1. Scatter Graph:
A scatter graph or a scatter chart is the one in which dots are used to represent data points marked against numerical data variables on the two axes. This type of chart is simple and very effective.
To elaborate here, we have taken the example of the amount of rainfall received by several different cities. This graph allows us to measure and compare this amount visually. One look will tell us where these values stand exactly.
2. Line Graph:
Line graphs are great for representing trends. These are almost like scatter graphs except that these have lines connecting all the data points, thus, explaining the trend followed in between two consecutive data points. Google sheets offers three different types of line graphs.
The following example involves the amount of rainfall received by different cities over a series of months. This graph helps us in making future rainfall predictions for the rest of the year.
3. Area Graph:
Area charts are great for the representation of data that involves the rise and fall of the trend under study. An example of this would be that of time spent on different tasks. In essence, they are similar to the line graphs.
We have carried on with the same example of the amount of rainfall over several cities. The graph shows the variation of the total amount of rainfall across different cities.
4. Histograms, Column Graph and Bar Graph:
Bar charts are used when one or more categories are involved in the data under study. Offered both vertically and horizontally, about six such types for bar charts and column charts are present on google sheets as templates.
Moving forward with the same example, here months represent the several categories involved in the data of the amount of rainfall.
5. Pie Graph:
A pie chart or a pie graph is great at representing data that needs to be communicated as proportions. It is especially used whilst conveying relative sizes and ratios.
One look at the example shown below gives us a clear idea of the city receiving the most rainfall.
In between all these charts exist the multiple types of combo charts which combine different charts such as a line and a column chart, or bar graphs with some other type of graph.
An example for this would be the incorporation of a vertical bar chart, showing the exact value of rainfall received by different cities, along with a line graph, giving us an idea of the trend across these cities as well.
7. Other Graphs:
Google sheets also have a number of other, more unconventional graphs. Examples for these include geo charts, candlestick charts, timeline charts, treemap charts, etc.
Geo graphs can be used to help display features of different geographical regions such as temperature, pressure, etc. in the form of a color-coded graph.
Similar are radar charts that aid in displaying multivariate data against three or more axes. Carrying on with the same example, we have shown how this graph appears visually.
Other examples include a candlestick chart, which is a financial graph that helps study patterns of changes in prices such as currency rates; and treemaps, which are complex charts for displaying complex, hierarchal, and nested data. This type of chart option is commonly used in computing.
This wide variety of charts on google sheets makes data organization much simpler and suited according to users’ needs. Therefore, the knowledge of how to make a graph on google sheets is a useful one to acquire.
How to Make a Graph in Google Sheets
Now that you’re aware of the options offered by google sheets, you may want to make charts of your own, for your own google documents. Now, we are going to walk you through the step-by-step process of how to make a graph in google sheets.
We will follow the guidelines involved in the making of a scatter graph. Some details differ with the type of graph chosen, however, fret not, for the differences are minor, and once you understand the major settings involved, the making of any type of graph will be a piece of cake for you.
All that is required is practice and patience. By the end of this article, you will fully understand how to make a graph in google sheets.
First off, to understand how to make a graph in google sheets, you need to have your data organized.
For our example, we have made a list of a group of people with their ages and heights in centimeters.
Step 1: For your first step, you need to select cells containing the data on which your graph will be based upon.
Step 2: Go to the top menu bar and click on the “Insert” option. This will allow for a drop-down menu to appear.
Step 3: From the drop-down menu, pick out “Chart”.
Your google sheets screen should now look like this. The graph that has appeared on your screen is the default graph type that google sheets automatically chooses as it deems it to be the most suitable one for your data.
It is to be noted that this graph may not be what you had in mind and in order to edit it according to your needs, you can utilize the chart editor on the right side of your screen.
You can see a chart set up on the left, and a chart editor pop-up box with customizations and chart editing options on the right side.
Chart Editor, and its Options
You may or may not be satisfied with the automatic setup graph, and if you aren’t, that’s what the chart editing panel is there for.
The first setting option is for the “type of graph”. From here you get to choose which type of graph you exactly want. You may select any one of the types mentioned above.
The “Chart type” drop-down menu once extended, should give you a number of different options for a graph style to pick from, alongside some suggested options according to the nature of your data. These offer a variety of options for you to choose from, each having different chart elements and features.
Immediately below the “Chart type” option is the “Data Range” option. If you hadn’t selected your range before inserting the graph, or if you want to edit your data range now, the “Data range” will allow you to do just that.
The “X-Axis”, as depicted by its name, lets you pick one of your series for the x-axis leaving the other for the y-axis. In our example, we can either choose the “Age” column as our x-axis or the “height” column, whatever our requirement may be.
Just below it, is the aggregate option. This one is a bit confusing if your data does not require aggregation, but if you have “Aggregate” checked, it is going to perform calculations on your data. You can pick the nature of the calculation as shown below:
The automatically selected type of calculation is “Sum”. This totals up the y-axis’ data for repeating values of the x-axis.
For example, here, in this case, if you check the “Aggregate” option, google sheets is going to add up the heights of the two people aged 23 together, of the two people aged 43 together, and of the two aged 34 together, making the graph look like this, displaying the maximum values.
A before and after displaying the without and with aggregate option ticked have been shown here.
The “Sum” is a good option for when you’re dealing with data that needs to be added such as routine inventory stock update for certain products.
Customize Your Charts: Change Chart Colors, Fonts, and Style
Now, on to the customization part. This chart menu section offers extensive customization options, as part of chart settings, covering a number of chart items for editing, including changes in fonts, grids, background color, and sizes.
Adjusting Chart Styles and Colors:
This section lets you change your chart format by changing the background’s and border’s colors and setting up different styles of title fonts, according to your choice.
Chart and Axis Titles
This is for chart labels and axis labels. From here you can add and edit your chart title, chart subtitle, vertical axis title, horizontal axis title, and even change the fonts and colors of these texts. You can also change your chart title by simply double-clicking on the chart title.
In this example, we have altered the axis labels as well as the graph title to that shown below:
The series section is especially important when you’re dealing with different types of data series, and data series that needs to be visually identifiable from other markers or other series on the graph.
Customizing these series according to your desire will help in better identification.
You can simply use it to change the color of even one type of data too if you want, for example, changing the blue markers into pink or any other color, as per your choice. You can play around with the other options and see what looks best to you.
For example, we only have one data series in this example, but if we were comparing both height and weight against age, it would’ve been crucial to separate them through some means such as color-coding.
This can also be done in a situation where we want a certain data point to stand out out. This can be done through the “Format Data Point” section. Go to “Add” in this section.
After picking a certain point on your scatter graph (as we’re following in this example), you can change its color, shape, and size to make it look more prominent compared to the other data points. This can easily be undone by the delete button present right below the shape-changing option.
In this example, we have represented the data point under consideration in a diamond shape and a mauve colour.
The three options of Error bars, Data labels, and Trendlines are simple options that let you do the following:
Error bars: These allow you to display gaps on your data points to signal the likelihood of, or to leave space for errors or to signal the presence of data over a small range.
Data labels: These mark exact data values from the google sheets table on the data points on the graph.
Trendlines: This option is crucial for the representation of trends in the data if there exists any.
Legends are markers for the identification of different types of points in a graph. This section is for them and allows users to set up and adjusts legends and their positions.
Horizontal and Vertical Axes:
The content of both these sections is similar for setting up, adjusting, and scaling of the axes and their ranges, and also for their formatting.
Gridlines and Ticks:
In this section, you can adjust the chart background grid of your graph. It not only allows users to control the size of the grid they want but also allows them to pick the size of each individual grid cell. There is also an option for adding and formatting a smaller more detailed grid with minor spacing.
Ticks refer to the small marks on the axes on which the vertical and horizontal scales are marked. Minor ticks can also be added, if needed, onto the chart.
Once you’re entirely done with your graph you can copy and paste it from google sheets onto your google document or other Microsoft application such as word. You may also simply have it saved in your google drive from where you can share it as an image. Once well constructed, it is fairly self-explanatory.
How to Make Mini Graphs in Google Sheets
We’ve discussed the traditional charts and graphs, but there is a newer function that produces mini graphs that fit in one single cell.
These single spreadsheet cells containing graphs are called “Sparklines” and this section will explain how to make such graphs in google sheets.
In order to get these, you have to go to an empty cell and in it, enter the formula “=SPARKLINE()”. Inside the bracket, you have to enter your cell range. You may also select it from the spreadsheet by highlighting your desired range.
We carry on with the same example of the amount of rainfall received by different cities across a number of months. This heading of rainfall received has been shown in a single merged cell on top of the months mentioned.
After the range of cells is fed into the formula, pressing enter would produce a small graph.
Now, since we have a larger data set, we will select our mini graph cell and drag its pointer down to the end of our table, which will give us mini graphs for all our rows.
Just like the other charts, these mini graphs or sparklines can also be copied and pasted onto other applications for data representation purposes.