Make your own free website on Tripod.com

Mr. Gonzalez's Website
Weslaco East High School

Wildcats

Excel

 

| Home |BCIS (II) |
| Access | Excel | MicroType | PowerPoint | Word |

Monday, May 25 (A)

Exercise 35 (Lesson 4:  Pg. 395 - 399)
Objectives: 
Create an Exploded Pie Chart, size the Plot Area or Legend in a Chart, Create a Stacked Area Chart.

  • 35BalSheetP&L file, Right-Click here (Save Link As) to download/save...

  • See Handout for directions...

  •  

  • Print the Exploded Pie Chart (Include a "Normal" Header and Footer)

  • Include the tab name in the center section of the Footer.

  • Include "Exercise 35 (Lesson4:  Pg. 399)" underneath the tab name.

  •  

  • Print the Stacked Area Chart (Include a "Normal" Header and Footer)

  • Include the tab name in the center section of the Footer.

  • Include "Exercise 35 (Lesson4:  Pg. 399)" underneath the tab name.

  •  

  •  

  • NOTE:  Successful completion of "extra" assignment will earn you an additional 2 "bonus" points towards your Semester Average


Thursday, May 21 (A)

Exercise 34 (Lesson 4:  Pg. 388 - 393)
Objectives: 
Create a Stock Chart, Add  Secondary Value Axis to a Chart, Change Data Market Format.

  • 34MWTStock2008 file, Right-Click here (Save Link As) to download/save...

  • See Handout for directions...

  •  

  • Print ONLY the Stock Chart (Include a "Normal" Header and Footer)

  • Include the tab name in the center section of the Footer.

  • Include "Exercise 34 (Lesson4:  Pg. 393)" underneath the tab name.

  •  

  • Skip Step 13 - Do NOT Print the Worksheet!!!

  •  

  •  

  • NOTE:  Successful completion of "extra" assignment will earn you 2 "bonus" points towards your Semester Average


Tuesday, May 19 (A)

Exercise 33 (Lesson 4:  Pg. 380 - 386)
Objectives: 
Add or Remove Data in a Chart, Set Data Label Options, Set Data Table Options, and Format a Data Series

  • 33MWTFirstQuarterBudget file, Right-Click here (Save Link As) to download/save...

  • See Handout for directions...

  • NOTE:  After Step 4b, Save your work.  Step 4c will cause an error, and close Excel!!!

  •  

  • Print ONLY the 3-D Pie in A16:F41 (Include a "Normal" Header and Footer)

  •  

  • Print ONLY the 3-D in H16:P41 (Include a "Normal" Header and Footer)

  • 10 POINT BONUS:  Determine how to separate the data table from the graph

  •  

  • Skip Step 17 - Do NOT Print the Worksheet!!!

  •  

    • Notify instructor of your group partner(s) names

    • Sit together with YOUR group

    • Staple Group's Printouts TOGETHER!!!

 

Group
Size
Total Points
Possible
1 90
2 - 3 100

 


Thursday, April 23 (A)

Exercise 65 (Lesson 10:  Pg. 330 - 335)

Skills Covered

  • Formula Error Checking

  • Error Messages

  • Audit Formulas

  • Use the Watch Window
  • Evaluate a Formula
  • Suppress Errors While Printing

Software Skills:  If you have a a problem with formulas in a large or complex worksheet, working through each formula to locate the values in the cells it references and to verify that everything is all right can be a tedious, complex job unless you use Excel's error and formula auditing features.

Application Skills:  It looks like the sale of your old building and the purchase of a new headquarters for the Wood Hills Animal Clinic is going to go through.  Before you can get final approval for your loan, however, you must prepare a balance sheet and a profit and loss statement.  You've been working on the profit and loss statement, and there's just something wrong with the numbers.  Your hope is that Excel's powerful formula auditing tools can help you sort out the problem.

65BalSheetP&L file, Right-Click here (Save Link As) to download/save...
Read the ENTIRE chapter BEFORE working on the exercise!!
Assignment will count as a QUIZ grade


Tuesday, April 21 (A)

Exercise 63 (Lesson 10:  Pg. 320 - 324)

Skills Covered

  • Conditional Sum Wizard

  • Conditionally Summarize Data with AVERAGEIF and AVERAGEIFS

Software Skills:  With the Conditional Sum Wizard, you can quickly create a formula that totals only certain cells in a range, such as those cells that contain sales amounts for Indiana.  The Conditional Sum Wizard takes the guesswork out of trying to use the IF function to perform the same task.  On the other hand, sometimes a simple IF function is just what you need, as in the case of the AVERAGEIF and AVERAGEIFS functions.

Application Skills:  Your boss at Wood Hills Animal Clinic has asked you to modify the monthly sales report and create an analysis of sales based on several factors such as animal type (cat versus dog, for example) and purpose (ear infection versus flea control for example).

63AugDrugSales file, Right-Click here (Save Link As) to download/save...


Exercise 64 (Lesson 10:  Pg. 326 - 328)

Skills Covered

  • Format Text with Formulas

  • Replace Text

Software Skills:  Using a series of simple text functions, such as PROPER, UPPER, LOWER, and SUBSTITUTE, you can quickly effect changes to text that's been entered incorrectly.  For example, with the UPPER function, you can change the text in a cell to all uppercase.

Application Skills:  As the new Human Resources Manager for PhotoTown, you've been busy getting familiar with their various employee and benefit related worksheets.  It's just been brought to your attention that the Employee Listing has several problems, all text related.  It's your hope that you can make the necessary corrections using Excel's vast array of text functions, avoiding the need to retype data.

64PhotoTownEEListing file, Right-Click here (Save Link As) to download/save...


Friday, April 17 (A)

Exercise 71 (Lesson 10:  Pg. 368 - 369)

QUIZ:  This assignment will count as a Quiz grade

NOTE

10 Point BONUS for anyone willing to demonstrate and explain the formulas to the class...

Application Skills:  You supervise the Customer Account Representatives at Intellidata, and it's their job to service the accounts for your 2,000 plus clients.  You're anxious to stop the increasing number of clients who have been switching to rival BitiBank, so you've made some changes in the department.  You've also been tracking client numbers lately to see if your changes are making a difference.  Your boss, the Chief Financial Officer (CFO), is interested, too, so you've created a report, which you need to spruce up a bit before printing it out for him.

71ClientOutlook file, Right-Click here (Save Link As) to download/save...
70Intellidata logo image file downloaded/saved in previous assignment...

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 369, Exercise 71" (Line 2)


Wednesday, April 15 (A)

Exercise 70 - On Your Own (Lesson 10:  Pg. 367)

Create a NEW Excel Spreadsheet file (OXL70)...


Tuesday, April 7/Thursday, April 9 (A)

Exercise 69 (Lesson 10:  Pg. 357 - 359)

Skills Covered

  • Insert WordArt

Software Skills:  WordArt allows you to bend, stretch, and rotate text to create dynamic effects.  With a simple piece of WordArt, you can add an element of surprise to an otherwise boring worksheet full of numbers.  WordArt gives your worksheets a professional touch, enabling you to create a lasting impression with something that takes only minutes to create.

Application Skills:  As the Chief Technician and Repairperson at Breakaway Bike Shop, you must detail each part and the amount of labor you put into any bike repair.  You've repaired a customer's bike, and you want to make certain she's notified about the extent of the work you did.  So you'll add text to the repair sheet that explains the changes.

69PartsReplacement file, Right-Click here (Save Link As) to download/save...


Exercise 70 (Lesson 10:  Pg. 361 - 365)

Skills Covered

  • Insert Clips

  • Insert Pictures

  • Format Graphics

Software Skills:  Images and graphics help the mind comprehend what it's seeing.  Even the slightest amount of aesthetics can make it easier for a reader to digest a page full of otherwise boring figures and statistics.  In this exercise, you'll learn how to import pictures from image files and from clip art.

Application Skills:  To add the finishing touch to the usage breakdown statement you designed for Intellidata Services, you need to add the corporate logo and some clip art from Microsoft's collection.  Luckily, you've just learned how to search for clip art, insert, and resize it.

70MonUsage file, Right-Click here (Save Link As) to download/save...
70Intellidata logo image file, Right-Click here (Save Link As) to download/save...

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 365, Exercise 70" (Line 2)


Friday, April 3 (A)

Exercise 66 (Lesson 10:  Pg. 337 - 341)

Skills Covered

  • Draw Shapes

  • Resize, Group, Align, and Arrange Shapes

Software Skills:  After putting all that hard work into designing and entering data for a worksheet, of course you want it to look its best.  In previous lessons, you've learned how to add formatting, color, and borders to a worksheet to enhance its appeal.  But to make your worksheet stand out from all the rest, you may need to do something "unexpected," such as adding your own art.  You can insert predesigned shapes (such as stars or arrows) or combine them to create your own designs.

Application Skills:  You're the accountant at Wood Hills Animal Clinic, and you're going over the profit and loss statement for 2007.  You notice that the cost of goods sold has gone down (caused by decreased inventory costs), so you want to create some shapes that help call attention to that for the presentation you'll be making at the next meeting.

66BalSheetP&L file, Right-Click here (Save Link As) to download/save...

  • Save, but do NOT Print Spreadsheet


Exercise 67 (Lesson 10:  Pg. 343 - 347)

Skills Covered

  • Format Shapes

  • Add Shape Effects

Software Skills:  When shapes such as rectangles, block arrows, and banners are added to a worksheet, they originally appear in the default style--a shape with a black outline, filled with the Accent 1 color.  You can change both the color and the outline style of any shape easily.  You can also add special effects such as shadows and soft edges.

Application Skills:  You're the accountant at Wood Hills Animal Clinic, and you're preparing for an upcoming meeting in which you'll present the profit and loss statement for 2007.  You've drawn some shapes to highlight an important improvement and now you want to format them.

Open the 66BalSheetP&L file edited in Exercise 66...

  • Save, but do NOT Print Spreadsheet


Exercise 68 (Lesson 10:  Pg. 349 - 355)

Skills Covered

  • Add Text to a Text Box, Callout, or Other Shape

  • Add 3-D Effects
  • Rotate Shapes

Software Skills:  If you need to place text in some spot within the worksheet that doesn't correspond to a specific cell, you can "float" the text over the cells by creating a text box or by adding a callout.  A text box or callout can be placed anywhere in the worksheet, regardless of the cell gridlines.  You might use a text box or callout to draw attention to some particularly important data in the worksheet.  If you don't like the look of a plain text box or a balloon-like callout, you can add to any other shape you choose.  Add 3-D effects or rotation to shapes to really make them stand out.

Application Skills:  The financial review at Wood Hills Animal Clinic is fast approaching, and you still have a few more changes you want to make to the profit and loss statement.  In this exercise, you'll add some text to the banner, insert a text box, and rotate the logo to finalize the look.

Open the 66BalSheetP&L file edited in Exercise 66, and 67...

  • Print the spreadsheet

  • Add a "Normal" Header

  • Change to PORTRAIT Orientation

  • Adjust Scaling - Fit to 1 Page

  • Change the Top and Bottom Margins to 0.5"

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 353, Exercise 68" (Line 2)


Wednesday, April 1 (A)

Load/Install the Solver Add-in...
The Solver Add-in is a Microsoft Office Excel add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first.

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  2. Click Add-Ins, and then in the Manage box, select Excel Add-ins.
  3. Click Go.
  4. In the Add-Ins available box, select the Solver Add-in check box, and then click OK.

    Tip  If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in.

    If you get prompted that the Solver Add-in is not currently installed on your computer, click Yes to install it.

  5. After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data tab.

 

Exercise 53 (Lesson 7:  Pg. 276 - 281)

Skills Covered

  • Use the PMT Function

  • Create What-If Data Tables

  • Solve a Problem with Goal Seek
  • Use Solver to Resolve Problems

Software Skills:  What-if analysis allows you to determine the optimal values for a given situation. For example, if you know that you can only spend a maximum of $32,000 this year on new computers, you could adjust the monthly budget amount so you could spend the total amount by the end of the year and yet still remain within your department's monthly budgetary constraints.

Application Skills:  As the owner of Restoration Architecture, you're always watching the bottom line.  You're preparing a bid for the renovation of your town's library, and you want to run the numbers through Excel before submitting it.  Specifically, you want to use Solver to help you analyze what you can afford to pay the plumbers on the job (the last remaining labor cost for which you need bids) and yet maintain a decent profit.  Then you'll create a couple of data tables to compute the cost of the small construction loan you'll need if you get the job and the estimated amount of any increased costs you might encounter if the job runs over deadline.

53BidSheet file, Right-Click here (Save Link As) to download/save...

PRINT All Four (4) 53BidSheet Spreadsheet Worksheets

  • PRINTOUT #1 (Bid Sheet) Tab

  • PRINTOUT #2 (Materials) Tab

  • PRINTOUT #3 (Labor) Tab

  • PRINTOUT #4 (Loan) Tab

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 280, Exercise 53" (Line 2)


Monday, March 30 (A)

  • Pop Quiz (Organizational Chart)
  • Click here for instructions...

Exercise 52 (Lesson 8:  Pg. 270 - 274)

Skills Covered

  • Create Predictions and Estimations

  • Use FORECAST

  • Use TREND
  • Use GROWTH

 

READ the Notes regarding LINEAR TREND, and GROWTH TREND Predictions on Pg. 270.

  1. In a LINEAR TREND, if your first value in a series is 25, and the next value is 32; what is your LINEAR TREND Prediction? 
    Using the Trend worksheet below, type your answer in your Footer Left Section.
     

  2. In a GROWTH TREND, if your first value in a series is 117, and the next value is 142; what is your GROWTH TREND Prediction? 
    Using the Growth worksheet below, type your answer in the Footer Left Section.

 

Software Skills:  When it comes to business accounting, a crystal ball that predicts the future would come in handy pretty often.  Imagine being able to predict sales so accurately you never order too many parts, carry too much inventory, or schedule too much staff.  Excel doesn't come with a crystal ball, but it does provide some nifty equivalents among them to FORECAST, TREND, and GROWTH functions.

Application Skills:  You are the owner of a Whole Grains Bread store in Olympia, Washington, and you've been looking for a way to manage inventory more effectively.  After learning about Excel's forecasting functions' you've decided to give them a try and see how good they are at predicting your future inventory needs.

52InventoryPredictions file, Right-Click here (Save Link As) to download/save...

PRINT 52InventoryPredictions Spreadsheet

  • Values Printout

  • Formulas Printout
     

  • PRINTOUTS (#1 & #2), (#3 & #4), and (#5 & #6)

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Footer Line 1

    • "Linear Trend Prediction = " your answer (Footer Left Section) - Trend Worksheet

    • Tab/Sheet Name (Footer Center Section) - All Worksheets

    • "Growth Trend Prediction = " your answer (Footer Left Section) - Growth Worksheet

  • "Pg. 272, Exercise 52" (Line 2)

  • "Values" (Line 3) - Values Printout

  • "Formulas" (Line 3) - Formulas Printout

  • Note:  Be sure you include your PREDICTION values in your Footer (Line 1)


Thursday, March 26 (A)

Progress Report Distribution and Discussion
Exercise 49 (Lesson 7:  Pg. 260 - 263)

Skills Covered

  • Create Organization Charts

  • Create SmartArt

Software Skills:  With an organization chart, you can easily show the relationship between objects or people.  For example, you could show how your department is organized.  With other conceptual charts, you could show the progress of a project--from conception to completion, areas of overlapping responsibility within a department or on a group project, or the cycle of events with a school or calendar year.

Application Skills:  In preparation for Saturday's open house at HelpNow MedCenter, you've decided to create a simple organization chart so that prospective franchisees can get a good idea of the structure of the company and where they might fit in.  Luckily, this task is easy to accomplish using Excel.

49OrgChart file, Right-Click here (Save Link As) to download/save...

PRINT 49OrgChart Spreadsheet
 

  • Do NOT open the 49OrgChart Spreadsheet data file

  • Open a BLANK Excel file

  • Wait for Instructor-led SmartArt Demonstration and Text Pane Discussion

 

  • PRINTOUT #1

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 263, Exercise 49" (Line 2)


Tuesday, March 24 (A)

Exercise 47 (Lesson 7:  Pg. 250 - 252)

Skills Covered

  • Print a Chart

  • Publish a Chart to the Internet/Intranet

Software Skills:  After creating a chart, you may want to print it out so you can share your data with others.  You can print the chart with the rest of the worksheet data, or simply print just the chart.  Another way to share your information is to publish your chart to the Internet, or to your company Intranet.  you can even make your online chart interactive, so that users can change the data in the chart as well as view it.  This is especially useful when the data for the chart comes from several different sources, such as several different departments in your company.

Application Skills:  You are the CFO of Restoration Architecture, and you've been preparing an important report for your CEO on 3rd Quarter revenues.  The most important part is the chart you've prepared, and so you're going to print it out for inclusion in the final report.

47RHSales file, Right-Click here (Save Link As) to download/save...

PRINT 47RHSales Spreadsheet and Chart
Print the Revenue worksheet and chart
Print the chart by itself (in draft quality)
Print the chart by itself (black and white option, non-draft quality)

  • PRINTOUT #1, #2, and #3

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 252, Exercise 47" (Line 2)


Exercise 48 (Lesson 7:  Pg. 254 - 258)

Skills Covered

  • Paste a Picture of a Chart

  • Link a Chart

  • Embed a Chart Into a Word Document
  • Edit a Linked or Embedded Chart

Software Skills:  You can link or embed an Excel chart into another document, such as a Word document.  If the source data is likely to change, you should link the data to its source, so that your chart will automatically update.  This is especially useful when the source data is updated by several different people in your organization.  You can also embed the chart in your Word document to ensure that your changes will not affect the original data.

Application Skills:  As the new Franchise Director, you are well aware that HealthNow MedCenter is seeking to expand its operations in nearby cities.  In anticipation for the first franchisee meeting, you have prepared a balance sheet and chart for use in a flyer that will be sent to potential investors.  Since you already have the necessary balance sheet data loaded in Excel, you'll use it to quickly link and embed that data and its chart into the flyer you've created using Word.

48Balance Sheet file, Right-Click here (Save Link As) to download/save...
48FranchiseBrochure Word file, Right-Click here (Save Link As) to download/save...

PRINT 48FranchiseBrochure Word document

  • PRINTOUT #4

  • Add a "Normal" Header

  • Use a Footer - "Pg. 258, Exercise 48"


Monday, March 13 (A)

  • Green Ribbon Presentation (Library)

  • Presentation Notes click here (Worth 1 Test Grade)

    • Don't Save File

    • Open

    • Print


Monday, March 9 (A)/Wednesday, March 11 (A)

Exercise 42 (Lesson 6:  Pg. 225 - 232)

Skills Covered

  • Change Cell Borders

  • Fill Cells with Pattern

  • Modify and Save a Theme

  • Conditional Formatting
  • Add a Watermark
  • Format Worksheet Background

Software Skills:  Each worksheet tells a story--of lost profits, increased costs, or skyrocketing sales.  To help your worksheet tell its "story," you can add shading, borders, or conditional formatting to highlight or separate important information in a complex worksheet.  To make a worksheet look more professional, you might want to customize the standard themes Excel provides by choosing company-style fonts and colors, adding a watermark, or applying a custom worksheet background.

Application Skills:  The worksheet you designed to track accessories sold each day at your PhotoTown store has proven very helpful, and the corporate headquarters may adopt it throughout the company.  Before you send it off for their review, you want to add some professional formatting touches.

42ProdSold file, Right-Click here (Save Link As) to download/save...
42phototownbackground file, Right-Click here (Save Link As) to download/save...

PRINT 42ProdSold Spreadsheet
Print BOTH Worksheets (July 22 and July 23)

  • PRINTOUT #1 and #2

  • Add a "Semi-Normal" Header - make sure your name and date is visible

  • Change to PORTRAIT Orientation

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 232, Exercise 42" (Line 2)


Exercise 43 (Lesson 6:  Pg. 234 - 239)

Skills Covered

  • Save a Workbook in a Different Format

  • Use Compatibility Checker

  • Create a New Folder for Saving Workbooks

  • Enable Macros in a Workbook
  • Create Original Templates

Software Skills:  If you share Excel data, you can easily save that data in a format that's compatible with the program someone else is using, such as an older version of Excel.  Sometimes you want to share not the data, but the format and formulas in a workbook in a reusable format called a template.  With a template, you can create as many similar workbooks as you like, with only a minimum of effort.  Using macros are also easy, since they are designed to save you time by automating repetitive tasks.

Application Skills:  Since you first started keeping records for Holy Habanero, you've learned quite a lot about using Excel, and so you feel pretty confident in using macros and creating reusable templates, so you'd like to give it a try with a produce log a colleague has developed.  You also need to convert the worksheet to Excel 2003 format, which is being used by the manager of a different restaurant location.

43HHProduceLog0214 file, Right-Click here (Save Link As) to download/save...

PRINT 43HHProduceLog0221_OF Spreadsheet (Range:  A1:J28)

  • PRINTOUT #3

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Change Left and Right Margins to 0.25"

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 239, Exercise 43" (Line 2)


POPQUIZ...
Exercise 45 (Lesson 6:  Pg. 245 - 246)

Skills Covered

Application Skills:  You are the payroll clerk at PhotoTown, and you've been calculating payroll checks manually ever since you were hired a month ago.  Now that you're familiar with Excel, however, you want to use it to complete this weekly task more easily.

Save BOTH data files before you begin working on the Exercise/PopQuiz
Wait for demonstration before printing Exercise/PopQuiz...

45PhotoTownPayroll file, Right-Click here (Save Link As) to download/save...
45PhotoTownEEs file, Right-Click here (Save Link As) to download/save...

  • PRINTOUT #4 (Full View)

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Change Left and Right Margins to 0.25"

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 245, Exercise 45" (Line 2)

  • In addition to the Full View printout, email 45PhotoTownPayroll Spreadsheet file as an attachment

    • To:  BCIS_Wiz@yahoo.com

    • Subject:  3A - 45PhotoTownPayroll

    • Be sure to send yourself a "cc" of the file


Thursday, March 5 (A)

Exercise 40 (Lesson 6:  Pg. 213 - 217)

Skills Covered

  • Hide Data Temporarily

  • Hide Worksheet Gridlines

  • Hide Row and Column Headings

  • Custom Views

Software Skills:  If you have data that's considered confidential or is needed strictly as supporting information, you can hide it from view.  This helps you keep the displayed information to just the relevant data, and prevents you from accidentally printing private data.

Application Skills:  As the bookkeeper for Intellidata Database Services, your job is to produce three versions of one Web traffic statistics report, each of which is distributed to a different office.


40UsageStats
file, Right-Click here (Save Link As) to download/save...

PRINT 40UsageStats Three (3) Spreadsheet Views ("North", "South", and "Central")

  • PRINTOUT #1, #2, and #3

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Change Left and Right Margins to 0.25"

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 217, Exercise 40" (Line 2)

  • (Line 3):  "North Offices", "South Offices", and Central Offices" one for each printout


Exercise 41 (Lesson 6:  Pg. 219 - 223)

Skills Covered

  • More Alignment Options

  • Format Dates and Times

  • Create Custom Number Formats

  • Adjust Row Height
  • Delete (Clear) a Cell's Format

Software Skills:  Sometimes, in order to accommodate the various kinds of data in a worksheet, you have to apply various formatting techniques that you might not ordinarily use, such as adjusting the row heights, merging cells, and slanting column labels.  Other refinements you may need to make include applying the proper format to data--even if that means removing formats and crating your own.

Application Skills:  As the owner of Giancarlo Franchetti's Go-Cart Speedrome, you're interested in using Excel to help you manage your growing business.  You've created a worksheet for tracking daily admissions and receipts, and you want to use your knowledge of Excel formatting to make the worksheet more attractive.

41DailyAdmissions file, Right-Click here (Save Link As) to download/save...

PRINT 41DailyAdmissions Spreadsheet

  • PRINTOUT #4

  • Add a "Normal" Header

  • Change to PORTRAIT Orientation

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 223, Exercise 41" (Line 2)


Monday, March 2 (A)

Exercise 39 (Lesson 6:  Pg. 207 - 211)

Skills Covered

  • Link Documents with Hyperlinks

  • Add a Hyperlink to a Worksheet

Software Skills:  A hyperlink can connect a worksheet to specific locations with any worksheet in any workbook, or to information on the Internet or the company intranet.  Using a hyperlink is a convenient way to provide quick access to related information.  For example, in a sales worksheet, you could provide a hyperlink to an area in the workbook (or in another workbook) that provides product costs or other revenues.

Application Skills:  You've been put in charge of tracking patient services at Wood Hills Animal Clinic.  You've put together a patient worksheet listing the various pets that have recently visited the clinic and a separate worksheet listing owner information.  Before you get too far on your project, you want to test out its usability, adding hyperlinks that connect each pet with its owner's personal data.  Finally, you want to link from a pet's name to its patient history, which is stored in a third worksheet.

Save ALL 3 data files before you begin working on the Exercise
Wait for demonstration before printing Exercise...


39Patients
file, Right-Click here (Save Link As) to download/save...
39AkemiHistory file, Right-Click here (Save Link As) to download/save...
39Owners file, Right-Click here (Save Link As) to download/save...

PRINT 39Patients Spreadsheet

  • Add a "Normal" Header

  • Change to PORTRAIT Orientation

  • Change Left and Right Margins to 0.5"

  • Change Top and Bottom Margins to 1"

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 211, Exercise 39" (Line 2)

  • Print this spreadsheet FIRST, and have instructor verify and initial your work!!!


Exercise 38 (Lesson 6:  Pg. 202 - 205)

Skills Covered

  • Covert Text to Columns

Software Skills:  Occasionally, you won't create problems in Excel, but inherit them.  For example, someone might have created a long list of important data such as client names and e-maill addresses.  Maybe you've been given the job of using this list to send out automated e-mail newsletters, coupons, and other incentives.  The problem is, certain fields that should have been kept separated (such as first and last names) are located in the same column, making it impossible to perform simple maintenance tasks such as sorting by last name.  Luckily, Excel allows you to split such cells into their component parts, and to put each part (such as the first name and last name)  into its own cell.

Application Skills:  Your boss at The Little Toy Shoppe wants you to create a newsletter to inform clients of new products and to entice them to return to the store on special sales days.  Luckily, Rob the intern has been keeping track of customer names and addresses in a new worksheet.  Unfortunately, Rob doesn't know the first thing about creating a workable database.  With the help you'll find in this lesson however, you'll be able to easily fix Rob's mistakes without resorting to reentering the data.

38Customers file, Right-Click here (Save Link As) to download/save...

PRINT 38Customers Spreadsheet

  • Add a "Normal" Header

  • Change to PORTRAIT Orientation

  • Change Left and Right Margins to 0.2"

  • Change Top and Bottom Margins to 0.5"

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally AND Vertically

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 204, Exercise 38" (Line 2)

  • Print this spreadsheet SECOND, and have instructor verify and initial your work!!!

  • Staple your work in order (Exercise 38 and then 39)


Thursday, February 26 (A)

Exercise 36 (Lesson 6:  Pg. 192 - 193)

Skills Covered

  • Use Go To

  • Use Name Box

Software Skills:  You can click any visible cell to make it active, but you can also use the Go To command and the Name box to change the active cell even more.

Application Skills:  You've been doing a great job at your new job at Intellidata, but the worksheets they use are typically very large, so you need to practice some techniques for moving around Excel quickly and easily.

  • Start Excel, and begin working on a new, blank spreadsheet...


Exercise 37 (Lesson 6:  Pg. 195 - 199)

Skills Covered

  • Enter Labels on Multiple Lines

  • Enter Fractions and Mixed Numbers

  • Smart Tags

  • Insert and Delete Selected Cells

  • Copy or Move Data with the Office Clipboard

Software Skills:  When entering labels, especially long ones, you may want to display them on more than one line so the column will not need to be as wide.  Entering fractions, on the other hand, requires a special technique so they display properly.  In order to shorten the time it takes to enter data, Excel displays smart tags that provide quick access to related commands.  After you create a worksheet, you may want to rearrange data by moving or copying it, or by adding new cells between existing data.

Application Skills:  As Junior Manager for a local PhotoTown store, you have been asked to create some way to track weekly sales for individual sales clerks.  Your plan is to start with an existing daily sales worksheet, and to use it to create a weekly sales tracker.

37WeeklyProductTracker file, Right-Click here (Save Link As) to download/save...

PRINT 37WeeklyProductTracker Spreadsheet

  • Add a "Normal" Header

  • Change to PORTRAIT Orientation

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 199, Exercise 37" (Line 2)


Tuesday, February 24 (A)
Friday, February 20 (A)

Exercise 34 (Lesson 5:  Pg. 187)
POP QUIZ Assignment (110 Points Possible)...

Application Skills:  You are the Accounting Manager at Whole Grains Bread, and you've been working on this quarter's profit and loss statement (income statement).  Preparing the statement hasn't been easy, but you're just about done.  You need to create some 3-D formulas, link to some data stored in two other workbooks, and then print the results.

34WGBP&L file, Right-Click here (Save Link As) to download/save...
34BreadSales
file, Right-Click here (Save Link As) to download/save...
34WGBInventory file, Right-Click here (Save Link As) to download/save...

Print EACH sheet of the 34WGBInventory Spreadsheet (6 sheets total)

  • Completed Workbook Printouts - 5 Points Each (30 Points Total)

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Left and Right Margins to 0.25"

  • Adjust Scaling - 100% Normal Size

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 187, Exercise 34" (Line 2)

Print the P&L Workbook of the 34WGBP&L Spreadsheet (Values)

  • Completed Profit and Loss (Values) Printout - 40Points

  • Add a "Normal" Header

  • Change to PORTRAIT Orientation

  • Adjust Left and Right Margins to 0.25"

  • Adjust Scaling - 100% Normal Size

  • Adjust Scaling - Fit to 2 Pages (1 page wide by 2 pages tall)

  • Center Horizontally

  • Change to Page Break Preview, and adjust page break to Row 29

  • 2nd Page will START with Row 30

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 187, Exercise 34" (Line 2)

  • "Values" (Line 3)

Print the P&L Workbook of the 34WGBP&L Spreadsheet (Formulas)

  • Completed Profit and Loss (Formulas) Printout - 40 Points

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Left and Right Margins to 0.25"

  • Adjust Scaling - 100% Normal Size

  • Adjust Scaling - Fit to 2 Pages (1 page wide by 2 pages tall)

  • Center Horizontally

  • Change to Page Break Preview, and adjust page break to Row 29

  • 2nd Page will START with Row 30

  • Minimize Column "C"

  • Do NOT include Column "G"

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 187, Exercise 34" (Line 2)

  • "Formulas" (Line 3)


Wednesday, February 18 (A)

Exercise 32 (Lesson 5:  Pg. 178 - 180)

Skills Covered

  • Drag-and-Drop Data Between Workbooks

  • Link Workbooks

Software Skills:  Arrange open files on the screen so you can see the worksheets as you work on them.  For example, you may want to copy or move information across worksheets using the drag-and-drop procedure.  You may want to consolidate information from several workbooks into a single summary workbook using the link feature.  With the source and destination workbooks open on the screen, you can see the linked information update as source data changes.

Application Skills:  You're an employee at Whole Grains Bread, and it's time to prepare your tax returns.  Luckily, the company accountant has been showing you how she uses Excel to make tax preparation easier.  You've entered most of the data, and all that's left are a few extra worksheets to see how much you can reduce your taxes further.  To complete the worksheets, you'll use linking to pull data from the IncomeTax workbook into the special TaxWS workbook you've created

32IncomeTax file, Right-Click here (Save Link As) to download/save...
32TaxWS
file, Right-Click here (Save Link As) to download/save...

PRINT 1040 Workbook of the 32IncomeTax Spreadsheet (Values)

  • Add a "Normal" Header

  • Change to PORTRAIT Orientation

  • Adjust Left and Right Margins to 0.25"

  • Adjust Scaling - 100% Normal Size

  • Adjust Scaling - Fit to 2 Pages

  • Center Horizontally

  • Change to Page Break Preview, and adjust page break to Row 40

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 180, Exercise 32" (Line 2)

  • "Values" (Line 3)
     

PRINT 1040 Workbook of the 32IncomeTax Spreadsheet (Formulas)

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Left and Right Margins to 0.25"

  • Adjust Scaling - 100% Normal Size

  • Adjust Scaling - Fit to 2 Pages

  • Center Horizontally

  • Change to Page Break Preview, and adjust page break to Row 40

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 180, Exercise 32" (Line 2)

  • "Formulas" (Line 3)


Exercise 33 (Lesson 5:  Pg. 183 - 185)

Skills Covered

  • 3-D Formulas

  • Duplicate Workbook Window

Software Skills:  Write a 3-D formula to reference values in the same cells, across multiple worksheets.  For example, you may want to total data from several worksheets into a summary worksheet.  When you need to look at more than one of the worksheets in a workbook, create a duplicate workbook window.

Application Skills:  As Sales Director at Whole Grains Bread, you designed a workbook to track three months' worth of sales at each of your various locations.  It's almost complete, but you need to add some 3-D formulas to summarize the monthly totals into a final set of totals for the quarter.

33BreadSales file, Right-Click here (Save Link As) to download/save...

PRINT Qtr 3 Workbook of the 33BreadSales Spreadsheet (Values)

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Left and Right Margins to 0.25"

  • Adjust Scaling - 100% Normal Size

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 185, Exercise 33" (Line 2)

  • "Values" (Line 3)
     

PRINT Qtr 3 Workbook of the 33BreadSales Spreadsheet (Formulas)

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Left and Right Margins to 0.25"

  • Adjust Scaling - 100% Normal Size

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally

  • Use a Footer which includes the Tab/Sheet Name (Line 1)

  • "Pg. 185, Exercise 33" (Line 2)

  • "Formulas" (Line 3)


NOTE:  Quiz on Friday, February 20, 2009

Skills Covered on Monday and today will be included!!!


Monday, February 16 (A)

Exercise 29 (Lesson 5:  Pg. 165 - 167)

Skills Covered

  • Copy and Paste Special

  • Transpose Data

Software Skills:  You can control how to paste data after you copy it to the Clipboard.  For example, you may copy cells that contain formulas to the Clipboard but only want to paste the results.  Use the time-saving Copy and Paste Special commands for this type of editing.

Application Skills:  As the CFO of Restoration Architecture, you need to create a separate revenue analysis report using the data in the 3rd Quarter Revenue report that you prepared last week.  In this exercise, you'll use Paste Special to get the job done quickly.

29RHSales file, Right-Click here (Save Link As) to download/save...

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Adjust Left and Right Margins to 0.2"

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally

  • Use a Footer which includes the Tab/Sheet Name (Line 1), and

  • "Pg. 167, Exercise 29" (Line 2)


Exercise 30 (Lesson 5:  Pg. 169 - 170)

Skills Covered

  • Combine Data with Copy and Paste Special

Software Skills:  Use the Copy and Paste Special commands when you need to copy and combine data.  For example, you may have individual worksheets containing the items sold each month and a summary sheet showing the total inventory.  Use the Paste Special command, Subtract operation to reduce the inventory on the summary sheet by the number of items sold each month.

Application Skills:  Your monthly inventory is complete at the Voyager Travel Adventures retail store in Logan, Colorado, and it's time to compute the monthly sales and sales revenue.  Unfortunately, the inventory tracker is located in one workbook, and the monthly sales revenue recap is in another.  No problem for you, however, since you just learned how to use the Paste Special function to subtract the monthly sales figures from the ending inventory so you can compute the number of each item sold during the month.

30Inventory file, Right-Click here (Save Link As) to download/save...
30VTAMonthlySales file, Right-Click here (Save Link As) to download/save...

  • Print all Three (3) Worksheets

    • Snowboarding and Heliskiing worksheet (LANDSCAPE Orientation)

    • Backcountry worksheet (PORTRAIT Orientation)

    • Camping worksheet (PORTRAIT Orientation)

  • Add a "Normal" Header

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally

  • Use a Footer which includes the Tab/Sheet Name (Line 1), and

  • "Pg. 170, Exercise 30" (Line 2)


Exercise 31 (Lesson 5:  Pg. 173 - 175)

Skills Covered

  • Freeze Labels While Scrolling

  • Split a Worksheet into Panes

Software Skills:  When working with a large worksheet, you can freeze row and/or column labels to keep them in view and split the worksheet window into two or four panes.  Freezing labels enables you to quickly identify a piece of data embedded within a large worksheet, while splitting a window into panes enables you to view multiple parts of a worksheet at the same time--in order to compare or copy data, for example.

Application Skills:  It's your job at the Whole Grain Breads' home office to complete the payroll for this week.  The worksheet you use is getting quite large, and you've decided to utilize the Freeze Panes and Split commands to help you view its information as you work.

31Payroll file, Right-Click here (Save Link As) to download/save...

  • Print Two (2) Spreadsheet Printouts

    1. Spreadsheet with Values

    2. Spreadsheet with Formulas

  • Add a "Normal" Header

  • Change to LANDSCAPE Orientation

  • Change Left and Right Margins to 0.25"

  • Adjust Scaling - Fit to 1 Page

  • Center Horizontally

  • Use a Footer which includes the Tab/Sheet Name (Line 1), and

  • "Pg. 175, Exercise 31" (Line 2)

  • "Values" and "Formulas" (Line 3)


Tuesday, February 10 (A)
Thursday, February 12 (A)

4th SIX-WEEKS EXAM

PART I (Pg. 144) - Worth 30 Points Total

Bar Chart - Worth 10 Points
Column Chart - Worth 10 Points
Area Chart - Worth 10 Points


NOTE:  EACH error will count 10 points!!!

24CDMania file, Right-Click here (Save Link As) to download/save...

Add a Chart Title to both charts moved to the Quarter 1 Sales Charts sheet
Edit the Chart Title "Bar Chart" for the bar chart
Edit the Chart Title "Column Chart" for the column chart
Add a Chart Title to the chart in the Average Sales Chart sheet
Edit the Chart Title "Area Chart" for the area chart
Print EACH graph separately (Landscape Orientation)
     Center  EACH graph Vertically and Horizontally
Do NOT include Gridlines and Row/Column Headings!!!
Include a "Normal" Header
Include the Tab/Sheet Name in the Footer Center Section (Line 1)
Include the Chart Title of each chart (in quotes) in the Footer Center Section (Line 2)
Include "Part I:  Pg. 144" in the Footer Center Section (Line 3)


PART II (Pg. 145, Exercise 25) - Worth 40 Points Total

Column chart printout - Worth 10 Points
Spreadsheet printout with Values - Worth 15 Points
Spreadsheet printout with Formulas - Worth 15 Points

NOTE:  EACH error will count 10 points!!!

25RHSales file, Right-Click here (Save Link As) to download/save...
sander file, Right-Click here (Save Link As) to download/save...

Include Gridlines & Row/Column Headings for the spreadsheet printouts
Include a "Normal" Header
Print spreadsheet with values (Landscape Orientation)
Print spreadsheet with formulas (Landscape Orientation)
Include the Tab/Sheet Name in Footer Center Section (Line 1)
          "Part II:  Pg. 145, Ex. 25 (Column Chart)" for the Chart Printout - (Line 2)
          "Part II:  Pg. 145, Ex. 25 (Values)" for the Values Printout - (Line 2)
          "Part II:  Pg. 145, Ex. 25 (Formulas)" for the Formulas Printout - (Line 2)


PART III (Pg. 158) - Worth 10 Points Total

Spreadsheet printout - Worth 10 Points

NOTE:  EACH error will count 10 points!!!

27ColExp file, Right-Click here (Save Link As) to download/save...

Follow ALL Header/Footer instructions
Include Gridlines & Row/Column Headings
Step 12 Print 1 copy of the worksheet - ONLY
Include the Tab/Sheet Name in Footer Center Section (Line 1)
            "Part III:  Pg. 158" in the Footer Center Section (Line 2)


PART IV (Pg. 164) - Worth 20 Points Total

Spreadsheet printout 1 (step 7) - Worth 10 Points
Spreadsheet printout 2 (step 10) - Worth 10 Points

NOTE:  EACH error will count 10 points!!!

28CDMania file, Right-Click here (Save Link As) to download/save...

Include a "Normal" Header
Include Gridlines & Row/Column Headings
Print all spreadsheets in Landscape Orientation
Step 10 & 11
Print 1 copy of the worksheet - ONLY (ignore Step 11)
Include the Tab/Sheet Name in Footer Center Section (Line 1)
            "Part IV:  Pg. 164" in the Footer Center Section (Line 2)


Friday, February 6 (A)

Exercise 27 (Lesson 5:  Pg. 150 - 156)

Skills Covered

  • Print a Workbook

  • Print Multiple Copies

  • Modify Page Setup

  • Insert Headers and Footers

  • Page Layout View

Software Skills:  Change the page setup of your worksheet and use the available print options to control the printed output of a report.  For example, if you need to fit the worksheet on one page, you can change the margins, change the print orientation, change the paper size, and change the scaling.  Add headers and footers to your report to repeat the same information at the top or bottom of each printed page.

Application Skills:  You are an Adventure Coordinator for Voyager Travel Adventures, and you have prepared a cost estimate for the Tell City Thrill Seekers Club for their upcoming trip that combines whitewater rafting, backcountry hiking, rock climbing, and all-terrain skating.  Now you need to print the estimate, but you want to change the setup so it will print the way you want.

27TripBudget file, Right-Click here (Save Link As) to download/save...
voyager travel logo file, Right-Click here (Save Link As) to download/save...

Follow all Header/Footer instructions
Use a Footer which includes the Tab/Sheet Name, and
"Pg. 156, Exercise 27"


Exercise 28 (Lesson 5:  Pg. 159 - 162)

Skills Covered

  • Insert Page Breaks

  • Page Break Preview

  • Set Print Area

  • Repeat Row and Column Labels

  • Other Sheet Tab Options

Software Skills:  If you are not satisfied with the page layout defaults set in Excel, you can change them manually.  For example, if a worksheet doesn't fit on one page, then Excel sets page breaks automatically for you.  These page breaks tell Excel where to start a new page when printing.  If you don't like where the automatic page breaks occur, you can manually set your own page breaks before printing.  If you need to print only part of a worksheet for a specific report, you can temporarily change the print area.  When your printout includes multiple pages, you can reprint the row and column labels for your data on every page, making it easier for you and others to locate data.  Other sheet data options may speed up the printing process or display data in a manner that makes it easier to read.

Application Skills:  As the new Franchise Director at HealthNow MedCenter, you've spent a lot of time creating reports that describe your company's fiscal strength.  The balance sheet is ready for printing, but you want to modify the print settings first so that it will print exactly as you want.

28Balance Sheet file, Right-Click here (Save Link As) to download/save...
bread file, Right-Click here (Save Link As) to download/save...

Include a "Normal" Header
Use a Footer which includes the Tab/Sheet Name, and
"Pg. 162, Exercise 28"


Wednesday, February 4 (A)

Exercise 23 (Lesson 4:  Pg. 129 - 134)

Skills Covered

  • Chart Basics

  • Select Chart Data

  • Chart Elements

  • Create Charts

  • Change Chart Types

  • Select a Chart

  • Resize, Copy, Move, or Delete a Chart

Software Skills:  A chart presents Excel data in a graphical format--making the relationship between data items easier to understand.  To present your data in the best format, you must select the proper chart type.  For example, if you want to highlight your department's recent reduction in overtime, you might use a column or bar chart.  Whereas, to compare you division's sales with other divisions, you might use a pie chart instead.

Application Skills:  The modifications you made to your daily bread sales worksheet are working out very well.  Now, as manager of a Whole Grains Bread store, you're ready to take it to the next level:  charts.  Being able to visually compare the sales of the various items in your retail store will help you to make the modifications you need to make to maximize profits, so in this exercise, you'll add two charts.

23DailyBreadSales file, Right-Click here (Save Link As) to download/save...

PRINT 3-D Pie Chart (Worth 1 Separate Daily Grade)
Do NOT include Gridlines or Row & Column Headings
Use LANDSCAPE Orientation
Include a "Normal" Header
Use a Footer which includes the Tab/Sheet Name, and
"Pg. 134, Exercise 23:  3-D Pie Chart"

PRINT Clustered Column Chart (Worth 1 Separate Daily Grade)
Do NOT include spreadsheet (Select and Print ONLY the chart)
Do NOT include Gridlines or Row & Column Headings
Use LANDSCAPE Orientation
Include a "Normal" Header
Use a Footer which includes the Tab/Sheet Name, and
"Pg. 134, Exercise 23:  Clustered Column Chart"


Exercise 24 (Lesson 4:  Pg. 136 - 142)

Skills Covered

  • Change Data Series Orientation

  • Format a Chart

  • Resize, Move, or Delete a Chart Object

  • Change Chart Text

  • Enhance Chart Background

  • Format Category and Value Axes

Software Skills:  There are many ways in which you can enhance your chart:  you can format the chart text, add color or pattern to the chart background, and format the value and category axes so that the numbers are easier to read.

Application Skills:  The charts you created to analyze daily bread sales in the retail portion of your Whole Grains Bread store are almost completed.  Before printing, you want to format them to make them more professional looking and easier to understand.

24DailyBreadSales file, Right-Click here (Save Link As) to download/save...
bread file, Right-Click here (Save Link As) to download/save...

PRINT embedded Chart (Worth 1 Separate Daily Grade)
Do NOT include Gridlines or Row & Column Headings
Use LANDSCAPE Orientation
Include a "Normal" Header
Use a Footer which includes the Tab/Sheet Name, and
"Pg. 142, Exercise 24:  Embedded Chart"

PRINT 3-D Pie Chart (Worth 1 Separate Daily Grade)
Do NOT include Gridlines or Row & Column Headings
Use LANDSCAPE Orientation
Include a "Normal" Header
Use a Footer which includes the Tab/Sheet Name, and
"Pg. 142, Exercise 24:  3-D Pie Chart"


Monday, February 2 (A)

Exercise 22 (Lesson 4:  Pg. 122 - 126)

Skills Covered

  • Understand IF Functions

  • Nested IF Functions

  • =SUMIF() and SUMIFS() Functions

  • =COUNTIF() and COUNTIFS() Functions

Software Skills:  IF functions allow you to test for particular conditions and then perform specific actions based on whether those conditions exist or not.  For example with an IF function, you could calculate the bonuses for a group of salespeople on the premise that bonuses are only paid if a sale is over $1,000.  With the SUMIF function, you could total up the sales in your Atlanta office, even if those sales figures are scattered through a long lit of sales figures.  And with the COUNTIF function, you could count the number of sales that resulted in a bonus being paid.

Application Skills:  You're the Manager of a Whole Grains Bread store in Olympia, Washington, and you're creating a new worksheet for tracking retail bread sales.  You've just learned about various IF functions, and, along with some other new functions you've discovered, you know you can refine the worksheet so that it's simple for your employees to use.  With the sales analysis the worksheet will provide, you can refine the retail end of your business to maximize profits.

22DailyBreadSales file, Right-Click here (Save Link As) to download/save...
Once you enter ALL the formulas,
verify your results against the completed spreadsheet in Illustration A

PRINT SPREADSHEET with values
PRINT SPREADSHEET with formulas
Change to LANDSCAPE Orientation
Change Left and Right Margin to 0.25", and Bottom Margin to 0.5"
"Fit to 1 Page" to make sure spreadsheet fits on one page
with "Normal" Header
with Footer (Pg. 126, Exercise 22)
with Gridlines and Row and Column Headings


Thursday, January 29 (A)

  • Grades Update...

  • Review Missing Assignments, and Make-up Missing Assignments...


Tuesday, January 27 (A)

Exercise 20 (Lesson 4:  Pg. 112 - 115)

Skills Covered

  • Create and Use Excel Tables

  • Use Comments

Software Skills:  Format a range as an Excel table to enable structured references and add comments to your worksheet to help you read the formulas or remember why you included certain information in your worksheet.

Application Skills:  You are the CFO (Chief Financial Officer) of Restoration Architecture, and it's time for the quarterly revenue recap.  This time, however, you want to make some modifications that will make the formulas easier to follow.  This will help you in future quarters, when you use this workbook as a template for new workbooks.

20RHSales file, Right-Click here (Save Link As) to download/save...
Once you enter ALL the formulas,
verify your results against the completed spreadsheet in Illustration A

PRINT SPREADSHEET with values
PRINT SPREADSHEET with formulas
Change to LANDSCAPE Orientation
Change Left and Right Margin to 0.25", and Bottom Margin to 0.5"
"Fit to 1 Page" to make sure spreadsheet fits on one page
with "Normal" Header
with Footer (Pg. 115, Exercise 20)
with Gridlines and Row and Column Headings


Exercise 21 (Lesson 4:  Pg. 117 - 120)

Skills Covered

  • Named Ranged

Software Skills:  At times it may be easier to reference a cell or range of cells with a descriptive name.  For example, a range name can make the formulas in your worksheet easier to understand, and the formatting and printing easier to accomplish.

Application Skills:  As the owner of Restoration Architecture, you're naturally very busy.  So when you learned about range names and how they can make certain tasks easier to understand and complete, you were eager to add them to your worksheets as soon as possible.  In this exercise, you'll modify a recent bid analysis worksheet so you can try out range names.

21BidAnalysis file, Right-Click here (Save Link As) to download/save...
Once you enter ALL the formulas,
verify your results against the completed spreadsheet in Illustration A & B

PRINT BOTH SPREADSHEET Ranges with values before closing
PRINT BOTH SPREADSHEET Ranges with formulas before closing
Add A46:G49 to the Bid Analysis Print Area
with "Normal" Header
with Footer (Pg. 120, Exercise 21) - line 1
with Footer ("Bid Results") - line 2 on 1st spreadsheet range printout
with Footer ("Bid Analysis") - line 2 on 2nd spreadsheet range printout
with Gridlines and Row and Column Headings


Friday, January 23 (A)

  • Review Progress Report

  • Make-up Work


Wednesday, January 21 (A)

Exercise 17 (Lesson 3:  Pg. 98)

Application Skills:  As Sales Director at Whole Grains Bread, it's your job to monitor sales throughout the company.  Today you're designing a workbook to track three month's worth of sales at each of your various locations.

17BreadSales file, Right-Click here (Save Link As) to download/save...

Click on Qtr 3 Tab, and PRINT SPREADSHEET with values
PRINT SPREADSHEET with formulas
Change to LANDSCAPE Orientation
Change Left and Right Margin to 0.25", and Bottom Margin to 0.5"
"Fit to 1 Page" to make sure spreadsheet fits on one page
with "Normal" Header
with Footer (Pg. 98, Exercise 17) - line 1
with Footer (sheet name) - line 2
with Gridlines and Row and Column Headings


Exercise 19 (Lesson 4:  Pg. 104 – 109)

Skills Covered

  • Use Functions

  • Common Functions

  • Insert a Function

  • AutoCalculate

Software Skills:  Use an Excel function to help you write a formula to perform specific calculations in your worksheet.  Excel's Insert Function feature provides a list of available functions with a wizard to assist you in "filling i the blanks" to complete a formula.

Application Skills:  You're the owner of Restoration Architecture, a large design and construction firm specializing in the remodeling, redesign, and restoration of existing properties.  The bid results are in for a project involving the restoration of several homes in an historic downtown neighborhood.  The homes were recently purchased by the Save Downtown Phoenix Committee, who asked for the bids.  Unfortunately your company didn't get the bid, so you want to analyze the results and determine how far off the mark you were.

Pop Quiz Grade
19BidAnalysis
file, Right-Click here (Save Link As) to download/save...
Once you enter ALL the formulas, enter the following Regular and Overtime Hours
Verify your results against the completed spreadsheet in Illustration A & B

PRINT BOTH SPREADSHEET Ranges with values before closing
PRINT BOTH SPREADSHEET Ranges with formulas before closing
with "Normal" Header
with Footer (Pg. 109, Exercise 19) - line 1
with Footer ("Bid Results") - line 2 on 1st spreadsheet range printout
with Footer ("Bid Analysis") - line 2 on 2nd spreadsheet range printout
with Gridlines and Row and Column Headings


Monday, January 19 (A)

Exercise 15 (Lesson 3:  Pg. 88 – 91)

Skills Covered

  • Insert and Delete Columns and Rows

  • Move Data (Cut/Paste)

  • Drag-and-Drop Editing

Software Skills:  After you create a worksheet, you may want to rearrange data or add additional information.  For example, you may need to insert additional rows to a section of your worksheet because new employees have joined a department.  With Excel's editing features, you can easily add, delete, and rearrange entire rows and columns.  You can also move or drag and drop sections of the worksheet with ease.

Application Skills:  You are the Payroll Manager at Whole Grains Bread.  The conversion to an in-house payroll system is next week, and you want to test a payroll worksheet the staff will use to collect and enter payroll data into the computer system.  You're about ready to test out the worksheet using the home office data, but you need to modify it slightly first.

15Payroll file, Right-Click here (Save Link As) to download/save...
Be sure your copy the formulas in H12:K12 to I25:L25
Once you enter ALL the formulas, enter the following Regular and Overtime Hours
Verify your results against the completed spreadsheet in Illustration A

Employee
Name
Regular
Hours
Overtime
Hours
  Thomas Cortese 10    0    
  Javier Cortez 20    0    
  Rocio Cortez 30    0    
  Allen Gaines 40    0    
  Freda Gage 40    0    
  Vickie Helms 40    0    
  Isiah Herron 40    0    
  Thomas Kaminski 40    0    
  Jalaine Kane 40    0    
  Sami Kafawy 40    0    
  Akihiko Nakamura 40    5    
  Chris Nakao 40    10    

PRINT SPREADSHEETS with values before closing
PRINT SPREADSHEETS with formulas before closing
with "Normal" Header
with Footer (Pg. 91, Exercise 16)
with Gridlines and Row and Column Headings


Exercise 16 (Lesson 3:  Pg. 93 – 96)

Skills Covered

  • Insert, Delete, Copy, Move, and Rename Worksheets

  • Change the Color of a Worksheet Tab

  • Hide Sheets

  • Group Sheets

Software Skills:  Use workbook sheets to organize your reports.  For example, instead of entering the data for an entire year on one worksheet, use multiple worksheets to represent each month's data.  Excel gives you the freedom to add, delete, move, and even rename your worksheets so you can keep a complex workbook organized.  In addition, you can group multiple sheets and work on them simultaneously and quickly format an entire worksheet in one step.

Application Skills:  As the Manager of Spa Services at the Michigan Avenue Athletic Club, you were just not satisfied with the spa invoicing worksheet you created earlier.  After using it for awhile, you've reworked it a bit and now it seems easier to use, so now you're ready to make copies of it for tracking each day's services.

16SpaServices file, Right-Click here (Save Link As) to download/save...
Verify your formatting results against the completed spreadsheet in Illustration A


Click on Friday's Tab, and PRINT SPREADSHEET
with "Normal" Header
with Footer (Pg. 96, Exercise 16) - line 1
with Footer (sheet name) - line 2
with Gridlines and Row and Column Headings


Exercise 17 (Lesson 3:  Pg. 98)

Application Skills:  As Sales Director at Whole Grains Bread, it's your job to monitor sales throughout the company.  Today you're designing a workbook to track three month's worth of sales at each of your various locations.

17BreadSales file, Right-Click here (Save Link As) to download/save...

Click on Qtr 3 Tab, and PRINT SPREADSHEET with values
PRINT SPREADSHEET with formulas
Change to LANDSCAPE Orientation
Change Left and Right Margin to 0.25", and Bottom Margin to 0.5"
"Fit to 1 Page" to make sure spreadsheet fits on one page
with "Normal" Header
with Footer (Pg. 98, Exercise 17) - line 1
with Footer (sheet name) - line 2
with Gridlines and Row and Column Headings


Thursday, January 15 (A)

Exercise 12 (Lesson 3:  Pg. 72 – 74)

Skills Covered

  • Select Ranges

  • Range Entry Using Collapse Button

Software Skills:  Select a group of cells (a range) to copy, move, or erase them in one step, or to quickly apply the same formatting throughout the range.  You can also perform calculations on cell ranges--creating sums and averages, for example.

Application Skills:  You're the Inventory Manager of the Voyager Travel Adventure retail store, and it's time to organize the monthly inventory.  To help you and your crew take inventory, you've created a new inventory workbook.  You have some adjustments to make before inventory day tomorrow, but they are only minor ones so you should have the workbook ready to go by the end of the day.

12Inventory file, Right-Click here (Save Link As) to download/save...

PRINT SPREADSHEET with values before closing
with "Normal" Header
with Footer (Pg. 74, Exercise 12)
with Gridlines and Row and Column Headings


Exercise 13 (Lesson 3:  Pg. 76 – 81)

Skills Covered

  • Choose a Theme

  • Apply Cell Styles (Quick Styles)

  • Apply Font Formats

  • Apply Number Format

Software Skills:  When you change the appearance of worksheet data by applying various formats, you also make that data more attractive and readable.

Application Skills:  The inventory worksheet is almost completed, but as the Inventory Manager of the Voyager Travel Adventures retail store, you expect more from yourself.  Since you have the time before inventory day tomorrow, you want to spruce up the worksheet prior to printing by adding some formatting.

13Inventory file, Right-Click here (Save Link As) to download/save...

Wait for Printing Instructions...


Exercise 14 (Lesson 3:  Pg. 83 – 86)

Skills Covered

  • Copy and Paste Data

  • Copy Formats

  • Relative Reference

  • Absolute Reference

  • Preview and Print a Worksheet

Software Skills:  Excel provides many shortcuts to save you time as you enter data and write formulas in your worksheets.  For example, you can use the copy and paste features to reuse data and formulas in the same worksheet, in another worksheet, or in another workbook.  The AutoFill handle bypasses the copy and paste features and allows you to copy data to adjacent cells quickly and easily.  After copying data and completing a report, you can preview and print a hard copy.

Application Skills:  As an Adventure Coordinator for Voyager Travel Adventures, it's your job to make all the arrangements needed to create a unique and thrilling adventure vacation for your clients.  Today, the Tell City Thrill Seekers Club has asked for an estimate of expenses per person for a special trip that combines white water rafting, backcountry hiking, rock climbing, and all-terrain skating.  You've completed a budget for them, which can be adjusted easily as more of their club members sign up for the trip.  You've also created a profit analysis for the company, computing the total profit for the booking (upon which your commission is based).  To complete the two worksheets, you need to create some formulas and copy them.

14TripBudget file, Right-Click here (Save Link As) to download/save...

PRINT BOTH SPREADSHEETS with values before closing
PRINT BOTH SPREADSHEETS with formulas before closing
with "Normal" Header
with Footer (Pg. 86, Exercise 14) - line 1
with Footer (sheet name) - line 2
with Gridlines and Row and Column Headings


Tuesday, January 13 (A)

Exercise 9 (Lesson 2:  Pg. 63 – 66)

Skills Covered

  • Enter a formula

  • Edit a formula

Software Skills:  One of the benefits of Excel is its ability to create formulas within a worksheet that perform calculations.  When you make a change to a cell that is referenced in a formula, Excel performs the recalculation, and the formula result is automatically updated to reflect the change.

Application Skills:  HelpNow MedCenter is seeking to expand its operations in nearby cities, and a balance sheet must be prepared for prospective franchisees.  Most of the information has already been compiled by accounting, but there are some calculations you need to add to complete it.  As the new Franchise Director, you also have some ratio comparisons you want to add to attract potential investors.

09Balance Sheet file, Right-Click here (Save Link As) to download/save...

PRINT SPREADSHEET with values before closing
PRINT SPREADSHEET with formulas before closing
with "Normal" Header
with Gridlines and Row and Column Headings


Exercise 10 (Lesson 2:  Pg. 67 – 68)

Application Skills:  As the Manager of Spa Services at the Michigan Avenue Athletic Club, you’ve decided to create a worksheet to be used to track the clients using the spa so that they can be properly billed.  After creating the worksheet, you’ll enter the last of today’s clients on the Invoice Worksheet you prepare each day for the Accounting Department.

10InvoiceWS file, Right-Click here (Save Link As) to download/save...

PRINT BOTH SPREADSHEETS with values before closing
PRINT BOTH SPREADSHEETS with formulas before closing
with "Normal" Header
with "Normal" Footer (Pg. 67, Exercise 10)
with Gridlines and Row and Column Headings


Exercise 11 (Lesson 2:  Pg. 69 – 70)

Application Skills:  Your social studies class is learning about the stock market.  Each student will be given $20,000 in pretend money to invest in up to five stocks.  You’ll make your selection from a list of stocks provided for you.  Each week on Monday, you’ll update your worksheet with each stock’s current value.  At the end of three months, each student’s portfolio will be compared, and the one with the highest current value will win.  In this exercise, you’ll create a worksheet to track each stock you select and its current value.

11StockChoices file, Right-Click here (Save Link As) to download/save...

Format all "money" fields as Currency
PRINT SPREADSHEET with values before closing (Landscape Orientation)
PRINT SPREADSHEET with formulas before closing (Landscape Orientation)
with "Normal" Header
with "Normal" Footer (Pg. 69, Exercise 11)
with Gridlines and Row and Column Headings


Friday, January 9 (A)

Exercise 6 (Lesson 2:  Pg. 41 – 47)

Skills Covered

  • Create a New (Blank) Workbook

  • Create a Workbook from a Template or Another Workbook

  • Enter Labels

  • Make Simple Corrections

  • Undo and Redo

  • Delete (Clear) Cell Contents

  • Save a Workbook

  • Close a Workbook

Software Skills:  To create your first worksheet, you begin by entering the column and row titles (the labels that appear to the right of each row of data and above each data column).  Next, you enter the numeric data, using the row and column labels to help you enter each amount into the correct cell.  As you enter data, you may make a few mistakes and need to correct them.  Finally, you save your workbook before exiting Excel so that the entries are not lost.

Application Skills:  In this exercise, you are the Accounts Receivable Supervisor at the Michigan Avenue Athletic Club, and a member has changed several services and has not paid for them.  You need to create an invoice detailing the charges, with a special reminder that the balance must be paid soon or club privileges will be suspended.  You’ll begin the invoice and complete it in a later exercise.

06Sales Invoice template file, Right-Click here (Save Link As) to download/save...
Copy template file downloaded to C:\...\AppData\Roaming\Microsoft\Templates

  • Enter your name in B29

  • Enter class period in B30

PRINT SPREADSHEET before closing WITHOUT a "Normal" Header
and WITHOUT Gridlines and Row and Column Headings


Exercise 7 (Lesson 2:  Pg. 48 – 54)

Skills Covered

  • Open Workbooks

  • Change from Workbook to Workbook

  • Compare Workbooks

  • AutoComplete

  • Pick From List

  • AutoCorrect

  • Spell Check

Software Skills:  After you have saved and closed a workbook, you can open it to view it again or make changes.  After opening several workbooks, you may want to arrange them on-screen so you can view their contents at the same time.  When entering data, take advantage of the many time-saving features Excel offers.  Excel’s AutoComplete feature, for example, automatically completes certain entries based on your previous entries.  AutoCorrect automatically corrects common spelling errors as you type, while the spelling checker proofs your worksheet for any additional errors.

Application Skills:  You’re the Head Chef at Whole Grains Bread, and you need to complete the baking schedule for tomorrow.  You want to compare today’s schedule with yesterday’s, in order to compile a list of any items that were not completed on time.  Those items will be given the highest priority.

07Bakery Schedule 7-21 file, Right-Click here (Save Link As) to download/save...
07Bakery Schedule 7-20 file, Right-Click here (Save Link As) to download/save...

PRINT 07Bakery Schedule 7-21 SPREADSHEET before closing
with "Normal" Header
with Gridlines and Row and Column Headings


Exercise 8 (Lesson 2:  Pg. 55 – 62)

Skills Covered

  • Enter Numeric labels and Values

  • Enter Dates

  • Create a Series

  • Change Data Alignment

  • Change Column Width

Software Skills:  Because Excel allows you to work with numbers so easily, numeric values are one of the most common Excel types.  After typing numbers and labels into a worksheet, you can improve its appearance by changing the alignment of data and widths of columns.  If entering a series of labels (such as Monday, Tuesday, Wednesday) or values (such as 1, 2, 3), use Excel’s AutoFill feature to save data-entry time and reduce errors.

Application Skills:  You are the Accounts Receivable Supervisor at the Michigan Avenue Athletic Club, and a member has charged several services and has not paid them.  You started to create an invoice detailing the charges, but you needed to learn a bit more about Excel before you were ready to continue.  In the meantime, your boss has customized the worksheet and asked you to complete it.  In this exercise, you’ll enter in quantity and price amounts, and on another worksheet you will enter member information for tracking the overdue amount.

08MAAC Invoice file, Right-Click here (Save Link As) to download/save...

PRINT BOTH SPREADSHEETS before closing
with "Normal" Header (including tab name)


Wednesday, January 7 (A)
 

Exercise 1 (Lesson 1:  Pg. 2 – 10)

Skills Covered

  • About Business Technology

  • About Microsoft Office 2007

  • Use the Mouse

  • Use the Keyboard

  • Start Windows Program

  • Conventions used in This Book

Software Skills:  Microsoft, Office 2007 contains a suite of programs that may be used independently or together to create letters, memos, sales presentations, and budget analyses.  Microsoft Excel, 2007 is one of the main programs in the Microsoft Office 2007 suite.

Application Skills:  You have just been hired as the assistant to the president of Restoration Architecture, a firm that specializes in remodeling, redesign, and restoration of existing properties.  He has asked you to become familiar with Microsoft Office Excel 2007, since the company uses it throughout its business operations.  In this exercise, you’ll start at the beginning, learning how to use a computer and the basic skills that apply to all Windows programs.


Exercise 2 (Lesson 1:  Pg. 11 – 19)

Skills Covered

  • About Commands

  • About the Ribbon

  • Use the Ribbon

  • Use Access Keys

  • Use the Office Button

  • Use the Quick Access Toolbar

  • Use a Mini Toolbar

  • Use Shortcut menus

  • Use Task Panes

Software Skills:  To accomplish a task in any program, you must execute a command.  You select the commands using buttons, menus, and in the case of Office 2007 programs, the Ribbon and the Office Button menu.  Once you learn to use these tools, you will be able to access the features you need to create documents in any Microsoft Office 2007 program, such as Excel 2007.

Application Skills:  To get up to speed using Excel 2007, you want to spend more time exploring the Office 2007 user interface so that you know how to locate and use commands when you need them.  In this exercise, you will practice using the Ribbon, the Office Button, and toolbars, selecting menu commands, and choosing options in dialog boxes.  The techniques you learn here can also be applied to any Office 2007 program you may use.


Exercise 3 (Lesson 1:  Pg. 20 – 26)

Skills Covered

  • Use Window Controls

  • Zoom

  • Scroll

  • Use Multiple Windows

Software Skills:  Controlling the way Microsoft Office 2007 programs and documents are displayed on your computer monitor is a vital part of using the programs successfully.  For example, you can control the size and position of the program window on-screen, and you can control the size a document is displayed.  In addition, you can open more than one window on-screen at the same time, so that you can work with multiple documents and even multiple programs at once.

Application Skills:  As you spend more time working with Microsoft Office 2007 programs, you’ll find that there are many tools that help you do your job more efficiently.  In this exercise, you will learn how to maximize, minimize, and restore windows on your screen, and you will experiment with the zoom level.  You’ll also practice scrolling through a document and opening more than one window at the same time.


Exercise 4 (Lesson 1:  Pg. 27 – 32)

Skills Covered

  • Use the Help Program

  • Search for Help

  • Use the Help Table of Contents

  • Recover a File

Software Skills:  Excel comes with Help information that you can access and display in a window while you work.  You can search for help on a particular topic, or browse through the Help Table of Contents to find the information you need.  The Help program is linked to the Microsoft Office Web site, making it easy to locate and access the most current information available.

Application Skills:  As a new employee at Restoration Architecture, it’s important to learn how to solve programs on your own.  In this exercise, you will learn how to use the Help system to answer the questions you may have while working with Excel.