GSP379

Overview

In a challenge lab you’re given a scenario and a set of tasks. Instead of following step-by-step instructions, you will use the skills learned from the labs in the course to figure out how to complete the tasks on your own! An automated scoring system (shown on this page) will provide feedback on whether you have completed your tasks correctly.

When you take a challenge lab, you will not be taught new Google Cloud concepts. You are expected to extend your learned skills, like changing default values and reading and researching error messages to fix your own mistakes.

To score 100% you must successfully complete all tasks within the time period!

This lab is recommended for students enrolled in the Use Functions, Formulas, and Charts in Google Sheets skill badge. Are you ready for the challenge?

Topics tested

• Use functions and data validation rules to validate data.
• Sort and search a spreadsheet.
• Create a chart and embed it in a Slides presentation.
• Calculate descriptive statistics.
• Create a pivot table.

Setup

Before you click Start Lab

Read these instructions. Labs are timed and you cannot pause them. The timer starts when you click Start Lab and shows how long Google Workspace resources are available to you.

This Google Workspace hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Workspace for the duration of the lab.

What you need

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).
• Time. Note the time at the top of the left panel, which is an estimate of how long it should take to complete all steps. Plan your schedule so you have time to complete the lab. Once you start the lab, you will not be able to pause and return later (you begin at step 1 every time you start a lab).
• You do NOT need a Google Workspace account. An account is provided to you as part of this lab.

Open an Incognito window to run this lab. When your lab prompts you to log into the console, use only the student account provided to you by the lab. This allows the activity tracking to verify that you have completed the lab activities.

Start your lab

When you are ready, click Start Lab in the upper left panel.

Find Your Lab's User Email and Password

To access the resources and console for this lab, locate the User Email and Password in the left panel. These are the credentials you use to log in to Google Drive.

Sign in to Google Drive

1. Click Open Google Drive.

Tip: Open the tabs in separate windows, side-by-side.

2. Notice the Email or phone field has been pre-filled with the User Email. Click Next.

3. Enter the Password and click Next.

4. Accept all terms and conditions as prompted.

Google Drive opens and you are signed in to the Student Google Account.

Challenge scenario

Thomas Omar and Seroja Malone started On the Rise Bakery as a small family business to share their love of international flavors and nostalgic baked goods. They expanded from New York City to across North America and now have bakeries around the world. As the company has grown, they have hired staff to help oversee daily operations for multiple locations.

On the Rise Bakery is preparing to launch its 100th location! As a staff member at this company, you create tables and charts in Google Sheets to support the launch. You also analyze data using functions and share insights with your colleagues.

Task 1. Clean and validate data

On the Rise Bakery has a staff directory for its newest location. After signing into Google Drive using the username and password provided for this lab, open the On the Rise Bakery Business Challenge file that has been created for you.

On the Staff Directory sheet, the first names of several employees are not properly capitalized. In column B, use a function to capitalize only the first letter of employee first names.

You also must verify that column E contains a valid email address for each new employee. With Google Sheets, you can use data validation rules and various functions to verify data.

Perform the following two methods of validating email addresses:

• Identify the appropriate function to validate email addresses, and then use that function in column E.
• Create a data validation rule that validates email addresses.

Click Check my progress to verify the objective. Clean and validate data.

Task 2. Organize and find data

You’re coordinating orientation for new staff members. To ensure that you don't miss any celebrations, you must organize the Staff Directory sheet in order of the birthdays listed in column G.

Due to a change in the availability of a facilitator, the food handling training on October 5 is rescheduled to October 10. Update column I to reflect this change.

At least one manager must be notified of this change. In cell A18, use a function to retrieve the email address of at least one manager from the spreadsheet.

Click Check my progress to verify the objective. Organize and find data.

Task 3. Create a chart

Using data in the Staff Directory sheet, create a chart that shows the roles of staff members.

Google Slides makes it easy to present slideshows with embedded charts from Google Sheets. Create a new Google Slides presentation with an embedded chart and use Staff Roles as the filename.

Click Check my progress to verify the objective. Create a chart.

Task 4. Calculate descriptive statistics

On the Rise Bakery needs help with analyzing the Customer Rating sheet, which has data from 100 different orders.

Calculate the average, median, range, and standard deviation of data in cells C2:C101 to complete the descriptive statistics table that spans cells E3:F8.

Click Check my progress to verify the objective. Calculate descriptive statistics.

Task 5. Create a pivot table

After reviewing the summary table, On the Rise Bakery wants more insight into the customer ratings data. Create a pivot table that displays the Average Customer Rating for each Item.

Note: In the Create pivot table dialog, select New Sheet for the Insert to option, and then click Create.

Click Check my progress to verify the objective. Create a pivot table.

Congratulations!

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated February 23, 2024

Lab Last Tested June 07, 2023

Copyright 2024 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.