Title: Spreadsheet Mission
Course: 21 Things for Students > Section: Suite Tools

Spreadsheet Mission

The SS Mission: Create a Spreadsheet That Uses a Simple Sum Formula

(Link to PDF Information)

Introduction

Have you ever had a subject that made you shiver in your shoes? Glazed eyes with fear or excitement? This mission is one of the most important ones, equipping you with skills to work with and manage information and data (which makes some Control agents tremble). What sort of data? Do you have a collection of any sort, like a music collection, comic books, characters, cards...? Wouldn't it be helpful if you had a record of each one of the items in your collection, and you could sort it by any field (recording artist, title of the song, name of the comic, character name and type, year created? These are all examples of data that can be entered in a field). In this Mission you will be learning all about a tool which helps you manage your data (enter data, track, order, graph, chart, plan parties, budget your money, and much more).

What is a Spreadsheet?

Step 1.  CLICK HERE to find out what a spreadsheet is!

Step 2.  CLICK HERE to learn basic spreadsheet vocabulary.

If you went to the two links you noticed that a program called Microsoft Excel was used in the examples.  Microsoft Excel is a spreadsheet program that can be used to create spreadsheets.  Microsoft Excel is very similar to Google Spreadsheets that we will be using in Google Drive.

Special Note to Agents: If you don't have access to Google Spreadsheets you can do these Quest activities using an application such as: Microsoft Excel, Calcin OpenOffice, iWork (Mac Suite), Free Spreadsheet or Numbers (iPad). The process is very similar across all of the applications.

Quest 1: The Story Begins!
Imagine that you are going to have a celebration and you will be inviting people to your party.  You could use a spreadsheet to keep track of who you invited and how many people are coming.  The spreadsheet will do the calculations for you and help you to stay organized.  Proceed to Quest 1 to begin with the Party Planner Spreadsheet where you will learn how to name a document & enter data, learn the difference between a cell, row, and column.

Spreadsheet Quest 1: Basics

Video hosted at Vimeo.com and created by Kathy Campau and Renee Jorae

  (Link to PDF Directions)

  It's Your Party!

Imagine you are having a celebration and you are inviting guests to your party. There are 5 people in your family and all 5 will be attending.  You have invited Grandpa and Grandma, but Grandpa is out of town fishing that day so only Grandma can attend.  Aunt Lucy has 5 people in her family and 3 will be attending.  Uncle Gus and Aunt Francis have 4 in their family however only 2 can attend that day.  You also want to invite all 9 of your cousins and you have let them know they can bring a friend, so 13 people will be attending.  In addition, you can't forget all 12 members on your Ping Pong team plus the 2 coaches so an additional 10 people will be attending.

You could use a spreadsheet to keep track of the data for you and automatically calculate numbers.  We are going to start with a simple spreadsheet that uses a basic SUM formula. Before beginning you will need to know and understand the words cell, row and column.  See image below for guidance:

Terminology

Creating the Basic Spreadsheet

Spreadsheet Image Sample

Step 1 - Open a new Google Spreadsheet and name it:  Party Planner.
Step 2 - Title three columns - Name, # Invited, # Attending (make sure all of the titles are capitalized).  This is called a Header Row.
Step 3 - List all of the families, neighbors, and groups invited under NAME.  You will need to adjust the column width to fit the name of the people invited.
Step 4 - List the number of people invited under # INVITED
Step 5 - List the number of people attending under # ATTENDING

Resources
    Spreadsheet Help From Google
     Using Google Spreadsheets from About.com

Check this Quest off your Tracking Document

Spreadsheet Quest 2: Format, Insert and Merge

Video hosted at Vimeo.com and created by Kathy Campau and Renee Jorae

 (Link to PDF Directions)

Introduction

You have set up your basic spreadsheet for your party and have entered the data.  There are special formatting features you can do to make it more readable for others.

Format your spreadsheet

Sample Image for Spreadsheet Formatting

Step 1 - Open the existing Party Planner spreadsheet.
Step 2 - Align the text for column B and C so it is in the center.
Step 3 - Highlight column A and color it light blue, highlight column B and color it light green, and highlight column C and make it light pink.
Step 4 - Put a border around each cell.
Step 5 - Have a row labeled TOTALS in cell A9 and make it bold.
Step 6 - Make the header row bold.

Insert and Merge

Sample Image for Spreadsheet Insert and Merge

Step 1 - Highlight row 1, go to INSERT on the menu bar and select ROW ABOVE
Step 2 - In Cell A1 put your name and the type of party you are planning.
Step 3 - Highlight Cells A1, B1, and C1.
Step 4 - Click on the Merge Cells Button.
Step 5 - Make the font for this row size 14, change the font color to blue, and align center.
Step 6 - Check with your SSI Department Chief to see how they want you to submit or share this document.

Resources
    Spreadsheet Help From Google
    Using Google Spreadsheets from About.com

Check this Quest off your Tracking Document

Spreadsheet Quest 3: Fantastic Formulas

Video hosted at Vimeo.com and created by  Kathy Campau and Renee Jorae

 (Link to PDF Directions)

Introduction

So you have created your basic spreadsheet for the party and have formatted it.  One of the really useful tools of a spreadsheet is having the ability to do calculations.  It can do almost everything a calculator can do if you know how to create a formula.  All formulas start with an equal sign.  Let's calculate how many people will be coming to your party!

Using a Formula to Add a Column

Sample Image - Spreadsheet Formulas

Step 1 - Put an addition formula in the cell B10 adding the # of people invited. The formula should look like one of these:

=B3+B4+B5+B6+B7+B8  or  = SUM(B3:B8)

Step 2 - Put an addition formula in the cell C10 adding the # of people attending.  The process is the same as above.  You may also experiment with using the FUNCTIONS button on the toolbar.

Step 3 - When you click off of the formula it should automatically add the numbers.  To see the formula again go to VIEW in the menu and select ALL FORMULAS.  This shows you all of the formulas on the sheet.

Step 4 - Take a screen shot of your spreadsheet with ALL FORMULAS showing.  Ask your SSI chief if they want you to share it with them.

Step 5 - To not see all of the formulas go back to VIEW in the menu and select ALL FORMULAS and it will return to numbers in the columns.

Resources:

    Spreadsheet Help From Google

    Formula Help From Google

    Using Google Spreadsheets from About.com

    Using Formulas from About.com (Hint: Look at all 6 steps of the tutorial for help)

 Check this Quest off your Tracking Document

Spreadsheet Question

What is a cell in a spreadsheet?

- A column and name combined
- A column and row combined
Feedback: Great job!
- A formula
- A small box

Spreadsheet Question

Select one

- =sum(b3:b8)
Feedback: Great job! I am ready for Quest 4: Chart Magic
- $sum(b3:b8)
- +sum(b3:b8)
- -sum(b3:b8)

Spreadsheet Quest 4: Chart Magic

Video hosted at Vimeo.com and created by Renee Jorae and Kathy Campau

 (Link to PDF Directions)

Introduction

The agency is requesting to see a simple visual of your party plans.  The easiest way to visualize something quickly is to make a chart.  This quest will show you how simple it is to make a chart using Google Drive.

Making a Chart

 Sample Image for Charts

Step 1 - Highlight Cells B2 to B8 & C2 to C8 (hint to highlight: hold the shift key and use the arrow keys to scroll down or hold down on the mouse and drag down to the end of both columns)

Step 2 - Select the chart button on the tool bar or INSERT then CHART from the menu.  Choose the kind of chart that you feel would display the information the best.  Also USE ROW 2 AS HEADERS should be selected.

Step 3 - Go to the CUSTOMIZE TAB and give your chart a name (What information is it displaying?) Also, label the horizontal axis and the vertical axis as well.

Step 4 - Select insert.

Step 5 - Drag the chart so you can see it under the spreadsheet OR move it to a different sheet.  Hint: Move your mouse around in the top right corner until you see a triangle drop down menu, then select MOVE TO NEW SHEET.

Resources

   Google Docs Help Inserting a Chart in a Spreadsheet

    Spreadsheet Help from Google

   Using Google Spreadsheets from About.com

Check this Quest off your Tracking Document

Spreadsheet Quest 5: Share

Video hosted at Vimeo.com and created by Renee Jorae and Kathy Campau

 (Link to PDF Directions)

Introduction

You have completed your document and your SSI Department Chief has decided to have you share your Google Spreadsheet document using Google Drive.

DIRECTIONS

Step 1 - Open the Google Docs file called PARTY PLANNER.

Step 2 - In the top right hand corner, click on the blue button that says SHARE.

Step 3 - On the bottom of the page it says ADD PEOPLE. Enter your SSI Department Chief's email address here and click on SHARE.

Step 4 - In addition, you are going to make your spreadsheet available to anyone with a link.  Your SSI Department Chief may let you know the names of 2 individuals that will need to peer edit your spreadsheet.  Email these individuals and request that they edit your spreadsheet making sure your formula is correct and that you have correctly inserted and labeled your chart. To share your spreadsheet and chart you will need to copy and paste the link in your email.

Step 5 (OPTION) - Your SSI Department Chief may also want you to share your spreadsheet as part of a blog or website

 A. Go to FILE then PUBLISH TO THE WEB.

B. Select AUTOMATICALLY REPUBLISH WHEN CHANGES ARE MADE if it is a file that continually is updated.

C. Select START PUBLISHING

D. Click on GET A LINK TO PUBLISH DATA and choose WEB PAGE

E. Select HTML to embed and it will give you an embed code to copy and paste into your web page.

Step 6 -  Check with your SSI Department Chief to see how they want you to submit or share this document.

__________________________________________________

Check this Quest off your Tracking Document

Congratulations! I have completed the quests as assigned by my SSI Department Chief.

Suite Tools Spreadsheet Mission Completed

Congratulations you have completed the Suite Tools SS Mission. You have earned the Cell Agent Award.

You will receive your SS Award for completion from your SSI Chief :

1. Share your updated tracking document
2. Share the web link to your portfolio or share your completed work with your SSI Chief