Excel 2003 & 2007

Microsoft Excel 2003 or 2007

Texts: 

Excel 2003 Complete Concepts and Techniques - Excel Authors:  Shelly, Cashman, et al. - $64.00 -  Search to see if you can find this book used.

Excel 2007 Complete Concepts and Techniques - Excel Authors:  Shelly, Cashman, Quasney - $64.00 -  Search to see if you can find this book used.

Estimated hours:  50 to 200 hours depending on text, program, computer experience, knowledge of Windows, and data entry speed.

Additional Resources:  You may want to purchase a reference manual for Microsoft Office in addition to your regular text book.

Assignments

General Directions:

There are some Excel Quick Reference files that you may want to print at the Shelly/Cashman site.  The Web address is:  http://www.scsite.com/off2003/index.cfm?action=quickref&product=Excel

Ask an instructor to copy the Student Data files that go with the book to your network folder. Save and open files to the network folder rather than using a diskette as suggested in the book.  Be certain you are using the appropriate Network Directory (never Drive C:\My Documents) to store and retrieve your files.  See ‘Saving a Document’ on the “Assignment Submission Procedures” handout.

Excel 2003 Assignments

Complete all work in the text except the “Cases and Places” exercises.  Print and submit the following:

  • Project 1 – Lab 3 (pgs. Ex. 61-62)
  • Project 2 – Lab 2 (pgs. Ex. 134-136) – Email the file to your instructor
  • Project 3 – Lab 2 (pgs. 215-218)
  • Web Feature* – Lab 2 (page Ex.240)  Submit the Dynamic Page Only
  • Project 4 – Lab 3 (pgs. Ex. 302-303)
  • Project 5 – Lab 1 (pgs. Ex. 369-370) – Omit Parts 4 & 5
  • Project 6 – Nothing to submit - Please do not print Lab 1
  • Integrated Feature - Lab 1 (pg. Ex. 480)

Lessons for the comprehensive book only (advanced)

  • Project 7 – Lab 3 (pgs. Ex. 564-566)
  • Project 8 – Lab 2 (pgs. Ex. 634-635)
  • Project 9 – Lab 3 (pgs. Ex. 723-724)

View all work in “print preview” before printing.  Before submitting any work, check it thoroughly for quality.  All work should be proofread, legible, spell checked and formatted correctly for data type (Currency, decimal places, etc.). Clearly identify each printout with Project #, Lab #, and instruction # for multiple printouts. Create a cover sheet for each project, attach the lab and submit it for review.

Excel 2003 Corrections/Notes

Project 2

Page EX132 #1 – The data is in table 2-8, not 2-9.

Project 3

Page EX220 #10 – The formula examples (pseudo code) in the book can be a little confusing.  Let’s take a look at the formula you need to enter into cell F4, the Gross Pay cell. 

If Hours <=40, then Gross Pay = Rate * Hours, otherwise Gross Pay = Rate * Hours + 0.5 * Rate * (Hours -40)

From this example, most students try to use this formula:

=IF(C4<=40,F4=B4*C4,F4=B4*C4+0.5*B4*(C4 -40))

Unfortunately, the above formula produces what is called a circular reference.  The inclusion of “then Gross Pay” and “otherwise Gross Pay” in the sample formula is a bit misleading.  Typically, when you write a formula you cannot refer (include) the cell you are in.  Since you are putting the formula in cell F4, you cannot include F4 in the formula. Here is the way I would write the sample formula:

If Hours <=40, Rate * Hours, Rate * Hours + 0.5 * Rate * (Hours -40)

Let’s break this down.  “If statements” are used to perform tests on cells.  There are three parts to the “if statement”: the logical test, the formula if the test results are true, and the formula if the test results are false.  Each part of the formula is separated by a comma.  Essentially the formula works like this:  If the test is true, then do this, otherwise do this.  The first comma in statement indicates then (if the statement is true), the second comma indicates otherwise (if the statement is false). Let’s take a closer look at the three parts:

If Hours <=40 - is the logical test portion of the formula.  It checks cell c4 to see if the contents are less than or equal to 40

, the first comma indicates the end of the logical test.  The next part of the formula tells Excel what to do if the test results are true.  When we say this formula out loud we would usually say “then” when we encounter the first comma in an “if statement”. 

Rate * Hours – is the formula that Excel uses if the logical test results are true.  So if the contents of cell c4 are less than or equal to forty then the rate amount should be multiplied by the hours worked.

, the second comma indicates that formula that follows should be used if the logical test is false.  When we say this formula out loud we would usually say “otherwise” when we encounter the second comma in an “if statement”.

Rate * Hours + 0.5 * Rate * (Hours -40) – is the formula that Excel uses if the logical test results are false.  So if the contents of cell c4 are greater than forty this formula should be used.

With this new knowledge, try to write the formula for cell F4.  If you still are having trouble, I have provided the formula below, but try to figure it out before you look at my solution. 

Formula Solutions for #10

a:  =IF(C4<=40,B4*C4,B4*C4+0.5*B4*(C4 -40))

The formula in cell I4 works the same way as the Formula in cell F4.  You should not include cell I4 in the formula.  Also, there is no need to include the comma after 38.46.  I have provided the formula below, but try to figure it out before you look at my solution. 

b:  =IF(F4-D4*38.46>0,0.2*(F4-D4*38.46),0)

Page EX220 #11 – Maximum Social Security is cell B15.  This should be an Absolute cell reference.

Project 4

Page EX296 Table 4-6 – The formula for cell C9 should be =-PMT(Interest_Rate/12, 12 * Years, Loan_Amount) Be sure to include a minus sign (-) at the beginning of the formula. 

Page EX298 #6-c - The formula for cell G4 should be 12*C10*C8

Page EX299 #5 (part 2) – C6 should be the Row input cell and C7 should be the Column input cell.  The subtitle of the worksheet should be “Company Match in Left Column and Annual Return in Top Row”

Page EX301 #6 – You should add a green border, not a black border. 

Page EX301 #13 – the Operating Income results in cell C21 for Data Set 1 should be $8,037,500

Page EX301 #14 – Part of the exercise was not printed.  I would recommend that you unhide rows 7-21. 

Page Ex302 #7 – The piggy bank graphic is not in your user directory.  Search the clipart gallery for “piggy bank” and use any piggy bank image that you like.  When you link to the MHT file to the graphic, be sure to browse for the file in your user directory (O:\).  Remember, we do not use floppies (a:\) in the classroom.

Page 338 – We are having problems with the Subtotals feature.  Please skip pages EX338-342. 

Excel 2007 Assignments

Complete all exercises in the book unless otherwise indicated. Learn It Online exercises are optional. Do not complete the Cases and Places.

If you already know Office 2003 you should check out the Interactive Guides for all of the Office 2007 programs.  They are very helpful for learning the new Office 2007 interface.

Submit the following In the Lab assignments for Excel 2007: 

  • Chapter 1 - Lab 3 (pgs. 77-79)
  • Chapter 2 - Lab 2 (pgs. 151-154)
  • Chapter 3 - Lab 3 (pg. 241-244)
  • Excel Web Feature - Lab 2 (pg. 262) - Ask your instructor how to preview your Web page in a browser
  • Chapter 4 - Lab 3 (pgs. 332-334)
  • Chapter 5 - Lab 2 (pgs. 408-411)
  • Chapter 6 - Lab 1 (pgs. 496-499)
  • Excel Graphics Feature - Lab 1 (pg. 526)

Excel 2007 Notes

Chapter 1

Page EX 30 #1 - In the classroom we save to the network drive O:\.  Files should be saved to the Project 1 folder inside your Excel folder. Ask your instructor for help saving your document. Each time the book tells you to save to a USB drive save to drive O:\ instead.

Web Feature

Page EX 260 #1 - It is easier to open Internet Explorer (Start > Internet Explorer) and click File > Open > Browse.  Click the Look In drop down arrow and select drive O:\ (your AM or PM folder).  Double click your Excel folder and navigate to the file that you want to open. Select the file and click Open and OK.

Special Notes:

If your lesson is returned “See Notes”, you must make all indicated revisions in order to receive credit.  You will have only one attempt to correct assignments.  Resubmit the original packet with the corrections attached.  If the project is still incorrect after the second resubmission, you will be given a ‘no credit’.  You may not have more than one ‘no credit’ to qualify for a certificate.

Certificates

Ask your instructor to help you to create an account on the DDC Testing Center.  You can use this system to prepare for your assessment and to prepare for the Microsoft Office Specialist exam if you decide to pursue it.

Certificates are awarded to students who:

Demonstrate effort to learn and problem solve.

Accurately complete all required project labs (one retry is permitted per project).

After completing the book, ask your instructor to take the practice DDC test to prepare for the Core Exam.

Receive 80% or better on an industry standard test.  Two retries are permitted.  ESL students are allowed instructor assistance.

Full course certificates require an additional, project-based test that takes the entire class period.  Ask an instructor for the project-based test after completing the test.