Back to Blog

How to Make a Chart or How to Create a Graph in Google Sheets

Numbers can be boring. Visualization makes your data more interesting. You can add more power to your data by using charts: grab people’s attention, change their perspective, and make them act by showing facts in a more visual way. 

Fortunately, creating a chart in Google Sheets is pretty easy. (And fun!) In this article, you’ll learn how to insert different charts and graphs, from basic to custom types. You’ll also learn how to customize these charts using the options available in Google Sheets. 

Table of Contents hide

Basic steps: how to create a chart in Google Sheets

To create a chart in Google Sheets, basically, you just need to follow the steps below:

  1. Prepare your data in a spreadsheet
  2. Insert a chart
  3. Edit and customize your chart

Pretty simple, right? Now let’s dive into each step in more detail. 

Step 1. Prepare your data

Add your data in a Google sheet. In case your data is in another source, such as Airtable, Shopify, WordPress, Xero, QuickBooks, and so on, pull them into Google Sheets first. You can use Coupler.io, the tool that provides ready-to-use Google Sheets integrations to retrieve data from the mentioned sources and more.

Next, summarize your data if needed. For example, if you have daily data, you may need to group it by month if you’d like to visualize it monthly. Your data doesn’t need to have a grand total—here’s an example:

Step 2. Insert a chart

Begin by selecting the data you want to show. After that, click Insert > Chart from the menu. Alternatively, you can also click the Insert chart icon in the toolbar.

Just like that! Google will create a default chart for you based on your data. For example, the chart may be a pretty line chart, as the following screenshot shows. However, it may not be the chart you were expecting.

Maybe you hate lines and think that they’re as boring as numbers. But don’t worry, you can edit your chart using the Chart editor that opens on the right side.

Step 3. Edit and customize your chart

If you accidentally closed the chart editor, just double-click on the chart, and it will open again. The editor has two tabs: Setup and Customize. There are many options available to edit your chart—you can customize almost everything here.

You can change your chart type from a line chart to a column or bar chart in the Setup tab. You can also modify your data range, add and remove series, and switch between rows and columns. 

Google’s idea may be different from yours. Thus, you may want to customize a generated chart’s default appearance — from a white background to light gray, from a red bar to green, etc. To do this, use the various options available in the Customize tab.

If you look at the chart area, you’ll notice three vertical dots in the top right corner. This is the chart menu. Click on it, and you’ll see several options as follows:

Notice that you can download your chart as an image (PNG or SVG) or PDF file. You can also copy and paste it to other apps, such as Google Slides or Google Docs. Alternatively, use Ctrl+C and Ctrl+V as keyboard shortcuts to copy and paste it.

Chart vs. graph – what’s the difference?

Every graph is a chart, but not every chart is a graph.

A graph is basically a two-dimensional diagram. This means it illustrates the correlation between two or more sets of data using the horizontal (X-axis) and vertical (Y-axis) lines. Here is an example of a scatter plot graph of monthly expenses data:

  • X-axis (horizontal) shows months (January to December)
  • Y-axis (vertical) shows the amount of expenses in USD

At the same time, a pie chart is not a graph. Why? Pie charts use only one quantitative coordinate. A heat map is also not a graph, whereas bar charts and line charts can be named bar graphs and line graphs respectively. 

Nevertheless, there is no distinct rule on using these terms. Most people use them as synonyms to refer to the same thing: a visual representation of data. Here is how we suggest you differentiate between graphs and charts:

GraphsCharts
Bar graph
Line graph
Stacked bar graph
Histogram
Dot graph or Plot
Scatter plot 
Pictogram graph
Column chart
Area chart
Pareto chart
Mekko chart
Pie chart
Bubble chart
Waterfall chart
Funnel chart
Bullet chart
Heat Map
Radar chart
Spline chart

Not to be confused, bear in mind that Google Sheets treats all graphs as charts. 🙂

Different types of charts in Google Sheets and how to create them

Basically, to create a chart, follow the three basic steps described above, and you’ll be fine. As of this writing, there are 17 chart types available in Google Sheets, and each type also has its own variation. We will cover most of them, but not all. 

How to make a line graph in Google Sheets

Line graphs are best if you’re going to show trends over time and reveal the overall direction of the data via trendlines. This can help you understand your sales trend, see whether the number of support tickets is growing linearly or exponentially, and so on. A line chart is usually better than a bar chart or column chart when showing data over time. This is because trendlines are more straightforward, allowing you to see change over time far more easily.

To create a line graph, make sure to select “Line chart” from the “Chart type” dropdown after you have inserted a chart. The data range for this example is A2:G3.

How to create a multi-lined graph in Google Sheets

For a multi-lined graph, we need to select a larger data range to compare the rest of the items against each other. In our example, the data range will be A2:G6.

Notice that the lines move from point to point over months. And almost always, in the X-axis of a line chart, you’ll see some kind of time frame. 

How to create a curved graph in Google Sheets 

If you want to customize the lines, there are options that are worth considering. One of them is the option to make the lines smooth to give you a different look and feel. 

Simply double-click the chart, then select the Customize tab. Under Chart style, tick the Smooth option to make curved lines.

How to make a comparison mode line chart in Google Sheets

Another option worth considering is making the chart show additional information when you hover over a data value. In the Chart style section, tick the Compare mode to enable this option. You’ll see useful comparison info as the following screenshot shows:

You may need to change the axis scale when dealing with multiple series. We cover this issue in the section “How to make a Pareto chart in Google Sheets“.

How to make a column chart in Google Sheets

Column charts (and bar charts) are often considered the best chart if you’re trying to accentuate the idea of volume – for example, if you’re going to show units of products sold, numbers of tickets sold, numbers of students per faculty, and so on. 

To create a column graph, make sure to select “Column chart” from the “Chart type” dropdown after you have inserted a chart. 

How to make a stacked column chart in Google Sheets

The advantage of a stacked column chart is that it’s simpler, slightly cleaner looking and, at a glimpse, you can tell the total. You can choose two types of stacked column charts from the Chart type dropdown: stacked column and 100% stacked column charts. 

A stacked column chart is best to compare category-to-category total value. If you look at the first chart (the top-left chart), you may ask which is bigger between Store 1 vs. Store 2. But in the second chart, it’s clear that Store 1 is bigger than Store 2

You won’t see the third chart (100% stacked column) very often. The attempt here is to meet some of the limitations of a pie chart. Each individual column here is showing the proportional breakout of the total.

How to create a log scale graph in Google Sheets

Logarithmic, or log, scales can be useful in graphs where a few points significantly exceed most of the data, or when you need to display percentage change or multiplicative factors.

You can add a log scale to your column or bar graph in the Chart editor. Go to Customize => Vertical axis. Scroll down to the Log scale checkbox and select it.

Note: if you want to add a log scale to your bar graph, you’ll find the Log scale checkbox in the Horizontal axis section. 

How to create a ranking chart in Google Sheets

A column chart is one of the best charts you can use to show ranking. Before plotting the data, it’s best practice to sort your data from largest to smallest if you want to see a ranking trend. It will help you come to a conclusion quickly.

How to make a bar graph/chart in Google Sheets

A bar chart is a column chart rotated 90 degrees. Like a column chart, a bar chart is suitable if you want to visualize quantity or volume. Usually it is used if your X axis captions are too long or there is not enough space to represent all of the needed columns.

To create a bar chart, make sure to select “Bar chart” from the “Chart type” dropdown after inserting a chart. 

How to create a double or triple bar graph in Google Sheets

In the screenshot above, the bar graph overlays four items: Orchid, Rose, Sunflower, and Daisy. If you want to compare only two or three of them, just remove the unnecessary item(s) using the Series section:

How to create a legend in Google Sheets bar graph

Chart editor provides numerous customization options. For example, you can tune the legend for your graph. To do this, click the Customize tab and select Legend.

Now you can customize the graph legend:

  • Change its position 
  • Change font and font size
  • Change format and text color

If you don’t need a legend on your bar graph at all, choose None in the position field and the legend will vanish.

How to make a stacked bar chart in Google Sheets

Similar to column charts, there are also options in Bar chart to make it stacked or 100% stacked. 

This type of charts is the best option if you need to create a Gantt chart in Google Sheets.

How to create a profit & loss bar chart in Google Sheets

If you want to look at profits, a bar chart (or column chart) can visualize that. It’s possible to have a negative profit, which is a loss. The bar chart shows that on a negative axis. See the following screenshot:

You can make the color of the negative bars different. To do that, separate the Profit/loss column into two: Profit and Loss. Thus, you’ll have two data series and can customize each series’ color as you want.

And here is what a bar graph made on the Profit and Loss report exported from Xero may look like:

How to make an area chart in Google Sheets

The area chart is a variation of the line chart. It has shade below the lines to help show the magnitude of trends. The area chart has different stacked variations like in a bar chart and column chart. It also has stepped shape variation.

To create an area chart, select “Area chart” from the “Chart type” dropdown after you have inserted a chart. See the following image for area chart variations:

How to make a pie chart in Google Sheets

A pie chart depicts a portion of a whole and is best for displaying pieces that add up to 100%. To create a pie chart, you basically need data with only two columns (or two rows). Here is an example: 

A pie chart has a limitation that if you select data with more than two columns and rows, you can only see a certain category’s values.

To customize the appearance of your pie chart, there are options in the Chart editor that may interest you:

  • Chart style > 3D: to make a pie chart with a 3-D appearance (also applies to other chart types). 
  • Pie slice > Distance from center: to move a slice slightly outside the chart.
  • Pie chart > Slice label: to customize labels inside pie slices.
  • Pie chart > Donut hole: to change the pie chart to a doughnut chart.

Note: We do not suggest using the 3D variant for slice distance and doughnut charts, as it is considered to be a bad practice. These types of charts are hard to read.

How to create a scatter chart in Google Sheets

A scatter chart is often referred to as an X-Y chart or a plotted graph. Use it to show the relationship between two variables. For example, when you want to find out how much one variable is affected by another. 

The closer the data points come to making a straight line, the stronger the correlation between the two variables. Here’s an example of a scatter chart that shows a relationship between the number of units sold and sales volume. 

How to create a slope graph in Google Sheets

On the scatter plot above, it would be great to have a slope line to see whether it is a positive or negative slope. To add the slope line to your plotted graph, go to the Customize tab and select Series. Scroll down to find a Trendline checkbox. Mark the checkbox to add a slope line to your graph.

In our example, we have a progression graph with a positive slope.

How to create a bubble chart in Google Sheets

A bubble chart is similar to a scatter chart. The only difference is that, in bubble charts, you need to add a third dimension, which is the size of the bubble. Here’s an example:

We added Store location and Number of employees columns to the same data set used in the previous scatter chart. Here, the numbers of employees are the bubbles’ sizes, and the store locations represent colors.

How to create a waterfall chart in Google Sheets

A waterfall chart is ideal for showing positive and negative data flow changes. For example, you can use it to show monthly (or quarterly, yearly, etc.) sales changes, cash flow changes, and so on.

You can find the Waterfall chart in the “Other” chart types. Here’s an example of monthly revenue changes plotted using a waterfall chart:

How to create a candlestick chart in Google Sheets

A candlestick chart shows the movement of low, high, opening, and closing values of a currency, security, or derivative. You can use a candlestick chart to analyze the fluctuation of daily/monthly stock prices, currency rates, TV program ratings, rainfall, or temperature. Check out what it may look like in the next example.

How to create a stock chart in Google Sheets

Google Sheets currently does not have “Stock chart in its chart types. So, use a candlestick chart to create a stock chart.

If you want to model daily stock movement, for example, put your data in the following order in a spreadsheet:

  • Column 1: enter the day.
  • Column 2 to 5: enter the high, open, close, and low values.

Then, highlight your data and insert a chart. After that, change the chart type to “Candlestick chart.”

How to make an organizational chart in Google Sheets

You can use an organizational chart to show the relationship between employees and their managers, a hierarchical structure of job titles, a family tree, etc. 

To create an organizational chart for employee-manager relations, add two columns to your spreadsheet. In the first column, list all the employee names. Then, in the second column, list their manager names.

Highlight your data and insert a chart. After that, change the chart type to “Organizational chart“, as the following screenshot shows:

Organizational charts are also great at representing a project work breakdown structure. The data for the chart can be exported from a project management tool as tasks and parent tasks. For example, here is how you can export data from Jira to Google Sheets.

How to create a timeline chart (for days and times) in Google Sheets 

A timeline chart shows important events in chronological order. You can use it, for example, to visualize your daily weight records, daily attendance counts, etc. 

The first column in your data must be in a date (or date and time) format to create a timeline chart. Then, insert a chart and change the type to “Timeline chart.”

The timeline chart displays two time frame views for the data changes:

  • Zoomed view – the larger view which you can zoom in by hour, day, week, month and other options.
  • Full chronology view – the bottom graph, which covers the entire timeframe of data change. The side scrollers let you zoom the view above.

How to make a Pareto chart in Google Sheets

A Pareto chart is a statistical chart that shows a combination of two charts: column and line charts. As of this writing, the Pareto chart is not available by default in Google Sheets, but you can create it using the Combo chart.

Now, let’s see a Pareto chart below that shows sales and their cumulative percentages. 

Here are simple steps to create a Pareto chart as shown above:

Step 1. Sort the sales data (B1-B7 cells) from highest to lowest.

Step 2. Calculate the cumulative percentage. 

Here are easy formulas to calculate it manually:

  • C2: =SUM(B2)/SUM(B2:B7)
  • C3: =SUM(B2:B3)/SUM(B2:B7)
  • C7: =SUM(B2:B7)/SUM(B2:B7)

If you don’t want to copy and adjust the formula for each line, you can simply place this ARRAYFORMULA instead of the column C header:

={“Cumulative %”; ARRAYFORMULA(IF(LEN(B2:B)=0,,MMULT(TRANSPOSE((ROW(B2:B)<=TRANSPOSE(ROW(B2:B)))*B2:B),SIGN(B2:B)))}

Read our blog post to know more about the magic of Google Sheets ARRAYFORMULA Function.

Step 3. Select the data (A1:C7), then insert a chart. Then change the chart type to Combo chart.

Step 4. In the Customize tab, expand Series. Then, select the Cumulative % series, and change its axis to “Right axis.”

How to create a histogram graph in Google Sheets

As you know, a histogram is a graph that distributes closely-related data into groups. Google Sheets allows you to create a distribution graph easily. Here is a data set we have:

Select it, insert a chart, select Histogram chart, and there you go!

Google Sheets histogram automatically distributed the data into five buckets:

  • Bucket one for values 0 to 35
  • Bucket two for values 35 to 70
  • Bucket three for values 70 to 105
  • Bucket four for values 105 to 140
  • Bucket five for values 140 to 175

You can customize the bucket size, as well as the outlier percentile in the Customize tab.

The outlier percentile lets you group outliers with the closest relevant bucket size. For example, a 10% outlier percentile includes 10% of the top and bottom values when calculating buckets.

Charts and graphs in Google Sheets: real-life use cases

How to create a chart with random data in Google Sheets

You may want to generate fake data for your chart (for example, random numbers). In Google Sheets, there are two functions you can use to do that: RANDBETWEEN and RAND. The RANDBETWEEN function creates random numbers within specified min and max values, while the RAND function generates random values between 0 and 0.9999999.

How to use auto-fill to create chart data in Google Sheets

Using auto-fill when preparing data for your chart will save you time. To do that, enter numbers, dates, or text in at least two cells next to each other. Then, select the cells and drag the small blue dot down or across. 

Here’s an example of using auto-fill to generate month values:

How to create a chart from data across multiple sheets in Google Sheets

Let’s say you have a Google Sheets doc that contains raw data about your deals, as well as some calculations such as how many lost/won deals you had in previous years.

You want to reference this data from another spreadsheet and create a graph to visualize the relationship between won and lost deals for the previous years. So, basically, you need to reference data ranges:

  • A76:A80 – years
  • D76:D80 – lost deals by years
  • E76:E80 – won deals by years

You can do this using one of the following options:

  • IMPORTRANGE function
={
   importrange("https://docs.google.com/spreadsheets/d/1o4M1evO7bcnU2vBspjFchF54Uq1z6CTPtdxVNK2l09g/edit#gid=1987922184","Dashboard!A76:A80"),
   importrange("https://docs.google.com/spreadsheets/d/1o4M1evO7bcnU2vBspjFchF54Uq1z6CTPtdxVNK2l09g/edit#gid=1987922184","Dashboard!D76:D80"),
importrange("https://docs.google.com/spreadsheets/d/1o4M1evO7bcnU2vBspjFchF54Uq1z6CTPtdxVNK2l09g/edit#gid=1987922184","Dashboard!E76:E80")
}

Read more about using IMPORTRANGE in Google Sheets.

  • QUERY+IMPORTRANGE function
=query(importrange("https://docs.google.com/spreadsheets/d/1o4M1evO7bcnU2vBspjFchF54Uq1z6CTPtdxVNK2l09g/edit#gid=1987922184","Dashboard!A76:E80"),"select Col1, Col4, Col5")

Read more about using QUERY and IMPORTRANGE in Google Sheets.

  • Google Sheets importer

Note: with Google Sheets importer, you can only select an integral data range, so it’s A76:E80 in our case. 

Read more about Google Sheets importer.

Now you can select the data set and make a graph by following the steps that we described before. For example, here is what the stacked bar graph will look like:

How do I create a pivot table graph in Google Sheets?

The same steps should be followed to create a graph of a pivot table. However, it’s very important to make sure a pivot table is properly structured. Let me explain in the example. We have a data set imported from Airtable

We need to make a pivot table to see the number of products by years. If we add two rows, Product and Year, we’ll get the following view:

The pivot table provides what we needed, but this structure won’t let you build a bar or column graph. So, we’d better remove the Year row and add Year as a column to get the following pivot table:

Now you can easily build a graph by selecting a data range (in our case, A2:C12) and inserting a chart. Mark the “Use row 2 as headers” checkbox to make it work.

Read our guide to master pivot tables in Google Sheets.

How to create a chart with 2 y-axis labels in Google Sheets

Sometimes, adding a second Y-axis to a chart can be useful. You can add it to a line, area, or column chart using a combo chart. Yes, it’s  similar to the Pareto chart explained previously.

Note: You can’t add a second X-axis to a graph.

We’ll create a combo chart using the following data containing a list of states, total users, and new users. 

Select all three columns and insert a combo chart. After that, customize the “Total new users” series to use the right axis. 

In the Customize tab, expand Series. Then, select “Total new users.” and change the axis value to “Right axis.”

How to create a vertical line in a Google Sheets chart

In Google Sheets, unfortunately, you can’t add a vertical line to a chart easily. Suppose you want to add a vertical line in a chart as in the following screenshot:

Notice that the vertical line is on X = 26 (age = 26). 

Step 1. Insert three new rows at the top, and fill them as the below screenshot shows:

To fill the new rows, see the following explanation:

  1. In B2:B4, enter 26 in each cell. This is the value on the X-axis where we will add the vertical line.
  2. In C2:C4, enter 0, 120000 (salary for age = 26), and 200000 (the maximum salary).
  3. In D3, enter 120000 — this is used for displaying a label.

Step 2. Click the Insert chart icon in the toolbar — a new blank chart will be added automatically.

Step 3. In the Setup tab, change the chart options as follows:

  1. Chart type: Line chart
  2. Date range: A1:B11,C1:C4,D1:D4
  3. X-axis: Age <30 
  4. Series #1: Salary
  5. Series #2: Age
  6. Label for Age: Label

And, done! Your chart will appear as in the expected result. 

How to create an interactive map chart in Google Sheets

Just like it sounds, a map chart has the ability to plot data on a map. It’s pretty easy to create a map chart in Google Sheets. You can also share it and make it interactive. 

The following steps demonstrate the process to create a map chart that shows the cumulative COVID-19 cases as of Dec 1, 2020, around the world.

Step 1. Prepare data in a spreadsheet. 

Tip: If you’d like to get data from the Internet, such as from a Wikipedia page, you can use the IMPORTHTML function.

Step 2. Select your data and insert a chart. Then, change the chart type to Geo map.

Step 3. Prepare to publish the chart by clicking the Chart menu, then select Publish chart.

Step 4. Choose whether you want to link or embed your document, make it interactive or not, etc. See a few options as shown in the screenshot below:

And here are explanations for the options above:

  • If you choose to link the document, a confirmation dialog will appear, asking you for confirmation. Click the OK button, and a link will be displayed. You can then share this link via emails and social media.
  • If you choose to embed the document, you will see an iframe code used to embed the document.
  • To make the chart interactive, make sure you choose Interactive from the dropdown. 
  • Using the Publish content & settings, you can choose to publish only the chart, entire sheet, or entire document. You can also choose to automatically republish when changes are made.

Done! Now you have your interactive map chart published. “Interactive” here means that when you hover your mouse over each country, you’ll see the number of its total cases.

How to create a living graph in Google Sheets

A living graph means that your data set will be constantly updated. For this, you need to automate data import from a third-party data source and create a graph of the data imported to Google Sheets. 

How to automate data import

In Google Sheets, there are a few functions that let you automate import of specific data formats:

  • IMPORTDATA to import CSV and TSV data
  • IMPORTRANGE to import Google Sheets data
  • IMPORTHTML to import tables or lists from HTML pages 
  • IMPORTXML to import structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds
  • IMPORTFEED to import RSS or ATOM feeds.

If you need to automate data import from other sources or apps such as Xero, Airtable, Shopify, and others, you should use Coupler.io, a no-code solution that can integrate your spreadsheet with different sources. 

How to create a graph in Google Sheets with every hour value

Here is an example of a living graph. Using Coupler.io, we connected Google Sheets to the Exchange rates API, a free service for current and historical foreign exchange rates published by the European Central Bank. Coupler.io will update currency exchange rates every hour, so the graph will update accordingly. 

This example is pretty simple, but you can build more advanced living graphs without any coding.

How to create a versus graph in Google Sheets

The example above is a distinct case when we use graphs to visualize the comparison of two or more things. It works pretty simply and doesn’t require you to somehow tweak the data. And it’s not necessary to compare X and Y on a time scale. For example, we have three columns:

  • Column A – list of fruits
  • Column B – number of fruits eaten by John
  • Column C – number of fruits eaten by Mary

In this case, we compare the appetite of John vs. Mary, and a graph is the best way to visualize this comparison. Select the range and insert a chart. Google Sheets will automatically fit a bar graph or a stacked bar graph:

Bonus stage #1: how to create dependency graph in Google Sheets

There is no native functionality in Google Sheets for building dependency graphs. However, there is a workaround. We’ll show it in the example of this funny engineering flowchart:

Let’s recreate it in Google Sheets as a dependency graph. First, specify the questions and answers, as well as their dependency in a spreadsheet:

Then apply the following array formula in the C2 cell to create dependency bonds:

=arrayformula("  """&A2:A13&""" -> """&B2:B13&"""")

Go to webgraphviz.com, copy the values from the C column and paste them into the curly brackets as follows:

Click the Generate Graph! button, and there you go!

Bonus stage #2: how to create a graph in Google Sheets with multiple separate data ranges

We have two data ranges:

The idea is to make a graph that represents two lines for each data range separately. However, you can’t just select the range A1:E5 and click Insert chart. What you should do is relocate the second data set below the first one, like this:

Now you can select the range (A1:C9) and create a line graph:

We attempted to cover as many use cases as possible to show you the power of building graphs in Google Sheets. However, we’re pretty confident that it’s just a drop in the bucket. So, if you have an interesting use case, share it with us. Good luck with your data!

Back to Blog

One response to “How to Make a Chart or How to Create a Graph in Google Sheets”

Access your data
in a simple format for free!

Start Free