This Microsoft Excel Expert course teaches you how to use a variety of intermediate and advanced features to merge workbooks, perform data analysis, and audit formulas in spreadsheets. Delegates also use summary functions, create Pivot Tables and Pivot Charts, and work with macros.
Delegates who complete this course will have reviewed all the exam objectives to prepare for the Microsoft Excel 2016 Expert Exam 77-728. In order to complete the Expert course the delegates need to have completed the Excel Advanced course at PCS.
Before undertaking the course, PCS will give you the expert training manual and access to our training quizzes. Delegates will need to use these resources to learn and revise for the exam. Once the delegate has used these resources they can come to our offices for a 1 day session at our offices to help you pass the exam.
Course Prerequisites
This course assumes that delegates have completed all levels of PCS' Microsoft Excel scheduled courses, or at least have the equivalent Excel knowledge and experience. Delegates should be able to:
 |
Enter text, numbers, dates and times |
|
Create formulas and apply functions | |
|
|
Create new workbooks using a template |
|
Create charts and insert graphical objects | |
|
|
Format cells and worksheets |
|
Use sparkline charts | |
|
|
Select a range of cells in different ways |
|
Create and modify tables | |
|
|
Split and freeze panes |
|
Sort, filter, find and replace data |
Course Content
Session 1: Advanced Formatting|
|
Section A: Conditional Formatting |
|
Section C: Internationalisation | |
|
|
Section B: Using Custom Cell Formats |
|
Section D: User Defined Styles |
Session 2: Advanced Functions And Formulas
|
|
Section A: Working with Named Ranges |
|
Section C: Using Lookup Functions | |
|
|
Section B: What are Functions? |
|
Section D: Date and Time Functions |
Session 3: Data Analysis Using Pivot Tables And Business Intelligence
|
|
Section A: Creating and Managing Pivot Tables |
|
Section C: Pivot Charts | |
|
|
Section B: Using Data Slicers with a Pivot Table |
|
Section D: Business Intelligence |
Session 4: Workbook Management Features
|
|
Section A: Linking External Workbooks |
|
Section C: Workgroup Functions | |
|
|
Section B: Consolidating Data |
|
Section D: Protecting Your Workbook |
Session 5: Advanced Charts, Functions And What-If Analysis
|
|
Section A: Advanced Chart Elements |
|
Section C: What-If Analysis | |
|
|
Section B: Using Advanced Functions and Features |
|
Section D: Structured References |
Session 6: Data Filtering, Macros And Forms
|
|
Section A: Advanced Filtering |
|
Section C: Macros | |
|
|
Section B: Working with Templates |
|
Section D: Adding Form Controls |
