Graphs in Spreadsheets
Note: If you would like to learn about pivot tables in spreadsheets
, please feel free to check out this tutorial.
Introduction
Even though graphs do not need any introduction, but to put it simply, graphs are a graphical representation of various points of a domain. Graphs help you view and analyze your data in a more realistic and simplified way. Graphs, Plots, and Charts can all be used interchangeably since they all mean more or less the same.
A graph can tell you a lot about your data and makes it easy to understand much like A picture is worth a thousand words!
Types of Graphs
There are many different kinds of graphs, but to name a few:
 Line Graph: Used to visualize the information that is connected over time.
 Pie Chart: A circular statistical graph, which is divided into slices to illustrate numerical proportion.
 Bar Graph: It uses bars to compare data among categories. It can be both horizontal or vertical.
 Cartesian Graph: It is just two number lines that cross at 0. These number lines are called the horizontal axis (the xaxis) and the vertical axis (the yaxis).
 Scatter Plot: Values of two variables are plotted along xaxis and yaxis as dots.
 Heatmap: Represents the data where the individual values contained in a matrix are represented as colors.
 Parallel Coordinate Graph: Used for plotting multivariate numerical data and a common way of visualizing highdimensional geometry.
 Histogram: A bar graph creates a view of the data's distribution. The vertical bars represent the frequency of occurrence by classes of data.
 Box Plot: It displays the fivenumber summary of a set of data. The fivenumber summary is the minimum, first quartile, median, third quartile, and maximum.
Graphs in Spreadsheets
A spreadsheet is an excellent tool for analyzing and understanding your data whether it is calculating simple numerical statistics, summary statistics, filtering your data based on a condition or sorting the data and various other intuition that you could possibly extract from the dataset using spreadsheets as shown in this tutorial.
One of the use cases of spreadsheets is to generate quick graphical views of the data also known as Data Visualization.
Data visualization can be done in two ways:
 To explore the data, you have with you and,
 To demonstrate the insights from the data
In today's tutorial, you will be using Google Spreadsheets.
Google spreadsheets allow a way of graphing, and apart from that it even allows you for a small degree of interactivity in the created graphs (and you can embed them to your websites in multiple ways). You will learn how you can do that later in the tutorial.
However, graphing in google spreadsheets is quite picky on how the data has to be laid out to produce the graphs you require; essentially, the data has to be right next to each other to create a graph (this is different from the way LibreOffice or Excel implement their charts).
Next, let's quickly understand the data you will be using in today's tutorial!
About the Data
You will start with a dataset that is well organized into a row and column format. This particular dataset shows the top 10 companies in the Fortune Global 500 in 2017 which is based on highest gross revenues column.
You will learn how to create graphs from this dataset and work with the google spreadsheet chart editor
, which will allow you to understand and analyze the data much better, and also learn some neat tricks and tips on using graphs in google spreadsheets!
Note: Please make sure you have a valid Gmail account, once you have an account all you need to do is click on this link and create a new blank spreadsheet.
The data is available here.
So, without any further ado, let's learn about Graphs in Google Spreadsheets!
Graphs
Let us start by clicking on an empty cell as shown below and click on the insert chart
tab. Since you have selected an empty cell, this will open a graph/chart window with no data in it and also a chart editor
.
Chart Editor
is the heart of creating graphs in google spreadsheets since most of the functionalities are comprised within the chart editor.
Chart Editor has two main components to it namely setup
and customize
.
Let's first understand some functions in the setup
of the chart editor:
Chart type
consists of mainly graphs and charts like Line, Area, Pie, Column, Bar, Scatter, Map, etc. which can be used based on the type of visualization you would like to plot. From the above figure, you can see that by defaultcolumn chart
is selected.
What is a Column Chart?
Column Chart is a vertical Bar Chart. A column chart lets you compare one or more categories of a dataset over some time.
Data range
is the critical component for plotting a graph or a chart since it takes in the information of the columns that you would like to visualize.
So now let's quickly click on the data range
tab and select a valid data range. You will now be plotting your first graph in google spreadsheet.
You will select all the rows of the name (B)
and the revenue (E)
column ranging from 1  11 as shown below.
Once a valid data range is selected you should be able to visualize your first graph in google spreadsheet!
Voila! Wasn't that so simple?
As you can see from the above figure that Walmart
has the maximum revenue
and Exxon Mobil
has the minimum revenue
. As shown in the figure Royal Dutch Shell
has a revenue of $240B. Similarly, you can hover over any of the companies to get the exact revenue
value.

Pie Chart:  Pie Chart: A pie chart is a circular statistical graph, which is divided into slices to illustrate numerical proportion. In a pie chart, the arc length of each slice is proportional to the quantity it represents.
Let's plot a pie chart between the
name
andrevenue
column. All you need to do is click on thechart type
dropdown menu and selectpie
.It will show a percentage for each company
name
.However, the percentage over which value?  The answer is straightforward all it does is, it sums up the
revenue
column values and calculates the share of each companyname
based on the total value.For example, The sum of revenue equals 2712, and the revenue of
Walmart
is 486. Hence,Walmart
accounts for 17.9% of the total revenue.
Exploring the Customized Functionality
Chart Style
provides you with few functionalities like set the background color, font style, maximize the area of the graph, 3D appearance, etc.
Chart and axis title
: In this section, you can set the chart title, chart subtitle, horizontal (xaxis) and vertical (yaxis) title. To set the xaxis and yaxis titles, you will disablemaximize
in thechart style
, and only then it will be visible to you.
Note: A twodimensional graph like a column chart has two axes. The line along the bottom is called the horizontal or xaxis, and the line up the side is called the vertical or yaxis.
The point at which xaxis and yaxis intersect is called the origin.

Series: It provides you lot of functionality like shifting the vertical axis from left to right axis, error bars, data labels, and trendline.
In the below figure, you can observe that the
revenue
label has been shifted to the right axis. After selecting theright axis
option, you will then select theright vertical axis
option inchart and axis title
to view the label on the right axis.Next, you will select
Data labels
which will then allow you to view therevenue
values for each company without hovering over it and you can change the position, font style and font size of the data labels accordingly.
Series also provides a functionality called Trendline
that indicates the slope (or trend) in a particular data series and is also known as a line of best fit for the given dataset. Trend lines can be useful for predicting future values based on the current data trend.
In google spreadsheets, six different kinds of trend your data can follow are Linear, Exponential, Polynomial, Logarithmic, Power Series and Moving Averages.
In the below figure, you can observe that power series
best fits given the data distribution.
Embedding the Graph in Google Docs
Graph from the spreadsheets can be copied to google docs
in multiple ways namely linked
and unlinked
. Let's first see how you can copy the graph from the spreadsheet.
As shown below, all you have to do is select the graph and click on the three dots on the top right of the graph and in the dropdown menu select copy chart
, and it's that simple!
Now, let's quickly open an empty google doc
and find out how you can paste the graph in two ways.
You will paste the chart in both ways to google docs.
 Graph copied with
Link to Spreadsheet
option will allow you to update thegraph
ingoogle docs
if you apply some changes in thegraph
on thegoogle spreadsheet
, whereas, thepaste unlink
option will provide no such privilege to you!
So, let's quickly do a minor tweak in the graph on the google spreadsheet
and find out whether you will be able to update
the graph in google docs
.
So as you can see the color of revenue
columns are now changed to black from blue. Let's see if you can update the graph in google docs
.
So as you can observe from the above figure that since you did some changes in the original graph in google spreadsheets
, you now have an option to update
the graph in google docs
. Let's quickly select the update
button and find the outcome!
Awesome! So as you can observe the graph that was pasted to google docs
with link to spreadsheet
condition was updated successfully to the original graph in google spreadsheets
.
Bonus: Applying Graph on a Pivot Table
Finally, let's create a graph of a Pivot Table!
Note: For those of you who do not know Pivot Tables can check out this tutorial which covers all significant aspects of a pivot table.
Also, note that the next section of this tutorial assumes that you already know how to create and work with a pivot table!
Let's create a pivot table
in which the rows
contain industry
and columns
contain the sum
of revenues
and profit
. By doing so, you will observe that the industries that appear twice in the dataset, for example, automotive
will be clubbed and their respective values will be summed to one.
Now, let's quickly create a combo chart
out of this pivot table and gather some insights about the industries
.
From the above figure, it is quite clear that the Petroleum refining
industry had the maximum revenue
whereas the Electronics
industry had the maximum profit
in the Year 2017.
Wow! Isn't that amazing, how refreshing and straightforward working with Google Spreadsheets is? You can gather so many insights from your data without even a single line of code.
Conclusion
First of all, congratulations on finishing the tutorial.
There are multiple options to create graphs, and Google spreadsheets happen to be one of them. Google spreadsheets are one of the best tools to analyze your data, and everything resides on Google Drive and no local memory storage needed. Of course, there are other ways of working with graphs like writing a Python or an R script but which option you should choose depends on your skill set, and it is also a tradeoff between what you want to accomplish and how fast you want to achieve this.
However, if you are not much familiar with Python, R or any other programming language, this tutorial is the one you might want to keep handy.
We encourage you to go and check out any additional information that you can possibly find on the Internet related to this tutorial. This way, you will gradually be immersed into the field of data visualization, start getting fluent and gain more indepth knowledge about it.
Please feel free to ask any questions related to this tutorial in the comments section below.
If you would like to learn about creating visualizations in spreadsheets, take DataCamp's Data Visualization in Spreadsheets course.
Spreadsheets Courses