Streamline Calculations with Formulas and Functions - Microsoft® Office Excel® 2007 & 2010
Course Content
Module 1: Work with Formulas
This module explains how to construct formulas, apply different types of mathematical operators, and use relative and absolute references. It also explains the order of calculation that Microsoft Excel uses when evaluating formulas.
Lessons
Constructing formulas in Microsoft Excel
Formula execution order of precedence
Lab : Working with Formulas
Create and edit formulas with parentheses
Paste formulas as values
Resize the formula bar and insert line breaks in a cell
Lab : Relative and absolute cell references
Use relative, absolute and mixed references
Module 2: Common Functions
This module explains how to use some of the more common functions in Microsoft Excel and the correct syntax when using a function.
Lessons
Introducing functions
Function syntax
Using simple functions
Lab : Introducing functions
Function syntax
Using simple functions
Search for and insert a list function
Lab : COUNT functions
Use the COUNT, COUNTA and COUNTBLANK functions
Lab : Date and Time functions
Use the DATE, TODAY and NOW functions
Module 3: More Advanced Functions
This module explains how to use some of the more advanced functions in Microsoft Excel. It also introduces the IF functions in Microsoft Excel.
Lessons
Analysing data with advanced functions
Demonstrations
Lab : Advanced functions
Use the PMT ,ROUND, COUNTIF and SUMIF functions
Lab : The IF function
Use the IF function to manage values and text
After completing this module, students will be able to:
Calculate monthly loan repayments using the PMT function
Understand precision with the ROUND function
Apply the COUNTIF function
Perform complex additions using the SUMIF function
Use the IF function and text
Module 4: Named Ranges in Formulas
This module explains how to create named cell ranges, use named ranges in formulas and manage names using the Name manager.
Lessons
Understanding the why and how of naming ranges
Using named ranges in formulas
Lab : Understand Names
Use the name box to navigate and create names
Use a name in a formula
Lab : Create and Apply Names
Create and apply names from row and column labels
Use the New Name dialog box
Modify names
Module 5: Link Worksheet and Workbooks
This module explains how to create formulas that contain links to other worksheets and workbooks. It also describes how to create three dimensional formulas and manage links in formulas.
Lessons
Building formulas that link worksheets
Building formulas that link workbooks
Understanding and managing links
Lab : Linking multiple worksheets
Create a link within a workbook
Consolidate using 3D functions
Lab : Link workbooks
Create, manage and edit links between workbooks
Module 6: Improve Worksheet Readability
This module explains how to create worksheets with consistent formatting, use the Fill command and create a custom list. It also describes how to use automatic number formatting and how to create a custom number format.
Lessons
Applying consistent formatting to worksheets
Creating custom fill lists
Understanding automatic cell number formatting
Creating a custom number format
Lab : Conditional Formatting
Apply cell styles and conditional formatting
Use shapes as labels
Create and use a custom list
Lab : Number Formatting
Create and apply number formatting
Use automatic number formats
Lab : The Fill Command
Fill adjacent ranges, worksheets and date series