Aug
15

Google Sheets Chart Tutorial

The tutorial explains how to build charts in Google Sheets and which types of charts to use in which situation. You will also learn how to build 3D charts and Gantt charts, and how to edit, copy or delete charts.

Analyzing data, very often we evaluate certain numbers. When we prepare presentation of our findings, we should remember that visual images are much better and easier perceived by an audience, than simply numbers. Whether you study business indicators, make presentation or write a report, charts and graphs will help your audience to better understand complex dependencies and regularities. That is why any spreadsheet, including Google Sheets, offers various charts as means of visual representation.

How to Make a Chart in Google Spreadsheet

Let's get back to analyzing our data on sales of chocolate in various regions to different customers. To visualize the analysis, we'll use charts.

The original table looks like this:
Original table

Let's calculate the sales results of particular products by months.
Sales by months

And now let's present numerical data more clearly and concisely with the help of a graph.

Our task is to analyze dynamics of sales using column charts and line charts. A bit later we will also discuss research of sales structure with circular diagrams.

Select a range of cells for building your chart. The range should include headers of lines and columns. The headers of lines will be used as indicator names, the headers of columns - as names of indicator values. Besides the amounts of sales, we should also choose ranges with the types of chocolate and with the months of sales. In our example, we select the range A1:D5.

Then choose in the menu: Insert - Chart.
Insert Chart in the menu

The chart is built, the chart editor is displayed. Google Sheets will at once offer you a chart type for your data.
Data in Chart Editor

Usually, if you analyze indicators which vary over time, Google sheet will most probably offer you a column chart or a line chart. In cases, when data is a part of one thing, pie chart is used.
Choosing Chart Type in Chart Editor

Here you can change type of chart according to your wish.

Besides, you can change the chart itself.

Specify, which values you would like to use along horizontal axis.

There is an option to switch rows and columns in a chart by ticking an appropriate checkbox. What is it needed for? For example, if in rows we have names of our goods and sales volumes, the chart will show us sales volume on each date.

This kind of chart will answer the following questions:

  • How did the sales change from date to date?
  • How many items of each product were sold on each date?

In these questions, date is the key piece of information. If we change the places of rows and columns, the main question will turn into:

  • How the sales of each item were changing over time?

In this case the main thing for us is the item, not the date.

We can also change data, used for building the chart. For example, we want to see dynamics of sales by months. For this let's change the type of our chart to line chart, then swap rows and columns. Suppose we are not interested in Extra Dark Chocolate sales, so we can remove these values from our chart.
How to edit chart in Chart Editor

You can see two versions of our chart in the picture below: the old one and the new one.
Two charts based on the same data

One can notice, that rows and columns have changed places in these charts.

Sometimes in the range you've chosen for building a graph there are filtered or hidden values. If you want to use them in the chart, tick the corresponding checkbox in Data Range section of the chart editor. If you are going to use only visible on the screen values, leave this checkbox empty.

After defining a type and contents of a chart, we can change the way it looks.

How to Edit Google Sheets Graph

So, you built a graph, made necessary corrections and for a certain period it satisfied you. But now you want to transform your chart: adjust title, redefine type, change color, font, location of data labels, etc. Google Sheets offers handy tools for this.

It is very easy to edit any element of the chart.

Left-click the diagram and on the right you will see a familiar chart editor window.

Choose Customize tab in the editor and several sections for changing graph will appear.

In Chart Style section, you can change background of the diagram, maximize it, transform straight lines into smooth, make a 3D chart. Also, you can increase or decrease font size and change its color.

Pay attention, that for each chart type different style changes are offered. For example, you cannot make 3D line chart or smooth lines in a column chart.

Moreover, you can change style of labels of the axes and the whole chart, select desired font, size, color and font format.

One can add data labels to a chart.

To make it easier to see how indicators change, you can add a trendline.

Choose location of a chart legend, it can be below, above, on the left, on the right side or outside a chart. As usual, one can change font.

You can also adjust a design of axes and gridlines of a chart.

The editing opportunities are easy to understand intuitively, so you will not encounter any difficulties. All changes you make are immediately displayed on a chart, and if something is done wrong, you can cancel an action right away.

Here is an example of how a standard line chart can be changed: compare two versions of the same chart above and below.
Two versions of the same chart

As we see, Google Sheets offers plenty of opportunities to edit charts. Don't hesitate to try all possible options to accomplish your goal.

How to Make a Pie Chart in Google Spreadsheet

Now we will see, how with the help of Google Sheets charts one can analyze structure or composition of a certain type of data. Let's get back to our example of sales of chocolate.

Let's look at the structure of sales, i.e. the ratio of different chocolate types in total sales. Let's take January for analysis.

As we've already done, let's choose our data range. Besides the sales data, we'll select the chocolate types and the month, in which we are going to analyze the sales. In our case it will be A1:B5.

Then choose in the menu: Insert - Chart.

The graph is built. If Google Sheets didn't guess your requirement and offered you a column diagram (which happens quite often), correct the situation by choosing a new type of chart - pie chart (Chart editor - Data - Chart type).
Pie chart in Chart Editor

You can edit the layout and style of a pie chart the same way, as you've done it for a column chart and a line chart.
The same pie chart in different colors

Again, on the screenshot we see two versions of the chart: the initial and the changed one.

We have added data labels, changed the title, colors, etc. You are free to edit your pie chart as long as needed to achieve the necessary result.

Make Google Spreadsheet 3D Chart

To present your data in a more appealing way, you can make your chart three-dimensional using the chart editor.
How to make 3D chart

Tick the checkbox as shown in the picture above and get your 3D chart.  All the other settings and changes can be applied as it was done before with standard 2D diagrams.

So, let's check out the result. As usual, below are the old version of the chart compared to the new one.
Ordinary chart and 3D chart

It's hard to deny that now representation of our data really looks more stylish.

How to create Google Sheets Gantt Chart

Gantt chart is a simple instrument to create task sequences and track deadlines in project management. In this type of chart, titles, start and end dates, and duration of tasks are transformed into waterfall bar charts.

The Gannt charts clearly show the time schedule and current state of a project.  This type of chart will be very useful if you are working with your colleagues on a certain project, which is divided into stages.

Of course, Google Sheets can't replace professional project management software, but the accessibility and simplicity of the proposed solution are certainly worthy of attention.

So, we have a product launch plan, which can be presented as a dataset below.
Sample of a launch plan

Let's add two columns to our table: the start day of task and task duration.
Insert start day for each task

We put day 1 for the start of the first task. To count the start day for the second task, we shall deduct the start date of the whole project (July 1, cell B2) from the start date of the second task (July 11, cell B3).

The formula in D3 will be:

=B3-$B$2

Pay attention that reference for B2 cell is absolute, which means that if we copy the formula from D3 and paste it to the range D4:D13, the reference won't change. For instance, in D4 we will see:

=B4-$B$2

Now let's count the duration of each task. For this we shall deduct the start date from the end date.
Insert duration for each task

Thus, in E2 we'll have:
=C2-B2

In E3:
=С3-B3

Now we are ready to build our chart.

As you probably remember, in Google Sheets we can use several data ranges to build a chart.

In our case we are going to use names of tasks, start days and durations. This means that we will take data from columns A, D, E.

With the help of Ctrl key select the necessary ranges.
How to insert chart

Then as usual go to menu: Insert - Chart.
How to insert Stacked bar chart

Choose the Chart type Stacked Bar Chart.

Now our task is to make the values in Start on day column not be displayed in the chart, but still be present in it.
How to make the values invisible

For this we should make the values invisible. Let's go to Customize tab, then Series - Apply to: Start on day - Color - None.

Now the values in Start on day column are invisible, but still they affect the chart.

We can continue editing our Gantt chart, change the title, location of legend, etc. You are free to make here any experiments.

Have a look at our final chart.
Final Gantt chart sample

Here one can find the end date of each project stage and sequence of their implementation. Unfortunately, you can't change the location of data labels.

Here are some important tips on working with Gantt chart:

  • You can add new tasks and change their deadlines.
  • Charts change automatically if new tasks are added or changed.
  • You can mark the days on X-axis in more detail, using the chart editor settings:  Customize - Gridlines - Minor gridline count.
  • You can give access to the chart to other people or give them status of observer, editor or administrator.
  • You can publish your Gantt chart as a web-page, which your team members will be able to see and update.

How to Copy and Paste Google Spreadsheet Graph

Click on chart and it will be highlighted at once. In the upper right corner three vertical points will appear. This is the editor icon. Click on it, and you will see a small menu. The menu allows you to open the chart editor, copy a chart or delete it, save it as an image in PNG format (Save image), move a chart to a separate sheet (Move to own sheet). Here one can also add a description of a chart. For instance, if for some reason your chart is not shown, the text of this description will be presented instead.
How to open the chart menu

There are two ways to copy a chart.

  1. Use the described above procedure to copy a chart to the clipboard. Then move to any place on your table (it can be different sheet as well), where you would like to paste your chart. Then just go to Menu - Edit - Paste. Copying is finished.
  2. Click on a chart to highlight it. Use Ctrl + C combination to copy your chart. Then move it to any place on your table (it can be different sheet as well), where you would like to paste your chart. To insert a chart, use Ctrl + V keys combination.

By the way, in the same manner you can paste your chart to any other Google Docs documents.
How to paste to Google Docs

After pushing Ctrl + V keys you can choose either to insert a chart in its current state without possibility to change it (Paste unlinked), or you can save its connection to initial data (Link to spreadsheet). In the second case if you edit the initial chart, its copy on Google Docs will be adjusted.

Move and Remove Google Sheets Chart

To change location of a chart, click on it, hold down left mouse button and move cursor. You will see a small image of a hand, and a chart will move with it.

To remove a chart, simply highlight it and press Del key. Also, you can use Menu for that, choosing Delete chart.
How to delete a chart

If you have deleted your chart by mistake, just push Ctrl + Z to undo this action.

So now if you ever need to present your data graphically, you know how to do that building a chart in Google Sheets.

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com