Standard

 

CIS 222 - Advanced Microsoft Excel

Course Description

Provide the fundamental, intermediate and advanced Microsoft Excel competencies to provide user with the skills necessary to obtain the expert user certification.  Topics include spreadsheet creation, financial statements, forecast, amortization schedules, workgroup editing and advanced features such as macros, using charts, importing and exporting data, HTML creation, formulas, Web queries, built-in function, templates, and trends and relationships.

 

Competency Areas

Hours

 

Importing and exporting data

Class

2

Use templates

D. Lab

3

Use multiple workbooks

P. Lab/O.B.I.

0

Formatting numbers

Credit

3

Work with named ranges

 

 

Collaborating with workgroups

 

 

Using macros and analysis tools

 

 

Printing workbooks and using toolbars

 

 

Auditing a worksheet

 

 

 

Prerequisite:  CIS – Advanced Spreadsheet Techniques

Corequisite:

 

Course Guide

 

Competency

After completing this section, the student will:

Hours

Class

D. Lab

P. Lab/

O.B.I.

INPORTING AND EXPORTING DATA

2

2

0

 

Export to other applications Import data from text files (insert, drag and drop).

 

 

 

 

Import from other applications.

 

 

 

 

Import a table from an HTML file (insert, drag and drop - including HTML round tripping).

 

 

 

USE TEMPLATES

1

2

0

 

Apply templates.

 

 

 

 

Edit templates.

 

 

 

 

Create templates.

 

 

 

USE MULTIPLE WORKBOOKS

1

2

0

 

Using a workspace.

 

 

 

 

Link workbooks.

 

 

 

FORMATTING NUMBERS

2

4

0

 

Apply number formats (accounting, currency, number).

 

 

 

 

Create custom number formats.

 

 

 

 

Use conditional formatting.

 

 

 

PRINTING WORKBOOKS AND USING TOOLBARS

3

4

o

 

Print and preview multiple worksheets.

 

 

 

 

Use the Report Manager.

 

 

 

 

Hide and display toolbars.

 

 

 

 

Customize a toolbar.

 

 

 

 

Assign a macro to a command button.

 

 

 

WORKING WITH NAMED RANGES

3

4

0

 

Add and delete a named range.

 

 

 

 

Use a named range in a formula.

 

 

 

 

Use Lookup Functions (Hlookup or Vlookup).

 

 

 

COLLABORTING WITH WORKGROUPS

2

3

0

 

Create, edit and remove a comment.

 

 

 

 

Apply and remove worksheet and workbook protection.

 

 

 

 

Change workbook properties.

 

 

 

 

Apply and remove file passwords.

 

 

 

 

Track changes (highlight, accept, and reject).

 

 

 

 

Create a shared workbook.

 

 

 

 

Merge workbooks.

 

 

 

USING MACROS AND ANALYSIS TOOLS

4

6

0

 

Record, run and edit macros.

 

 

 

 

Use PivotTable autoformat.

 

 

 

 

Use Goal Seek.

 

 

 

 

Create pivot chart reports.

 

 

 

 

Work with Scenarios.

 

 

 

 

Use Solver.

 

 

 

 

Use data analysis and PivotTables.

 

 

 

 

Create interactive PivotTables for the Web.

 

 

 

 

Add fields to a PivotTable using the Web browser.

 

 

 

AUDITING A WORKSHEET

2

3

0

 

Work with the Auditing Toolbar.

 

 

 

 

Trace errors (find and fix errors).

 

 

 

 

Trace precedents (find cells referred to in a specific formula).

 

 

 

 

Trace dependents (find formulas that refer to a specific cell).

 

 

 

 

Suggested Resources

 

Books

 

New Perspectives on Microsoft Excel 2000 - Comprehensive Enhanced, Parsons, Oja, & Ageloff,   ISBN 0-7600-7087-3, Course Technology, 2000.

MOUS Essentials:  Excel 2000 with CD,  Fox & Metzelaar, ISBN: 0130191043, Prentice Hall, 2000.