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 |

Tuesday, February 02 (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


ON YOUR OWN (Pg. 116)
Create a new file in Excel, and save file as OXL20
Step 19: 
PRINT the spreadsheet - with values
Insert the (sheet name) in the Custom Footer Center Section
On the 2nd line of the Custom Footer Center Section key "Values"
Be sure printout "fits" on one page.  To make this possible, you MIGHT have to:
  1. Change to Landscape Orientation
  2. Change Left and Right Margins
  3. Change Bottom Margin
  4. Change the Scaling:  Fit to 1 page

PRINT the spreadsheet - with formulas
Insert the (sheet name) in the Custom Footer Center Section
On the 2nd line of the Custom Footer Center Section key "Formulas"
Be sure printout "fits" on one page.  To make this possible, you MIGHT have to:
  1. Change to Landscape Orientation
  2. Change Left and Right Margins
  3. Change Bottom Margin
  4. Change the Scaling:  Fit to 1 page

Friday, January 29 (A)

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 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 25 (A)
Wednesday, January 27 (A)

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) - values or formulas:  line 1
with Footer (sheet name):  line 2
with Gridlines and Row and Column Headings


QUIZ GRADE
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) - values or formulas
with Gridlines and Row and Column Headings


Thursday, January 21 (A)

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
Change to Landscape Orientation


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...

  • Change to Landscape Orientation
  • Change Bottom Margin to 0.5"
  • Change Scaling:  Fit to 1 page

Tuesday, January 19 (A)

  • Right-click here (Open Link in New Tab...), to show how to add a "Normal" Header

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

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 "Normal" Footer (Pg. 65, Exercise 9)
with Gridlines and Row and Column Headings

  1. Change to Landscape Orientation
  2. Change Left and Right Margins to 0.25"
  3. Fit printout to 1 page wide by 1 page tall

 


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


Wednesday, January 13 (A)
 

Explain "Header," Gridlines, and Row & Column Headings Printout...

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.

  • 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)


Monday, January 12 (A)
 

Lesson 1 (Exercise 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.


Lesson 1 (Exercise 2:  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.


Lesson 1 (Exercise 3:  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.


Lesson 1 (Exercise 4:  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.