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 PivotTables 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.
11. Import and export information and documents.
12. Use workgroup features.
13. Work with custom views.
14. 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
• Counting the Occurrences of a Value
• Finding the Sum of Specific Records
• Finding the Average of Specific Records

Working with PivotTables

• Creating and editing PivotTables
• Updating Information in a PivotTable
• Grouping PivotTable Data
• Filtering PivotTable Data using the Slicer (2010 and 2013 versions only)
• Hiding and Showing Source Details
• Adding Page Fields and displaying page fields on separate sheets
• Creating PivotCharts

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

• Recording, Running, and Assigning Macros
• Understanding relative and absoute referencing in Macros
• Editing, Renaming, and Deleting Macros

Importing Information

• Importing Documents from Other Applications
• Importing Text Files

Using Excel in a Workgroup

• Sharing workbooks
• Adding and deleting Comments to Cells
• Reading and Editing Comments
• Tracking Changes to Workbooks
• Accepting or Rejecting Changes

Working with Custom Views