Welcome to the Google Sheets Skills Test!
Do you have what it takes to complete the spreadsheet projects?
You'll get the chance to find out very soon!
Note: At the bottom of this page you can find learning resources to help you gain spreadsheet skills. I highly suggest taking the dashboards course, and getting one of the formulas cheat sheets.
Either the free cheat sheet or the ultimate cheat sheet will help you find the formulas that you need. You can also use the search tool on the top of this website to find what you are looking for. You can also simply Google what you are looking for.
What this test is all about
In this lesson I will give you a series of spreadsheet projects to complete, as if you were being assigned a spreadsheet task / project by an employer in the real world. This will not only be a great test of your skills… but it will also be a fun and super effective way to learn (in an organized trial by fire).
Quick introduction of myself
My name is Corey, and I have been working professionally with Google Sheets for many years, including several years as a data integration specialist at an online high school. So I can confidently tell you that these are very realistic spreadsheet tasks that I am giving, as I have been asked to create just about every different type of spreadsheet that you can imagine. You can read more about my story at the bottom of this page if you want.
What to expect from this test
I will give you one set of data, and then I will give you an assortment of tasks to complete based on the provided data. The tasks will start simple, and will get more advanced… progressing into data analysis and dashboard building.
So you'll have the following:
- Data
- Project requirements
And that's it. Nothing else besides some links to helpful lessons and courses (again you can find these at the bottom).
I am not going to tell you how to complete the projects step by step, because that's not how it works at a job. The employer doesn't know how to build the spreadsheets… and that's why they need you.
So you have to be the one to figure it out based on the specifications that they provide. An employer doesn't say, "Use the SUM function to give me the total"… they just say "Give me the total". If an employer wants you to build a dashboard that shows data for only 9th grade students… they don't care which formula you use to organize the data… they just want it done. If the employer tells you to put a total at the top of the sheet, they aren't going to tell you how to add a new row at the top to make room for the total / formula… you'll have to be able to make these formatting / design decisions yourself.
This also applies for when you haven't necessarily been assigned a task, but you want to build something that you have in mind (perhaps to impress your employer).
That being said, everyone who completes these projects will end up building things in a different way than other people. That's the beauty of it, and that's why I am not going to be giving any example sheets to show the finished tasks, because I want what you build to come from your own mind, without any preconceptions from me.
Be creative, and work towards making the sheet functional, according to the criteria / functionality requested in each step.
Download the CSV data required for these projects
Click here to download the project data (CSV)
Okay so I'll give you one hint to get started, so that you can actually get the data into your sheet. Click here to learn how to import CSV data into a Google spreadsheet. After clicking the big link above, the spreadsheet file (CSV) should be in your downloads folder.
This data shows a list of students at a school, each student's score in a course, each student's assigned teacher, etc.
Below are a variety of tasks / projects that are based on this student data.
These are some of the types of tasks that I was asked to do at an online high school. So let's say that your employer gave you the data above, and sent you the following project requirements. How will you go about completing these tasks in a timely manner, to give the employer a professional and presentable spreadsheet that has all of the functionality that was requested?
Basic projects
1) Rename the tab with the raw data, to "Data"
2) Freeze the header row of the "Data" tab, and make the headers big, bold, and centered so that they are easy for staff to read (Always do this in the future for additional tabs with headers)
3) Calculate the total points earned for all students (Display the total at the top of the "Points" column, and make sure the total is frozen just like the headers)
4) Calculate the average percentage score earned for all students (Display the average at the top of the "Score" column, and make sure the average is frozen just like the headers)
Intermediate projects
5) Put a thick border between columns F and G, to use as a divider between the left and right side of the sheet. We will use the blank section on the right of the border / divider, as an area to put more calculations.
6) Calculate how many students have a passing score in the class. A passing score / passing grade is any score that is 60% or higher. Also, in the cell above the calculation, put a text label that says "Students with passing grade" so we know what the number represents. The calculation and the label should both be frozen at the top of the sheet.
7) Calculate how many students received a failing score on their test. A failing grade is any score that is less than 60%. Also, in the cell above the calculation, put a text label that says "Students with failing grade" so we know what the number represents
8) Calculate the total number of students in the class / on the report (Display the number at the top of the sheet, and make sure that the number & the text label are frozen). The text label should be "# of Students"
9) Calculate the percentage of students who received a passing score. (With the label "% Students with passing grade" frozen at the top along with the calculation)
10) Calculate the percentage of students who received a failing score. (With the label "% Students with failing grade" frozen at the top along with the calculation)
11) In the "Score" column, make it so that the cell background color turns green (automatically) when a score is 60% or above.
12) In the "Score" column, make it so that the cell background color turns red (automatically) when a score is less than 60%.
13) Create a new tab, and name it "Passing Grades". The new tab will filter the data to show only data / rows with a passing score (This will be done automatically, by using a formula that connects / refers to the "Data" tab. Make sure that you include the appropriate headers at the top of the new tab (same as the headers from the "Data tab").
Advanced projects
14) Modify the formula on the "Passing Grades" tab so that in addition to retaining the current functionality of showing only students with a passing score, the formula will also sort the data by student name. "Last Name" is the primary column to sort by, and then the "First Name" is the secondary column to sort by. So this one formula will show only data / students with a passing score, where the data is sorted by student name.
15) Create two new tabs. The first new tab will be named "Between 60 and 90". This first tab will show only rows where the scores are between 60% and 90% (By using a formula that connects / refers to the "Data" tab). Make sure that you include the appropriate headers at the top of the new tab (same as the headers from the "Data tab"). Also include scores in that are exactly equal to 60% or 90%.
16) The second new tab will be named "Below 60 or above 90". This second tab will show only rows where the scores are either below 60%, or above 90% (By using a formula that connects / refers to the "Data" tab). Make sure that you include the appropriate headers at the top of the new tab (same as the headers from the "Data tab").
17) Generate a list of each different teacher name, automatically with a formula (remove the duplicates). Make sure that the formula also sorts this list. Put the text label / header "Teachers" above the list of teachers.
18) To the right of each teacher name, display how many students that each teacher has on their roster / on the report (Automatically with formulas). Put the text label / header "Students per teacher" above these calculations.
Expert projects
Note: These are the types of projects that an expert should be able to do, but being able to / learning to complete this specific list of projects does not necessarily make you an expert (unless perhaps you find these tasks easy and are one of the few who don't need additional education to complete these tasks… in which case that would be highly indicative of your expertise).
If you want to truly be an expert, complete the entire dashboards course (linked above and below). Although you will learn a lot during this skills test… remember that it is mostly a test, and not a comprehensive course.
"Master" level projects will soon be added to this page, further below.
19) Further to the right of the teacher names, calculate how many students (with a passing score) that each teacher has. For example, calculate how many of the first teachers students have a passing score, and then do the same thing for each teacher. Put the text label / header "Students with passing grade" above these calculations.
20) Further to the right, calculate how many students (with a failing score) that each teacher has. Put the text label / header "Students with failing grade" above these calculations.
21) Further to the right, calculate the percentage of each teacher's students who have a passing score. For example, calculate the percentage of the first teacher's students who have a passing score, and then do the same thing for each teacher. Put the text label / header "% of students with passing grade" above these calculations.
22) Further to the right, calculate the percentage of each teacher's students who have a failing score. Put the text label / header "% of students with failing grade" above these calculations.
*If you want you can align the teacher data with the headers from the overall calculations, in which case you would be able to get rid of some of the duplicate header names, but this is up to you.
23) Create a pie chart that shows the total number of students with a passing score vs. the total number of students with a failing score. Make the pie slice that represents passing scores, green. Make the pie slice that represents failing scores, red.
24) Create a column chart that shows the percentage of students who have a passing score, for each teacher. For example, the first column in the chart will show the percentage of the first teacher's students who have a passing score. Make the columns dark blue.
25) Create a new tab, name it "Dashboard", and move the charts to this new tab. Make the background color of all the cells dark grey.
26) Create a drop down list (anywhere you want) that allows you to select a student name.
27) To the right of the drop-down selection, use a formula that matches / looks up the teacher, grade level, score, and points of the selected student. i.e. when you select a student, the student's data pops up.
28) Display the letter grade of the selected student:
Between 90% and 100% = A
Between 80% and 90% = B
Between 70% and 80% = C
Between 60% and 70% = D
Below 60% = F
29) Make it so that the cell background color of the letter grade automatically changes depending on the letter:
A = Green
B = Yellow / Green mix
C = Yellow
D = Orange
F = Red
30) Make sure that the sheet looks good, and that everything is nicely aligned and easy to read. Now that you have the functionality in place, make it looks professional and presentable. A little bit of adjusting formatting, colors, etc. can go a long way. Move anything wherever you prefer it, so long as the requested functionality remains.
Master level projects (Coming soon)
Even higher-level projects will be added in the future. These will use completely new sets of data, and the projects / tasks will be even more advanced! Click below to get your free cheat sheet, as well as to sign up for my newsletter so that you stay up to date on new updates.
Study the resources below to increase your skill level:
Google Sheets formulas lessons
My Story of becoming a spreadsheet pro
I remember my first day of being a data integration specialist at an online high school (I was called a "Tech Associate" at that time, but my job was dealing with spreadsheet data from day 1).
Believe it or not, I did not actually have all the knowledge and skills that I needed to perform my work. My Google sheets skills were advanced in comparison to most people, but I was at the very beginning stage of being advanced / capable of working with data on a professional level.
On day 1, I was given multiple sets of data, and specifications for multiple dashboards that the company wanted built (quickly). I didn't already know all of the formulas and functionality that was needed for each project. I had to do lots of research… I had to start from square 1 and reason out what the formula should do for each little task… and then I had to think of how to phrase what I was looking for correctly so that I could Google my problem and find the correct formula / feature. This is why my dashboards course as well as my other tutorials are so great to have, because learning the essential skills up front can save you lots of time as opposed to completely winging it like I did.
So it's up to you as to how you attain the knowledge that is needed to complete the spreadsheet projects that I am going to give you. I highly suggest taking my dashboards course and studying formulas on my website in preparation, but either way… I am going to test your skills so that you can see what you are capable of, and so you that you can increase your skill level in a very fun way. It's like putting together a puzzle or solving a riddle. Solving it will make you feel proud and confident in your skills. Completing my dashboards course, or completing all of these projects, will tell you that you can work with Google Sheets on a professional level (Completion of the entire dashboards course is much better / gives a far higher skill level).
This content was originally created by Corey Bustos / SpreadsheetClass.com
