top of page

Spreadsheet Certification Assignment

Prior to starting this assessment, please create a new Google Drive folder labeled with your name and share it with info.rookiguru@gmail.com.

 

Save every item in this folder. Each item should be labeled with the section + the #. 

For example Excel-1, Excel-2, etc.

Spreadsheet

Go In Order.

Do not skip Anything.

Google is your best friend for anything related to Excel/Google Sheets.

1. Create a Google Sheet outlining the 10 people you interact with most. 

 

The spreadsheet should tell us names, emails, locations, hobbies, the number of siblings they have (guess), and 5 additional fields that you think are relevant. It should also include a ranking of how much you interact with them as well as an hour amount of how many hours a week you interact with them. 

 

2. Using the correct spreadsheet formatting article, make sure you are following every single one of the rules in the article in your spreadsheet. 

 

In a separate sheet, create a checklist of all the items in the article. The checklist should include every single item in the article with written or picture evidence of how you are following each rule. 

 

3. Use data validation for several of the fields in the sheet

 

Set it up. 

 

4. Use conditional formatting to highlight the employees in different colors based on their locations. 

 

Also use conditional formatting to highlight employees in colors based on their ranking.

 

Set it up.

 

5. Use 10 different Excel formulas to learn different things about the data and pull different fields from the data out to display elsewhere in the spreadsheet

 

You must use the following formulas but go above and beyond those:

  • COUNTIF

  • VLOOKUP

  • INDEX & MATCH (used together)

 

Do all your work with these formulas in a separate sheet titled “Formulas” and explain what you’ve done and how this could be useful in different situations (for example, if this was 10,000 rows instead of 10)

 

6. Create a pivot table and then use that pivot table to find the following information. You need to save the pivot table configurations that show this information, you cannot use formulas. 

  • Show the people you spend more than 2 hours/week with

  • Show the people you spend more than 2 hours/week with AND are in India

  • Calculate the number of siblings that the people you spend more than 2 hours/week with AND are in India have

 

Do in a separate sheet titled “Pivot Table”

 

7. Create macros that do the following

  • Highlight all rows red

  • Copy and paste the first row to the last row and then delete the first row

  • Sort the rows by ranking of how much you interact with them

  • A macro of your choice

 

There should be separate buttons for each macro in a separate sheet titled “Macros”

 

8. Write  custom VBA. 

 

Create a button that does the following when tapped:

  • Finds all people you spend more than 2 hours/week with and highlights those rows in red

  • Deletes all people you don’t spend more than 2 hours/week with from the spreadsheet

 

This should be in a separate sheet titled “VBA”

​

9) In another sheet enter your email address and full name so we can send you your certificate if you have passed. 

​

10) All The Best.

bottom of page