Week 6 - Exploring the Function Library (Ch6) | Working with Templates and Co-Authoring (Ch7)

Overview

The Excel Function Library includes functions that perform complex and sophisticated calculations for use in business, government, education, and research. In this chapter, you learn about functions in the Database, Logical, Date & Time, Financial, and Text categories. You also nest functions and build time, date, and statistical calculations.

Excel templates are model workbooks. When you create a new workbook, you use a default template with basic settings, including a document theme, a font name and size, and default margins. When you co-author a workbook, you and colleagues collaborate on a shared file.

Objectives

At the end of this week, you will be able to:

• Use Database functions such as DSUM and DAVERAGE.
• Build Logical functions including AND, OR, and IFS functions.
• Explore the Lookup & Reference category with INDEX, MATCH, and TRANSPOSE.
• Build date, time, and statistical calculations.
• Use Financial functions such as PV, FV, and NPV.
• Work with Text functions including TEXTJOIN, CONCAT, EXACT, and REPLACE.
• Use multiple criteria in SUMIFS, AVERAGEIFS, and COUNTIFS functions.
• Monitor and edit functions with the Watch Window and Find and Replace.
• Use a template to create a workbook and save a workbook as a template.
• Set data validation, input messages, and error alerts.
• Use Form Controls in a workbook.
• Set worksheet and workbook protection to manage editing.
• Co-author a workbook.
• Finalize a workbook for distribution by inspecting it for personal information, accessibility, and compatibility.

Learning Activities

1. Read Excel Ch 6 (SIMBook) in SIMnet.
2. Read Excel Ch 7 (SIMBook) in SIMnet.
3. Complete the end-of-chapter questions to check your knowledge (you are not required to submit these).

Assignments

1. Complete Excel Ch 6 SIMBook 'Let Me Try' in-reading tasks.
2. Complete Excel Ch 6 SIMnet Project 1.
3. Complete Excel Ch 6 SIMnet Project 2.
4. Complete Excel Ch 7 SIMBook 'Let Me Try' in-reading tasks.
5. Complete Excel Ch 7 SIMnet Project 1.
6. Complete Excel Ch 7 SIMnet Project 2.