Prerequisites

Prerequisites

Participants should have completed the Excel Intermediate level or have equivalent experience. 

Who Should Take This Course
Microsoft Excel users who are familiar with the intermediate level functions and want to learn how to use the software's high end-user features.

Course Objectives

Course Objectives

Upon completing this course, you will have learned how to:

1. Sort and manage data in lists.
2. Filter and query data.
3. Use outlines on worksheets.
4. Use the following built-in Excel functions: lookup and reference, database, date and time, text, financial, and information.
5. Use Pivot tables to display and chart data.
6. Use data tables and scenarios to make projections about data.
7. Use Goal Seek and the Solver add-in to analyse data.
8. Audit worksheets.
9. Protect worksheets and workbooks.
10. Create and use macros (note: this course doesn't cover VBA)
11. Work with custom views.
12. Consolidate data.

Course Outline

Course Outline

Working with Functions

• Applying Nested IFs to test multiple conditions
• Combining the IF function with other functions such as AND, OR, ISBLANK, and ISERROR
• Use the VLOOKUP function
• Joining several text strings into one text string with CONCATENATE function
• Using the NETWORKDAYS function to calculate workdays between two dates 

Working with Databases

• Creating and removing Subtotals
• Using Data Validation to restrict entries
• Displaying an Input Message
• Displaying an Error Alert

Filtering and Querying Data

• Filtering Data including custom filters
• Filtering with single and multiple comparison criterion
• Using the Advanced Filter

Using Outlines

• Creating manual and automatic outlines
• Hiding and displaying outline levels

Using Lookup and Database Functions

• Using LOOKUP function to automatically look up values in a Table
• Working with Database functions (e.g. DSUM, DCOUNT etc.)
• Using COUNTIF to count the occurrences of a value
• Using SUMIF to find the Sum of specific records
• Using AVERAGEIF to find the average of specific records

Working with Pivot tables

• Creating and editing Pivot tables
• Updating information in a Pivot table
• Grouping Pivot table data
• Filtering Pivot table data using the Slicer
• Adding page fields and displaying page fields on separate sheets
• Creating Pivot charts

Making Projections About Data

• Solving What-If problems
• Using Data Tables (one-input and two-input tables)
• Creating and managing scenarios
• Creating a summary of scenario results

Analysing Data

• Working with the Goal Seek feature
• Working with Solver add-in

Auditing Worksheets

• Trace errors in formulas
• Finding referenced cells
• Finding formulas that refer to a cell

Implementing Data Security

• Understanding levels of security
• Protecting worksheets and workbooks

Working with Macros (non-VBA)

• Recording, running, and assigning Macros
• Understanding relative and absolute referencing in Macros
• Editing, renaming, and deleting Macros

Working with Custom Views

Consolidating Data