December 4, 2020

Charts & Graphs in Google Sheet

How to make Line Graph in Google Sheet :

Let’s say we have the below data set in the table. It contains the number of sales of the iPhone in 4 consecutive years in 2 cities in USA.

Now to create the line chart based on the above table please follow the below steps.

  1. Select the entire table(A1:C5)
  2. Go to ‘Insert’ menu of the Google Sheet
  3. Click ‘Chart’

4. Select the chart style as ‘Line Chart’ under ‘Line’ in the ‘Setup’ tab of Chart Editor.

  1. The chart will look like above. The X-axis which is the horizontal axis shows the year and the Y-axis which is the vertical axis shows the sale count. In this example, the line chart in Google Sheet is useful to show the comparison of the trend in change of sale count over the years in 2 cities.
  2. Right Click on the chart and click ‘Chart Style’ to open the Chart Editor.

7. Now in the ‘Setup’ tab of Chart editor you can select the Chart Style as ‘Smooth Line Chart’ which is another type of Line Chart available in Google Sheet as follows.

8. Now in the ‘Setup’ tab of Chart editor you can select the Chart Style as ‘Combo Chart’ which is a different type of Line Chart available in Google Sheet as follows. This is actually a combination of Line Chart and Column Chart. Here the iPhone sale count in New York is displayed in the form of column chart and the iPhone sale count in Chicago is displayed in the form of Line Chart.

  1. Now if you want to customize the default combination chart, let’s say you want your own colour to apply or you want the iPhone sale count in New York to show in the form of column chart and the iPhone sale count in Chicago to show in the form of Line Chart then go to ‘Customize’ tab in the chart Editor.
  2. Now under ‘Series’ you will find both ‘iPhones sold in Chicago’ and ‘iPhones sold in New York’. Now for ‘iPhones sold in Chicago’ we have selected the ‘Type’ as ‘Columns’. You can select anything from the list of Types available here as per your requirement.

11. And for ‘iPhones sold in New York’ we have selected the ‘Type’ as ‘Line’.

  1. As you can see in the above combo chart now the iPhone sale count in New York is displayed in the form of column chart and the iPhone sale count in Chicago is displayed in the form of Line Chart as we have chosen.

13. Now we will change the colour of the Column chart by selecting the desired colour(we selected Orange) for the series ‘iPhones sold in Chicago’ in the ‘Customize’ tab of the Chart Editor as follows.

How to make Bar Graph in Google Sheet

For the same data set we will now learn how to create Bar Graph in Google Sheet.

Now to create the Bar Chart  based on our example of iPhone sale count  table please follow the below steps.

  1. Select the entire table(A1:C5)
  2. Go to ‘Insert’ menu of the Google Sheet
  3. Click ‘Chart’

  1. Select the chart style as ‘Bar Chart’ under ‘Bar’ in the ‘Setup’ tab of Chart Editor.

  1. The chart will look like above. The X-axis which is the horizontal axis shows the sale count and the Y-axis which is the vertical axis shows the year. In this example, the Bar Chart in Google Sheet is useful to show the comparison of the sale count over the years in 2 cities.
  2. Right Click on the chart and click ‘Chart Style’ to open the Chart Editor.

7. Now in the ‘Setup’ tab of Chart editor you can select the Chart Style as ‘Stacked Bar Chart’ which is a different type of Bar Chart available in Google Sheet as follows.The Stacked Bar Chart in Google Sheet is useful to show the proportion of the sale count in New York and Chicago of the whole iPhone sale count over the 4 consecutive years.

8. Now in the ‘Setup’ tab of Chart editor you can select the Chart Style as ‘100% Stacked Bar Chart’ which is another type of Bar Chart available in Google Sheet as follows. The 100% Stacked Bar Chart in Google Sheet is useful to show the overall trend in the proportion of iPhone sale percentage for each city. For example, we can visually deduce that the iPhone sale percentage in New York to overall sales increased in 2018 but then decreased in 2019.

9. Now we will use another feature of Bar Chart. In the ‘Setup’ tab of the Chart Editor scroll down to the end and you will find a checkbox as ‘Switch rows/columns’. If you select the same your 100% Stacked Bar Chart will look as follows.

10. Let’s see how does it look for the bar graph by changing the chart type as only Bar Chart. As you can see below, since we have checked the option to switch between rows & columns, now the cities that are New York & Chicago became the Y axis of the Bar Graph.

How to make Gantt Chart in Google Sheet

A Gantt chart in Google Sheet represents tasks in the form of cascading horizontal bar charts. A Gantt chart shows the breakdown structure of the entire project by showing the start and finish dates for each task of the project along with the relationships between the tasks.In this way we can track the activities against their scheduled time or predefined milestones. Google Sheet does not offer Gantt as chart type, but it’s easy to create a Gantt chart by customizing the stacked bar chart type as follows.

Let’s demonstrate the process of creating Gantt chart in Google sheet with a simple example.

We have the below table containing the list of tasks along with the start date of each task and the duration in days for building an Office.

To make Gantt chart in Google sheet we first need to convert the dates to the integer days as follows.

  • Copy the entire table and paste it somewhere else in the same google sheet. Just remove the date values from start date column in the second table.
  • Here we have created the second table just below the first table. Then at the first cell of second column of the second table add the below formula. We have added the below formula in cell B14 and then dragged for the entire column of the second table.
=INT(B2)-INT($B$2)

B2 contains the start date of the first task in the first table.

  • As you can see the integer value of the start day for each task has been added in the second table.
  • Now we will be creating the Gantt chart based on this second table. First select the entire second table and click ‘Chart’ in the ‘Insert’ menu of the Google Sheet.

  • Select ‘Stacked Bar Chart’ as the Chart Type in the ‘Setup’ tab of Chart Editor.

  • Now in the ‘Customize’ tab of the Chart editor select the Colour as ‘None’ for the ‘Start Day’ under ‘Series’. The Gantt chart that updates in real time is now ready as follows.

  • You can also change the chart title as per your requirement as we have updated the chart title of our Gantt chart as ‘Schedule for Building an Office’ under ‘Chart and axis titles’ in ‘Customize’ tab of the Chart editor.

Leave a Reply

Your email address will not be published. Required fields are marked *