Google Sheets can assist you with chart suggestion. It suggests a chart from your selected date. It may enable you to create a proper chart. But it won’t work in every cases. For example when you want to create a powerful weekly or monthly progress chart, there are certain customisation required. It’s a combination of line and bar chart and also we must use the secondary axis too. So in this tutorial I’ll guide you to create a progress chart in Google Doc Spreadsheet where I will apply the combination of charts and secondary axis too.
Why Progress Chart to Track Your Job Progress?
Every systematic job will be based on a meticulous job schedule. But to achieve the goal, the schedule is not enough. We must strictly monitor the progress and implement the schedule. Progress of the job should be strictly monitored and then to be adjust the schedule, if any lag found.
We can use several method to assess the progress of a scheduled job. Using charts are one of the method to visually assess the progress of a scheduled job.
This is an ultimate tutorial that elaborate each and every aspect of creating a progress chart report in Google Sheets. We are just creating a progress chart report. But on course, you will learn lots of aspects in creating a Chart.
What You are Going to Learn by Creating this Progress Chart in Google Doc Spreadsheet?
- Create charts in Google Doc Spreadsheet.
- Create Combo aka Combination Charts in Google Doc Spreadsheet – Combo charts are the combination of line charts as well as bar or column charts.
- How to implement secondary axis means secondary “Y” axis in Google Doc Spreadsheet. It’s called Left / Right Axis in Google Sheets.
- How to Create an S Curve in Google Doc Spreadsheet – The term “S” curve generally being used in a Job Schedule. Almost all schedule using line charts look like as an “S” shape. It is because the nature of the scheduled progress is like that.
- Formatting of Charts like labelling, colouring etc. – Skipped as any one can easily do it in the chart editor.
- Finally and of course, How to Create a Monthly Progress Report Using Google Doc Spreadsheet – You can create daily, monthly or weekly progress report in the same way.
Why We Are Using Combo Charts Here?
Because we have to show monthly schedule and monthly progress as well as cumulative schedule and cumulative progress. We are going to use bar / column chart to show monthly schedule and progress. For cumulative schedule and cumulative progress we are going to use line chart. So it become a combo chart.
Why We Are Using Secondary Axis Here?
Because we have to show monthly progress / schedule as well as cumulative progress / cumulative schedule. Once created the chart you will understand the difference.
Before going to the steps to create a progress chart in Google Doc Spreadsheet, see the finished chart view above.
Steps to Create Weekly or Monthly Progress Chart in Google Sheets
Type the sample data shown below in a blank Google Doc Spreadsheet. It’s a must to learn. Just reading this tutorial may not work for you!
Now to the Steps:
Update: The below steps updated as per latest Google Sheets Changes. But images below kept as it is. So I will tell you wherever to skip the image. I added new images to incorporate the update.
First select the whole data.
Go to Insert menu, Chart. On the chart editor that appears on the right hand side, select chart type as Combo chart. The below screenshot may wary. Skip this image.
Then click on the chart and from the drop down menu select “Move to own sheet”.
The chart will be moved to a new sheet. Now we can easily edit the chart without any distraction.
The next step is to add the respective series to secondary Axis or here Right Axis. We will move “Cum. Target” and “Cum. Achieved” to the secondary / right axis.
Click on the respective series on the chart to select it. One series at a time.
Skip this image.
First we will change the the series type then move the required series to secondary or Right axis.
How to Change Series Type in Google Sheets Chart?
The default series may be not as per our requirement. You may need to change line chart to column or vice versa. To change series types in Google Sheets chart;
First click any where on the blank space on the chart to activate the chart editor. Under customise tab, click the Series drop down.
Refer the above image to change series type from line to column or column to line in Google Doc Spreadsheet. Select the series and change the type. I mean “Cum. Target” and “Cum. Achieved” should be of the type “Line”. “Monthly Target” and “Monthly Achieved” should be “Column”.
Skip the image below.
Now we should change “Cum. Target” and “Cum. Achieved” to the secondary axis means the right Axis.
How to Change Series to Secondary / Right Axis in Google Sheets Charts?
First select the series “Cum. Achieved”, then on the bottom you can change the axis to right. See the below image.
Repeat it with Cum. Target.
That’s it. You have just competed your Progress Chart in Google Doc Spreadsheet. Now change chart title and axis name.
This way you can successfully create daily, weekly, monthly progress charts or curves in Google Doc Spreadsheet. Hope you understood the complete steps. The above chart is the one I had been using for many years when I worked in planning. But the sample data above is just mocked. For any clarification, please feel free to write to me or post in the comments below.