BEGIN THIS TODAY. YOU WILL FINISH IT NEXT CLASS.

Excel Module 3–Performing Calculations with Formulas and Functions

At the end of this module you will know about how to:

  1. Document formulas and data values
  2. Explore function syntax
  3. Insert functions from the Formula Library
  4. Perform a what-if analysis
  5. AutoFill series and formulas
  6. Use relative and absolute cell references
  7. Use the Quick Analysis tool
  8. Work with dates and Date functions
  9. Find value with Lookup functions
  10. Work the Logical functions

RIGHT NOW:

  1. Autofilling Formulas & Data: Read and do pages EX156 to EX161 and watch this video to help–show me before continuing
  2. Exploring Cell References: Read and do pages EX161 to EX167 and watch this video to help–show me before continuing
  3. Summarizing Data with the Quick Analysis Tool: Read and do pages EX167 to EX169 and watch this video to help–show me before continuing
  4. Working with Dates and Date Functions: Read and do pages EX169 to EX170 and watch this video to help–show me before continuing
  5. Using Lookup Functions: Read and do pages EX171 to EX174 and watch this video to help–show me before continuing
  6. Working with Logical Functions: Read and do pages EX174 to EX179 and watch this video to help–show me before continuing

FINISH THIS TODAY.

Excel Module 3–Performing Calculations with Formulas and Functions

At the end of this module you will know about how to:

  1. Document formulas and data values
  2. Explore function syntax
  3. Insert functions from the Formula Library
  4. Perform a what-if analysis
  5. AutoFill series and formulas
  6. Use relative and absolute cell references
  7. Use the Quick Analysis tool
  8. Work with dates and Date functions
  9. Find value with Lookup functions
  10. Work the Logical functions

RIGHT NOW:

  1. Autofilling Formulas & Data: Read and do pages EX156 to EX161 and watch this video to help–show me before continuing
  2. Exploring Cell References: Read and do pages EX161 to EX167 and watch this video to help–show me before continuing
  3. Summarizing Data with the Quick Analysis Tool: Read and do pages EX167 to EX169 and watch this video to help–show me before continuing
  4. Working with Dates and Date Functions: Read and do pages EX169 to EX170 and watch this video to help–show me before continuing
  5. Using Lookup Functions: Read and do pages EX171 to EX174 and watch this video to help–show me before continuing
  6. Working with Logical Functions: Read and do pages EX174 to EX179 and watch this video to help–show me before continuing

BEGIN THIS TODAY. YOU WILL FINISH IT NEXT CLASS.

Excel Module 3–Performing Calculations with Formulas and Functions

At the end of this module you will know about how to:

  1. Document formulas and data values
  2. Explore function syntax
  3. Insert functions from the Formula Library
  4. Perform a what-if analysis
  5. AutoFill series and formulas
  6. Use relative and absolute cell references
  7. Use the Quick Analysis tool
  8. Work with dates and Date functions
  9. Find value with Lookup functions
  10. Work the Logical functions

RIGHT NOW:

  1. Review Assignment pages EX181 to EX182: Print as instructed
  2. Case Problem 1 pages EX182 to EX183: Print as instructed
  3. Case Problem 2 pages EX183 to EX184: Print as instructed
  4. EXTRA CREDIT–Case Problems 3 & 4 pages EX184 to EX186: Print as instructed

FINISH THIS TODAY.

Excel Module 3–Performing Calculations with Formulas and Functions

At the end of this module you will know about how to:

  1. Document formulas and data values
  2. Explore function syntax
  3. Insert functions from the Formula Library
  4. Perform a what-if analysis
  5. AutoFill series and formulas
  6. Use relative and absolute cell references
  7. Use the Quick Analysis tool
  8. Work with dates and Date functions
  9. Find value with Lookup functions
  10. Work the Logical functions

RIGHT NOW:

  1. Review Assignment pages EX181 to EX182: Print as instructed
  2. Case Problem 1 pages EX182 to EX183: Print as instructed
  3. Case Problem 2 pages EX183 to EX184: Print as instructed
  4. EXTRA CREDIT–Case Problems 3 & 4 pages EX184 to EX186: Print as instructed

GOAL: Before you leave class today you will know how to complete the Excel worksheet cycle, enter labels & values, use fill options, perform calculations using formulas, use the SUM function, and copy formulas.

  1. Excel Section 1
    1. Read and look at pages i – viii to get an overview of the Excel unit
    2. Read and look at pages 1-3 to get an overview of this section
  2. WATCH THIS
  3. Activity 1.1–Read and do pages 4-7
    1. Read the top of page 4
  4. WATCH THIS
  5. Activity 1.2–Read and do pages 8-11
    1. Read the top of page 8–be sure you know the difference between a label & a value
    2. BE SURE TO READ ALL THE BLUE TEXT PARAGRAPHS IN THIS ACTIVITY!!!
    3. Read the In Addition on page 11
  6. Activity 1.3–Read and do pages 12-13
    1. Read the top of page 12
    2. Be sure to read all the blue text paragraphs for additional instruction in this activity
    3. In Addition at the bottom of page 13–VERY IMPORTANT!!!
  7. Activity 1.4–Read and do pages 14-15
    1. Read the top of page 14
    2. #2–READ THE BLUE TEXT
    3. #6–READ THE BLUE TEXT
  8. Activity 1.5–Read and do pages 16-17
    1. What is the difference between copy & paste and fill? Read In Addition at the bottom of page 17.

GOAL: Before you leave today you will know how to test the worksheet, improve the worksheet appearance, sort, use help, preview, change page orientation, print a worksheet, display formulas, and navigate a worksheet.

Excel Section 1

  1. WATCH THIS
  2. Activity 1.6–Read and do pages 18-21
    1. Read the top of page 18
    2. What is the purpose of Alt-ENTER?
    3. #4–IT IS EXTREMELY IMPORTANT THAT YOU UNDERSTAND THIS!!! IF YOU DO NOT ASK FOR CLARIFICATION!!!!
    4. Read and try the In Addition on page 21
  3. Activity 1.7–Read and do pages 22-23
    1. Make sure you know what a ScreenTip is and how to use it!!
  4. Activity 1.8–Read and do pages 24-25
    1. #5–VERY IMPORTANT TO KNOW
    2. #7–Put your name and document name in the footer & print
  5. Activity 1.9–Read and do pages 26-27
    1. Read the top of page 26
    2. READ ALL INSTRUCTION CAREFULLY IN THIS ACTIVITY
  6. BEGIN:
    1. Knowledge Check–Share with me through OneDrive
    2. Skills Review 1 &  Skills Review 2–Share with me through OneDrive
  7. Share a Google Doc with me telling me what you learned today and how you can use it.

FINISH:

GOAL: Today you will have the opportunity to apply what you have learned in Excel section 1.

  1. Knowledge Check–Share with me through OneDrive
  2. Skills Review 1 &  Skills Review 2–Share with me through OneDrive

FINISH:

GOAL: Today you will have the opportunity to apply what you have learned in Excel section 1. SEE THE EXAMPLES!!

  1. Knowledge Check–Share with me through OneDrive
  2. Skills Review 1 &  Skills Review 2–Share with me through OneDrive
  3. Do the Assessments & Challenges for extra credit. Share them with me.

BEGIN EXCEL SECTION 1 QUIZ

Practice Test #1–CLICK PRACTICE TEST ON LEFT–NOT GRADED

Test #1–CLICK REPORTED QUIZ ON RIGHT–GRADED

Creating a Budget

  1. Create this spreadsheet in Excel
  2. Merge & center row one and two from A1 to F1
  3. Make column A WIDER–as shown
  4. Contents of B4 to F4–Bold & Center
  5. Contents of A5, A10, A11, A12, A16, A17, A23, A24, A27, A28, A32, A33, A38, A39, A41–Bold
  6. Put in an amount in cells B6 to B9 (estimate–you may put zero)
  7. Copy the contest of cells B6 to B9 over to C6 to C9, D6 to D9, and E6 to E9.
  8. Repeat the following for the Charitable Giving, Transportation, Communication, Entertainment, and Personal Expenses sections
    1. Put in an amount in cells B_ to B_ (estimate–you may put zero)
    2. Copy the contents of cells B_ to B_ over to C_ to C_, D_ to D_, and E_ to E_.
  9. Put a SUM Function in cells F6 to F10, F12 to F36, and F41 to calculate the total of their respective rows
  10. Put a SUM Function in cells B10 to E10, B16 to E16, B23 to E23, B27 to E27, B3 to B38 to calculate the totals of their respective sections
  11. Put a formula in cell B39 to calculate the totals of each respective expenses section for Week 1 (HINT: add the totals of the Charitable Giving, Transportation, Communication, Entertainment, and Personal Expenses ).
  12. Copy the formula in B39 to C39, D39, E39
  13. Put a formula in cell B41 that subtracts the total of the EXPENSES section from the total of the INCOME section.
  14. Copy the formula in cell B41 to C41, D41, E41
  15. Format all numbers to be Accounting format
  16. What can you do to TEST the worksheet? Do this to your worksheet somewhere
  17. Put your name in cell A42
  18. Print the worksheet TWO times:
    1. Showing the ANSWERS–print normally
    2. Showing the FORMULAS

FINISH EXCEL SECTION 1 QUIZ

Practice Test #1–CLICK PRACTICE TEST ON LEFT–NOT GRADED

Test #1–CLICK REPORTED QUIZ ON RIGHT–GRADED—use tracyweber@bellevue.k12.ia.us for the instructor’s email

Creating a Budget

  1. Create this spreadsheet in Excel
  2. Merge & center row one and two from A1 to F1
  3. Make column A WIDER–as shown
  4. Contents of B4 to F4–Bold & Center
  5. Contents of A5, A10, A11, A12, A16, A17, A23, A24, A27, A28, A32, A33, A38, A39, A41–Bold
  6. Put in an amount in cells B6 to B9 (estimate–you may put zero)
  7. Copy the contest of cells B6 to B9 over to C6 to C9, D6 to D9, and E6 to E9.
  8. Repeat the following for the Charitable Giving, Transportation, Communication, Entertainment, and Personal Expenses sections
    1. Put in an amount in cells B_ to B_ (estimate–you may put zero)
    2. Copy the contents of cells B_ to B_ over to C_ to C_, D_ to D_, and E_ to E_.
  9. Put a SUM Function in cells F6 to F10, F12 to F36, and F41 to calculate the total of their respective rows
  10. Put a SUM Function in cells B10 to E10, B16 to E16, B23 to E23, B27 to E27, B3 to B38 to calculate the totals of their respective sections
  11. Put a formula in cell B39 to calculate the totals of each respective expenses section for Week 1 (HINT: add the totals of the Charitable Giving, Transportation, Communication, Entertainment, and Personal Expenses ).
  12. Copy the formula in B39 to C39, D39, E39
  13. Put a formula in cell B41 that subtracts the total of the EXPENSES section from the total of the INCOME section.
  14. Copy the formula in cell B41 to C41, D41, E41
  15. Format all numbers to be Accounting format
  16. What can you do to TEST the worksheet? Do this to your worksheet somewhere
  17. Put your name in cell A42
  18. Print the worksheet TWO times:
    1. Showing the ANSWERS–print normally
    2. Showing the FORMULAS

PERIOD 8–FINISH THE QUIZ FROM LAST CLASS

GOAL: Before you leave class today you will know how to edit & clear cells, use proofing tools, insert & delete columns & rows, move & copy cells, adjust column width & row height, use AutoFit, change the font, size, style, & color of cells, format numeric cells, adjust decimal places, and use undo & redo

Excel Section 2–Read & look at pages 37-39 to get an overview of this section.

  1. Activity 2.1–Read and do pages 40-41: VIDEO
    1. Read and follow all instructions carefully
  2. Activity 2.2–Read and do pages 42-43 VIDEO
    1. Read and do the In Addition at the bottom of page 43
  3. Activity 2.3–Read and do pages 44-45 VIDEO
    1. BE SURE TO READ ALL THE BLUE TEXT PARAGRAPHS CAREFULLY!!!
  4. Activity 2.4–Read and do page 46-47 VIDEO
    1. #5–This will happen to you!! Be sure you know what to do when it does!
    2. Read and do the In Addition at the bottom of page 47
  5. Activity 2.5–Read and do pages 48-49 VIDEO
  6. Activity 2.6–Read and do pages 50-51 VIDEO

PERIOD 8–DO LAST CLASS’ LESSON PLAN

GOAL: Before you leave today you will know how to change the alignment & indentation of cells, use repeat, add borders & shading, copy formats with format painter, use cell styles & themes, use find & replace, freeze panes, change the zoom, and insert, move, & resize clip art & pictures.

Excel Section 2

  1. Activity 2.7–Read and do pages 52-53 VIDEO
    1. #7–This is important to know in order to format a spreadsheet that is easy to read.
    2. What is the repeat command in Excel?
    3. Read and do the In Addition at the bottom of page 53
  2. Activity 2.8–Read and do pages 54-55 VIDEO
    1. #11–VERY USEFUL– MAKE SURE YOU KNOW HOW TO USE IT! Get help if you need it!!
    2. Read and try the In Addition
  3. Activity 2.9–Read and do pages 56-59
    1. Be sure to read the top of page 56
    2. #12–Understand this
    3. #17–IMPORTANT–What did you do BEFORE you clicked the Merge & Center button?
    4. #22–Do a PRINT PREVIEW–DO NOT PRINT
  4. Activity 2.10–Read and do pages 60-61
  5. Activity 2.11–Read and do pages 62-63
    1. When does freezing panes come in handy?
  6. Activity 2.12–Read and do pages 64-65

GOAL: Before you leave class today you will have the opportunity to apply what you have learned in Excel section 2.

  1. Knowledge Check–Share with me though OneDrive
  2. Skills Review 1, 2, & 3–Put your name & document name in the footer–Print as instructed.

GOAL: Before you leave class today you will have the opportunity to apply what you have learned in Excel section 2.

  1. Knowledge Check–Share with me though OneDrive
  2. Skills Review 1, 2, & 3–Put your name & document name in the footer–Print as instructed.

 

EXCEL SECTION 2 QUIZ

Read and do Challenge 2 on page 74. Print–be sure it fits on 1 page.

 

Practice Test #2–CLICK PRACTICE TEST ON LEFT

Test #2–CLICK REPORTED QUIZ ON RIGHT–tracyweber@bellevue.k12.ia.us

GOAL: Before you leave today you will know how to use the IF function, PMT function, create & use range names, write formulas using dates & date functions, use statistical functions, and create absolute cell addressing.

Excel Section 3

PLEASE get the video on 1/2 of your screen and Excel on the other 1/2. Work with the video and follow along in your book!!

  1. Activity 3.1: Video
    1. ABSOLUTE CELL REFERENCING is extremely important to understand!! Please ask if you need further instruction!
  2. Activity 3.2: Video
    1. ALWAYS check to be sure the correct cell range is selected before entering the function!
  3. Activity 3.3: Video
    1. The more functions you know, the more flexible your worksheets will be. If you do not understand how the functions work, please ask for clarification!!
  4. Activity 3.4: Video
    1. YOUR WRITING FOR TODAY:In a shared with me (tracy_weber@hotmail.com) Word Online Document, explain how to use the PMT function and why it is useful to know.
  5. Activity 3.5: Video
    1. Include in the Word Online Document why creating and using Range Names is useful to know.
  6. Activity 3.6: Video
    1. Include in the shared Word Online Document, how to use the IF function and why it is useful to know.
  7. With 5 minutes remaining in class, send me an email message describing what you accomplished today and what you learned in activities 3.1 to 3.6 that is going to be useful to know.

 

PERIOD 1–GUEST SPEAKER

PERIOD 8–CHECK JMC & GET CAUGHT UP

 

PERIOD 1 (PERIOD 8 SKIP THIS)--WRITE A 2 -3 SENTENCE THANK YOU NOTE TO MEGAN IN YOUR WORD ONLINE FILE FOR THIS CLASS. INCLUDE AT LEAST 1 SPECIFIC TAKE AWAY YOU LEARNED FROM HER.

  1. FINISH Sections 3. 1 to 3.6 before you take the quiz below
  2. Excel Section 3 QUIZ
    1. You are looking into getting a $5000 loan. You are looking at Bellevue State Bank who has an APR of 12% and will give you a loan for 3 years AND Fidelity Bank who has an APR of 15% and will give you the loan for 2 years. Create a spreadsheet that incorporates the following:
      1. PMT
      2. MAX
      3. MIN
      4. AVG
      5. SUM
      6. IF
      7. Named Range
      8. Attractive formatting
    2. YOU MAY DO THIS WITH A PARTNER–BE CREATIVE–YOU MAY ADD MORE INFORMATION TO MAKE THIS WORK

Excel Section 3

FINISH: QUIZ

You are looking into getting a $5000 loan. You are looking at Bellevue State Bank who has an APR of 12% and will give you a loan for 3 years AND Fidelity Bank who has an APR of 15% and will give you the loan for 2 years. Create a spreadsheet that incorporates the following:

  1. PMT
  2. MAX
  3. MIN
  4. AVG
  5. SUM
  6. IF
  7. Named Range
  8. Attractive formatting

YOU MAY DO THIS WITH A PARTNER–BE CREATIVE–YOU MAY ADD MORE INFORMATION TO MAKE THIS WORK

PRINT TWICE–SHOW FORMULAS & SHOW ANSWERS

 

MAKE UP DAY:

  1. Finish the Quiz (see last class’ lesson plan)
  2. Check JMC & PRINT any missing work

 

GOAL: Before you leave today you will know when, how, and why to use charts in Excel. You will be expected to demonstrate your knowledge by writing about the charts as you progress through the lesson.

  • Activity 3. 7: Video
    1. Charts are an EXCELLENT way of visually representing the numbers in your spreadsheet and they make it easier for people to interpret your data.
  • Activity 3.8: Video
    1. In a shared with me (tracy_weber@hotmail.com) Word Online Document, explain the difference between a column chart and a pie chart other than they look different…….WHY?
  • Activity 3.9: Video
    1. In the shared Word Online Document created above, explain why someone would choose to use a line chart.
  • Activity 3.10: Video
  • Activity 3.11: Video
  • Activity 3.12: Video

 

Excel Section 3
Knowledge Check–Shared Google Doc

Skills Review 1
Skills Review 2
Print as instructed with your name and document name in footer for REVIEW 2 ONLY

 

FINISH:

Excel Section 3
Knowledge Check–Shared Google Doc

Excel Section 3
Skills Review 1
Skills Review 2
Print as instructed with your name and document name in footer for REVIEW 2 ONLY

QUIZ NEXT CLASS

CHECK JMC & GET ALL WORK TURNED IN