MOUS Essentials

MARIANNE FOX is an Indiana CPA with BS and MBA degrees in Accounting from Indiana University. For more than20 years, she has enjoyed teaching full-time—initially in Indiana University's School of Business; since 1988 in the College of Business Administration at Butler University. As the co-owner of an Indian-based consulting firm, Marianne has extensive experience consulting and training in the corporate and continuing education environments. Since 1984, she has co-authored more than 35 computer-related books and made presentations on accounting, computer applications, and instructional development topics at a variety of seminars and conferences. LAWRENCE C. METZELAAR earned a BS in Business Administration and Computer Science from the University of Maryland and an Ed.M and CAGS in Human Problem Solving from Boston University. Larry has more than 35 years experience with military and corporate mainframe and microcomputer systems. He has taught computer science and information systems at the University of Hawaii, Control Data Institute, Indiana University, Purdue University, and is currently on the faculty at Butler University. As the co-owner of an Indiana-based consulting firm, he has extensive experience consulting and training in the corporate environment. Since 1984, he has co-authored more than 35 computer-related books and made presentations on computer applications and instructional development topics at a variety of seminars and conferences.

1. Getting Started with Excel 2000.


Exploring the Excel Work Area. Entering Text and Numbers. Saving a Workbook. Getting Help. Entering a Formula. Preparing a Worksheet for Printing. Printing a Worksheet. Closing a File and Exiting Excel.



2. Modifying a Worksheet.


Opening an Existing Worksheet. Selecting Worksheet Items. Using AutoFill. Inserting and Deleting Rows and Columns. Copying and Moving Cell Contents. Using AutoSum. Copying a Formula with Relative References. Spell-Checking a Worksheet.



3. Improving the Appearance of a Worksheet.


Formatting Numbers. Aligning Cell Contents. Changing Font and Font Size. Applying Bold, Italic, and Underline. Adding Color. Adding Borders. Using Format Painter. Removing Formatting.



4. Entering Formulas in Well-Designed Worksheets.


Working with Operators and Order of Precedence. Using Type-and-Point to Enter Formulas. Comparing Absolute and Relative Cell References. Creating and Copying Formulas with Absolute or Relative Cell References. Freezing and Splitting Worksheet Display. Hiding and Unhiding Rows and Columns. Displaying Formulas Instead of Formula Results. Using Save As to Change File-name, File Type or Storage Location.



5. Working with Functions.


Analyzing Data with AVERAGE, MAX and MIN. Calculating a Loan Payment with PMT. Creating a Loan Payment Table. Evaluating Investment Plans with FV Function. Using IF to Display Messages. Using IF to Calculate. Using NOW to Display the Current Date. Using VLOOKUP to Convert Data.



6. Creating and Modifying Charts.


Identifying Common Chart Types and Features. Using Chart Wizard to Create an Embedded Pie Chart. Using Chart Wizard to Create an Embedded Column Chart. Changing Chart Type. Charting Nonadjacent Data Series. Modifying Chart Formulas. Adding, Reordering and Deleting a Data Series. Creating and Printing a Combination Chart.



7. Developing a Multiple-Sheet Workbook.


Copying Data from Another Application. Inserting, Moving and Renaming Worksheets. Editing Multiple Worksheets Simultaneously. Subtotaling Data. Linking Worksheets. Applying Conditional Formatting. Using a Worksheet to Solve a Problem. Saving a Worksheet with Chart as a Web Page.



8. Working with Custom Formats, Auto Formats, Styles, and Templates.


Inserting and Deleting Cells. Replacing and Rotating Text. Creating a Custom Format. Applying an AutoFormat. Creating and Using Styles. Copying a Worksheet. Emailing a Workbook or Worksheet within Excel. Creating and Using a Template.



9. Creating Special Effects in a Worksheet.


Creating WordArt. Inserting and Rotating an AutoShape. Creating a Text Box. Grouping Objects. Adding Emphasis with Lines and Arrows. Adding Emphasis with Callouts. Inserting a Graphics File. Embedding Sound.



10. Sorting, Filtering, and Editing Lists.


Using an Excel List as a Database. Sorting Records on the Contents of One Field. Sorting Records on the Contents of Two or More Fields. Using AutoFilter with One Search Condition. Using Custom AutoFilter with OR Search Criteria. Using Custom AutoFilter with AND Search Criteria. Adding and Deleting Records by Using a Form. Editing Records by Using a Form.



11. Documenting and Protecting Worksheets and Workbooks.


Creating and Using Range Names. Attaching Comments to a Cell. Getting Help on Security Features in Excel. Protecting Worksheet Contents and Objects. Unlocking Cells. Unlocking Objects. Viewing and Setting File Properties. Requiring a Password.



12. Guiding Cell Entry with Data Validation.


Attaching an Input Message to a Cell. Restricting Cell Entries to Data from a List. Restricting Cell Entries to Whole Numbers within Specified Limits. Creating a User-Specified Error Alert Message. Copying Data Restrictions and Messages to Other Cells. Using Audit Tools to Find Invalid Data. Editing Errors Including Validation Settings. Finding Cells That Have Data Restrictions or Messages.



13. Creating PivotTable and PivotChart Reports.


Displaying Help on Pivot Tables and Related Charts. Creating a Pivot Table. Expanding a Pivot Table. Removing, Hiding, and Showing Data. Refreshing a Pivot Table. Creating a Chart from Pivot Table Data. Applying Predefined Formats to a Pivot Table. Displaying Pivot Tables on the Web.



14. Creating Consolidated Data, Outlines, Views, and Reports.


Consolidating Data in Multiple Worksheets. Consolidating Multiple Workbooks. Creating an Auto Outline. Creating a Manual Outline. Creating and Using Custom Views. Creating Reports Using Report Manager. Editing and Printing Reports Using Report Manager. Saving a Group of Workbooks in a Workspace File.



15. Automating Tasks with Macros.


Preparing to Record a Macro. Recording a Macro. Playing a Macro. Recording a Macro in a Personal Macro Workbook. Playing a Macro from the Personal Macro Workbook. Unhiding and Editing the Personal Macro Workbook. Creating a Macro Button in a Workbook.



16. Using Auditing Tools, Goal Seek, Data Tables, Scenarios, and Solver.


Using Auditing Tools to Interpret Error Messages. Using Auditing Tools to Check Worksheet Formulas. Using Auditing Tools to Reverse Engineer a Worksheet. Using Goal Seek in Revenue Analysis. Performing a What-if Analysis with a Data Table. Using Scenarios to Perform What-If Analysis. Using Solver to Maximize Profits.



17. Using Collaborative Tools, Extracting Data, and Creating HyperLinks.


Tracking Changes in a Workbook. Accepting or Rejecting Changes. Editing a Shared Workbook. Extracting Data Using Advanced Filter. Using Hyperlinks within a Workbook. Using a Hyperlink in a Word Document to Reference Excel Data. Using a Hyperlink in a PowerPoint Slide to Reference Excel Data.



18. Integrating Applications.


Linking Excel Data to a Word Document. Embedding Excel Data in a Word Document. Linking Excel Data to a PowerPoint Slide. Linking an Excel Chart to a PowerPoint Slide. Converting an Excel List to an Access Database. Importing Data from a Text File. Editing an HTML File Using Excel. Getting External Data from Access into Excel.



Appendix A: Using the MOUS PinPoint 2000 Training and Testing Software.


Introduction to PinPoint 2000. Running PinPoint 2000. Installing the Launcher to Your Computer. Preparing to Run the PinPoint 2000 Launcher. Starting the PinPoint 2000 Launcher. Starting the PinPoint 2000 Trainers and Evaluations. Running a Trainer. Running an Evaluation. Viewing Reports in the Launcher. Recovering from a Crash during a Trainer or Evaluation. Removing PinPoint 2000.



Appendix B: Preparing for MOUS Certification.


Glossary.


Index.