Spreadsheet Basics 1 & 2 Lessons

All classes will start a new unit on Spreadsheets. After a class discussion on Spreadsheet terms students will work on five practice exercises (2 saved files) which include entering data, surveying, using formulas and creating graphs. Students will also complete a worksheet on reading a spreadsheet.

Spreadsheet Basics 1 Assignment – Example

Objectives:

• I CAN Participate in simple class surveys (Favorite Dessert, How I Get to School, Favorite Pet)
• I CAN Enter data collected in Google Spreadsheet (one survey per sheet)
• I CAN Create a column chart for the data collected from “How I Get to School”
• I CAN Create a pie chart from the data collected from “Favorite Dessert”
• I CAN Create a chart/graph of their choice that best represents the data collected for “Favorite Pet”
• I CAN Recolor all charts and graphs so that the data is visually appealing and easy to read.

Spreadsheet Basics 2 Assignment

For this lesson students will create 2 worksheets in Goggle Sheets (spreadsheet software)

In the first spreadsheet students will organize “How much they made per month in their part-time jobs”.

• I CAN Format cells and numeric data
• I CAN Use formulas to determine total amount earned per month and per job [=SUM]
• I CAN Use formulas to determine the least amount earned and the most amount earned; per month and per job [=MIN, =MAX]

In the second spreadsheet students will organize “student test grades”.

• I CAN Format cells and numeric data
• I CAN Use formulas to determine student & test averages [=Average]
• I CAN Use formulas to determine students lowest and highest test grade [=MIN, =MAX]

The whole group classwork will be scored as follows (total = 100pts):

• Basics 1 – 30 pts
• Basics 2 – 50 pts
• Worksheet – 20 pts

Excel 101

  AGENDA

• Introductions
• Excel Overview & Interface
• Basic Lesson #1 – Survey with charts (column, pie, bar)
• Basic Lesson #2 – Part-time Jobs (formulas & charts on a new sheet)
• Fast Food Facts
• Shopping Spree
  – enter & format data, add wordart & pictures, add headers & footers, print on one page
• Weather Charts
  – Data with 3 charts on new sheets
• Media Use on Edmodo.com
• Review ideas for use in the classroom & for productivity
• Save files (media stick or email)

Excel Advanced Training

Excel Templates & Help

1. Office.Microsoft.com
2. Getting Started with Excel 2007
3. Mail Merge with Excel
4. The Spreadsheet Page

Spreadsheets – Fast Food Facts

Excel Lesson  – Fast Food Facts
lesson Modified from: http://www.bcsberlin.org/lessons/excelff.htm

Objectives

In this lesson you will:

• Study and analyze nutritional information about FAST FOOD
  Question????How healthy are Fries?  Cheeseburgers? Pizza? Happy Meals?
  
• Use (spreadsheet software) to calculate, analyze and  compare nutritional facts such as Calories, Fat Calories, Sodium, Protein or Cholesterol.

Directions

Part 1 – Research

Visit the following Web sites and fill out the Fast Food Worksheet:

GOOGLE DOCS WORKSHEET to Download & Make a Copy

• Site 1. http://www.ars.usda.gov/is/kids/nutrition/story10/fastfoodmain.htm
• Site 2. https://www.choosemyplate.gov/saturated-unsaturated-and-trans-fats
• Site 3. http://sustainablekodiak.blogspot.com/2008/01/what-is-eaten-in-one-week.html

When you are done read interesting facts on this page:
http://www.turnoffyourtv.com/healtheducation/fastfoodfacts.html

Nutritional Facts Label

Part 2 – Gather Information

Explore some Fast Food Sites and plan your project. You must decide what fast food menu items and what nutritional facts you will compare using Spreadsheet software.  Fries?  Cheeseburgers? Pizza? Happy Meals? Select four menu items and four nutritional facts to compare.  Calories? Protein? Sugars?

Fast Food Websites:

• McDonalds http://www.mcdonalds.com/us/en/food/full_menu/full_menu_explorer.html
• Burger King  http://www.bk.com/pdfs/nutrition.pdf
• Wendy’s  http://www.wendys.com/food/NutritionLanding.jsp
• Arby’s  http://www.arbys.com/nutrition/
• Taco Bell  http://www.tacobell.com/nutrition
• KFC  http://www.kfc.com/
• Subway http://www.subway.com/nutrition/nutritionlist.aspx

Part 3 – Excel Spreadsheet

See FastFoods-EXAMPLE

You will be graded on the following:

Excel – Shopping Spree

Student Objectives:
Students will be able to:

• Gather information on the Internet (mock shopping spree – price, item)
• Enter this information into an Excel document
• Enter formulas to calculate totals, balance, least most and average price
• Format spreadsheet to print on one page, add headers/footers, images and word art.

Example Shopping Spree File

Student Directions:

You just won $5,000 to shop on-line at the following Web sites:

• http://www.target.com
• http://www.bestbuy.com/
• http://www.hollisterco.com/hol/homepage.html
• http://www.hottopic.com/hottopic/index.jsp
• www.blackholeboards.com
• http://www.danscomp.com/
• http://thepennycandystore.com

But wait! There’s a catch! The total cost of all the items you select must not exceed (go over) $5000.00, tax included. Also, the total cost of all the items (tax included) must fall between $4995.00 and $5000.00 or you lose everything.

“Not a problem!” you exclaim. “I have a secret weapon called a spreadsheet! I can make it do the math for me – right down to the very last penny! Show me the money!”

1. Before you go on-line, set-up your file as below:

2. Format B2-B25 as currency with 2 decimal places and show the dollar sign.
3. Format D2-D30 as currency with 2 decimal places and show the dollar sign.
4. Save your file in your grade folder in the ShoppingSpree folder as ShoppingSpree_YourFirstLastName.
5. Pick one of the online stores listed above and begin shopping! You must only go to the stores listed and select at least ten items to enter into your spreadsheet.
6. Enter items and quantity.
7. Create the following formulas:

• In D3 – formula to multiply cost of the item by the quantity, then use the fill handle to fill to D25
• In D27 – formula to add prices of all the items (D3 to D26)
• In D28 – formula to calculate tax, sub-total by 5%
• In D 29 – formula to add the subtotal and tax
• In D30 – formula to subtract Grand Total from $5,000

8. Insert a header with your name and the date
9. Insert a footer with Source Information: www.theWebSite.com, format the font as italic.
10. Insert 2-3 pictures.
11. Add a word art heading
12. File > Print Preview to make sure your information fits on one page.
13. Change page setup to horizontal view.

Here’s more good news! If you are able to spend EXACTLY $5000.00, tax included, you win an additional $500.00 “imaginary” cash to spend. Will YOU be the one? Good luck!

Weather Charts

Essential Question:
Explain how spreadsheets simplify the organization of data.

Student Objectives:

• I CAN: Gather weather information from the Internet from the week and year of their birth and the current year’s temperatures during that time period
• I CAN: Enter this information into an Excel spreadsheet
• I CAN: Calculate average, lowest and highest temperatures using formulas
• I CAN: Create and format charts to represent the data

Student Directions:

1. Create a new Google Sheet
2. Save your file in you class folder as:  WeatherDataSpreadsheet.
3. In Row 1 – type the headings as below:
4. In Row 2-8, type the dates of the week you were born. It can be 6 days before or 6 days after your birthday. Format these cells as “text” [Select the cells > Format > Cells > Number Tab > Select Text > Click OK]
5. In Row 9 type – Average
6. In Row 10 type – Lowest Temp
7. In Row 11 type – Highest Temp
8. Important — Format cells B2 – E11 as a number with two decimal place.
9. Go on-line to: https://www.wunderground.com/history/
10. Select the dates (day & year), enter zip code 02719 and search for the weather.  You will be looking for the weather during the week you were born and also this year. Enter your information in the correct cells on you Excel spreadsheet.
11. In B9 enter a function – average temp
12. Use the fill handle across to column E
13. In B10 enter a function – lowest temp
14. Use the fill handle across to column E
15. In B11 enter a function – highest temp
16. Use the fill handle across to column E
17. Rename sheet to WeatherData, change tab color to orange.
18. Format the cell color so that your data is easy to read.
19. Create 3 charts:
    • Bar – Highs of 2017 vs. Highs of year you were born
    • Line – Lows of 2017 vs. Lows of year you were born
    • Column – Average Temperatures

Large Example of Charts & Worksheet