SA-EXI-1: Microsoft Excel Intermediate

Upon completion of this program, participants should be able to:

• Create simple to complex formulas and functions, like:
• COUNTIF Function
• AVERAGEIF Function
• SUMIF Function
• IF and IFERROR Function
• Filter data using Auto & Advanced Filters
• Advanced Chart Formatting
• Clean Duplicate Records

Basic knowledge of Microsoft Excel is essential with the following pre-requisites:
Have attended Microsoft Excel – Foundation Level; OR

• Able to switch between task applications
• Able to create a spreadsheet with simple formatting
• Able to create a basic chart
• Able to print a spreadsheet with headers and footers added
• Able to use Auto Filter command
• Able to apply Freeze Pane command
• Able to create basic formulas – Addition, Subtraction, Multiplication and Division
• Able to use basic functions – AutoSum, Count, Max, Min and Average functions.

Module 1: Performing Calculations

Topic A: Reuse Formulas

• Relative References
• Absolute References
• Mixed References
• Understanding Mixed Cell References

Module 2: Working with Functions

Topic A: Using Statistical Function

• COUNTIFS Function
• AVERAGEIFS Function

Topic B: Using Mathematical Function

• SUMIFS Function

Topic C: Using Logical Function

• IFERROR Function
• IF Function

Module 3: Organizing Worksheet Data with Tables

Topic A: Create and Modify Tables

• Tables
• Table Components
• The Create Table Dialog Box
• The Table Tools – Design Contextual Tab
• Styles and Quick Style Sets
• Customizing Row Display
• Table Modification Options

Topic B: Sort and Filter Data

• The Difference Between Sorting and Filtering
• Sorting Data
• Advanced Filtering
• Removing Duplicate Values

Topic C: Use Subtotal to Calculate Data

• SUBTOTAL Functions
• Summary Functions in Tables

Module 4: Visualizing Data with Chart

Topic A: Create Charts

• Charts
• Chart Types
• Chart Insertion Methods
• Resizing and Moving the Chart
• Adding Additional Data
• Switching Between Rows and Columns

SA-EXA-2: Microsoft Excel Advanced

To help you get the most out of your Excel experience, this Microsoft Office Excel Advanced course expands on the foundational and intermediate skills offered in the Excel Foundation and Excel Intermediate courses.