CMSY 117 Advanced Spreadsheets

After successful completion of this course, students will be able to use Microsoft® Excel to apply advanced formatting techniques and functions, perform what-if analysis, create PivotTables, use custom and advanced filters, and audit worksheets. This course may be completed in fewer than 14 weeks. All of the work (except tests) may be done outside of class if the student has compatible software.

Credits

Prerequisite

CMSY 101

Course Objectives

1. Retrieve records using AutoFilter and custom filter.
2. Insert automatic subtotals, use lookup functions to locate list entries, and apply database functions to
summarize list data that meets specific criteria.
3. Use advanced financial, statistical, logical, text, lookup, and math functions.
4. Track what-if scenarios and generate summary reports using Excel Scenario Manager.
5. Use Goal Seek to perform what-if analysis involving multiple variables.
6. Plan, design, create, update, and change the layout of a PivotTable report.
7. Exchange data between Excel and other Windows programs which includes embedding and linking.
8. Create a shared workbook.
9. Perform a worksheet audit and add comments.
10. Record and run a macro.

Course Objectives

1. Retrieve records using AutoFilter and custom filter.
2. Insert automatic subtotals, use lookup functions to locate list entries, and apply database functions to
summarize list data that meets specific criteria.
3. Use advanced financial, statistical, logical, text, lookup, and math functions.
4. Track what-if scenarios and generate summary reports using Excel Scenario Manager.
5. Use Goal Seek to perform what-if analysis involving multiple variables.
6. Plan, design, create, update, and change the layout of a PivotTable report.
7. Exchange data between Excel and other Windows programs which includes embedding and linking.
8. Create a shared workbook.
9. Perform a worksheet audit and add comments.
10. Record and run a macro.