Let's Take an Adventure into Edtech

How to Create Your Own Progress Tracker in Google Sheets

This blog post is dedicated to Basingstoke College’s Digital Leaders. I have created this blog post in order to help you learn to use Google Sheets’ features in a real-life scenario so you gain more knowledge and see the beneficial side of using it.

If you feel confident and want to learn more, I can help you create a more detailed progress tracker as well as anything else you would feel useful to help enhance teaching and learning.

I have incorporated texts, images and gifs into this ‘how-to’ and as always, I am available to all of you if you do get stuck.

How to Create Your Own Progress Tracker in Google Sheets

STEP ONE:

Open a BLANK Google Sheet: to do this, go to sheets.google.com and click on the ‘Blank’ template which will open up giving you a spreadsheet which is similar to the interface of Microsoft Excel (minus the sharing and automatically saving abilities)

STEP TWO: CHANGE FONT AND MERGE

In cell A1, type your name

Highlight your name, change the font size to 36 (you can find the font size changer on the toolbar which is similar to all G-Suite and Microsoft products)

Highlight once more and merge the cells. You can do this by clicking on the ‘merge cells’ icon in the tool bar (you can also ‘unmerge’ by clicking the down arrow if you make a mistake).

Your name should now be seen as one cell and you can treat this cell as if it is only one

STEP THREE: MERGE AND ROTATE TEXT

Merge the cells underneath your name and type in the name of your course. You can also center your name and your course title here by clicking on the down arrow on the icon next to ‘merge cells’ and change the alignment to center

In cell A4, add in again the name of your course – to ensure this fits nicely, make the column bigger by dragging the A column out until it passes the text

In cell B4, we will create the ‘Completed’ column where you will be able to use a drop-down menu to select whether you have completed the unit or assignment. To do this, simply type in ‘Completed’ in B4

We will use text rotation to make the text diagonal. To do this, highlight the ‘Completed’ text and click on the icon with the A that has been rotated on the toolbar. Select how you wish the text to be rotated

STEP FOUR: FILL ME IN

Click on cell A6 and begin listing the units/assignments (whichever is easiest for you) in the course.

*Hint* You can use enter to quickly move down the cells

STEP FIVE: DATA VALIDATION

Highlight down from B6 to the cell next to the cell that includes your last assignment/unit

Keeping this selected, go to the horizontal menu at the top and click on ‘Data’ and then at the bottom will be ‘Data Validation’ click on this

As you have already selected the range, we can ignore ‘Cell Range’. Move to the drop down menu where it says ‘List from a Range’, change this to ‘List of items’ then in the text box type in ‘Received,Submitted,Pass,Merit,Distinction,Referral’ – make sure to include the commas, do not include the speech marks

Click ‘Save’

You will now notice little drop-down menus pop up in the highlighted cell (if not, repeat step five – if still no luck, come and see me and we can investigate).

STEP SIX: CUSTOMISATION & PERSONALISATION

Now that the progress tracker is looking pretty much done, we can begin to customise and personalise the progress tracker.

Make column B wider just as we did before.

Select column C and drag and highlight until you get the end of the sheet. Right click and delete these columns.

Do the same with the cell just below your last assignment title and delete the rows. This might take a while as the sheet has 1000 rows altogether.

STEP SEVEN: CONDITIONAL FORMATTING

In this step, we will make a cell change colour depending on what is written exactly in the cell. You may choose your own colours for this

We will start by highlighting all of the cells that contain data validation (the cell that deciphers where you are in the stages of the assignment/unit, if it has been submitted, graded, etc. Whilst this is highlighted, go to ‘Format’ in the toolbar and from the drop down menu, click ‘Conditional Formatting’

If you add a new rule, then from the drop down menu select ‘text is exactly‘, from this in the text box, type ‘Submitted’ exactly how you typed it in the Data Validation stage. Choose your colour using the fill colour tool and press ‘Done’. This should automatically change the colour of any of the highlighted range depending on what is written in the box.

Do this for all of the outcomes.

STEP 8: FILL COLOUR

You are now free to change the colour of whatever cells you’d like. Your basic progress checker is now complete and you can start using it straight away. Don’t forget to name it and keep it safe in your Google Drive.

Let me know if you need any help – if you master this one, the detailed version should be up soon 🙂