Booking options
£121.99
£121.99
On-Demand course
9 hours 18 minutes
All levels
Become a Microsoft Certified Excel Expert - includes lectures, test exercises, and video solutions
This course will prepare you to take the 77-728 Excel Expert certification exam from Microsoft. Every aspect of the exam is covered in detail, together with exercises and solutions. This course is delivered by a University professor who has many years' experience in teaching Excel to undergraduate students and is also a Microsoft Office Specialist examiner. If you take this course and follow the guidelines provided, you will enhance your chances of obtaining this valuable qualification. Prove your skills by obtaining independent certification from Microsoft. All the code files are placed at https://github.com/PacktPublishing/77-728-Complete-Techniques-for-the-MOS-Excel-Expert-Exam
• You will be fully prepared to take the MOS Excel Expert Exam
• Get familiar with every aspect of the objective domain for the 77-728 exam
• Master the advanced use of Microsoft Excel 2016
• Get ready to pass the 77-728 exam
This course is for anyone who wants to obtain independent certification from Microsoft that they have achieved the expert level in the use of Microsoft Excel 2016; students who are skilled in Excel and want to achieve independent recognition of their abilities; students who have passed the 77-727 Core Excel exam and want to progress to the next level; and experts in Excel who would like to get their expertise recognized officially.
This course includes practice tests, projects, and tasks prepared at the level required to take the 77-728 Excel Expert certification exam. The solutions to all of the practice projects are provided in the form of video lectures, in which the instructor explains how to approach the project and how the solutions are derived.
• Learn the techniques to become a Microsoft Certified Excel Expert. * • A practical course designed to prepare you for the 77-728 Excel Expert certification exam
https://github.com/packtpublishing/77-728-complete-techniques-for-the-mos-excel-expert-exam
Dave Murphy - With over twenty years of experience as a systems analyst and project manager with significant experience in the design and implementation of major projects for some of the world's largest companies, Dave Murphy changed his career track in 2000 and has been a professional university educator for over 20 years. Dave is currently a lecturer in business information systems at the Technological University, Dublin. His main interests are in Microsoft certifications, project management, and business process analysis. Dave has a bachelor's in science (Hons) from Trinity College, Dublin, and a master's degree in computing. In addition to obtaining PRINCE2 Practitioner status, Dave is also a Microsoft Certified Professional in managing projects with Microsoft Project, is a MOS Master, and also a MOS examiner.
1. Introduction
1. Introduction Introduction: Introduction |
2. Understanding the Objective Domain Introduction: Understanding the Objective Domain |
3. How is the exam be conducted and what will it be like? Introduction: How is the exam be conducted and what will it be like? |
2. 1.1 Manage Workbooks
1. 1.1.1 save a Workbook as a Template Manage Workbooks: 1.1.1 save a Workbook as a Template |
2. 1.1.2 Copy Macros between Workbooks Manage Workbooks: 1.1.2 Copy Macros between Workbooks |
3. 1.1.3 Reference Data in another Workbook Manage Workbooks: 1.1.3 Reference Data in another Workbook |
4. 1.1.4 Reference Data by using Structured References Manage Workbooks: 1.1.4 Reference Data by using Structured References |
5. 1.1.6 Display Hidden Ribbon Tabs Manage Workbooks: 1.1.6 Display Hidden Ribbon Tabs |
3. 1.2 Manage Workbook Review
1. 1.2.1 Restrict Editing 1.2 Manage Workbook Review: 1. 2.1 Restrict Editing |
2. 1.2.2 Protect a Worksheet 1.2 Manage Workbook Review: 1. 2.2 Protect a Worksheet |
3. 1.2.3 Configure Formula Calculation Option 1.2 Manage Workbook Review: 1. 2.3 Configure Formula Calculation Option |
4. 1.2.4 Protect Workbook Structure 1.2 Manage Workbook Review: 1. 2.4 Protect Workbook Structure |
5. 1.2.5 Manage Workbook Versions 1.2 Manage Workbook Review: 1. 2.5 Manage Workbook Versions |
6. 1.2.6 Encrypt a Workbook with a Password 1.2 Manage Workbook Review: 1. 2.6 Encrypt a Workbook with a Password |
4. Objective Domain 1 - Exercises & Solutions
1. Domain Objective 1: Manage Workbook Options and Settings - Exercises & Solutions Objective Domain 1 - Exercises & Solutions: Domain Objective 1: Manage Workbook Options and Settings - Exercises & Solutions |
5. 2.1 Apply Custom Data Formats and Validation
1. 2.1.1 (Part I) Create Custom Number Formats Apply Custom Data Formats and Validation: 2.1.1 (Part I) Create Custom Number Formats |
2. 2.1.1 (Part II) Create Custom Number Formats Apply Custom Data Formats and Validation: 2.1.1 (Part II) Create Custom Number Formats |
3. 2.1.2 Populate Cells by using Advanced Fill Series Options Apply Custom Data Formats and Validation: 2.1.2 Populate Cells by using Advanced Fill Series Options |
4. 2.1.2 (Part II) Issues with AutoFill using Dates Apply Custom Data Formats and Validation: 2.1.2 (Part II) Issues with AutoFill using Dates |
5. 2.1.3 Configure Data Validation Apply Custom Data Formats and Validation: 2.1.3 Configure Data Validation |
6. 2.2 Apply Advanced Conditional Formatting and Filtering
1. 2.2.1 Create Custom Conditional Formatting Rules 2.2 Apply Advanced Conditional Formatting and Filtering: 2. 2.1 Create Custom Conditional Formatting Rules |
2. 2.2.2 Create Conditional Formatting Rules that use Formulas Apply Advanced Conditional Formatting and Filtering: 2.2.2 Create Conditional Formatting Rules that use Formulas |
3. 2.2.3 Manage Conditional Formatting Rules Apply Advanced Conditional Formatting and Filtering: 2.2.3 Manage Conditional Formatting Rules |
7. 2.3 Create and Modify Custom Workbook Elements
1. 2.3.1 Create Custom Colour Formats 2.3 Create and Modify Custom Workbook Elements: 2. 3.1 Create Custom Colour Formats |
2. 2.3.2 Create and Modify Cell Styles 2.3 Create and Modify Custom Workbook Elements: 2. 3.2 Create and Modify Cell Styles |
3. 2.3.3 Create and Modify Custom Themes 2.3 Create and Modify Custom Workbook Elements: 2. 3.3 Create and Modify Custom Themes |
4. 2.3.4 (Part I) Create and Modify Simple Macros 2.3 Create and Modify Custom Workbook Elements: 2. 3.4 (Part I) Create and Modify Simple Macros |
5. 2.3.4 (Part II) Create and Modify Simple Macros 2.3 Create and Modify Custom Workbook Elements: 2. 3.4 (Part II) Create and Modify Simple Macros |
6. 2.3.5 (Part I) Insert and Configure Form Controls 2.3 Create and Modify Custom Workbook Elements: 2. 3.5 (Part I) Insert and Configure Form Controls |
7. 2.3.5 (Part II) Insert and Configure Form Controls 2.3 Create and Modify Custom Workbook Elements: 2. 3.5 (Part II) Insert and Configure Form Controls |
8. 2.3.5 (Part III) Insert and Configure Form Controls 2.3 Create and Modify Custom Workbook Elements: 2. 3.5 (Part III) Insert and Configure Form Controls |
8. 2.4 Prepare a Workbook for Internationalization
1. 2.4.1 and 2.4.2 Display Data in Multiple International Formats 2.4 Prepare a Workbook for Internationalization: 2. 4.1 and 2.4.2 Display Data in Multiple International Formats |
2. 2.4.3 Manage Multiple Options for +Body and +Heading Fonts 2.4 Prepare a Workbook for Internationalization: 2. 4.3 Manage Multiple Options for +Body and +Heading Fonts |
9. Domain Objective 2 - Exercises & Solutions
1. Domain Objective 2 Apply Custom Data Formats and Layouts - Exercises & Solutions Domain Objective 2 - Exercises & Solutions: Domain Objective 2 Apply Custom Data Formats and Layouts - Exercises & Solutions |
10. 3.1 Apply Functions in Formulas
1. 3.1.1 Perform Logical Operations by using AND, OR, and NOT Functions Apply Functions in Formulas: 3.1.1 Perform Logical Operations by using AND, OR, and NOT Functions |
2. 3.1.2 Perform Logical Operations by using Nested Functions Apply Functions in Formulas: 3.1.2 Perform Logical Operations by using Nested Functions |
3. 3.1.3 Perform Statistical Operations by using SUMIFS, AVERAGEIFS, and COUNTIFS Apply Functions in Formulas: 3.1.3 Perform Statistical Operations by using SUMIFS, AVERAGEIFS, and COUNTIFS |
11. 3.2 Look up Data by using Functions
1. 3.2.1 and 3.2.2 Look Up Data by using the VLOOKUP & HLOOKUP Functions Look up Data by using Functions: 3.2.1 and 3.2.2 Look Up Data by using the VLOOKUP & HLOOKUP Functions |
2. 3.2.3 Look Up Data by using the INDEX and MATCH Functions Look up Data by using Functions: 3.2.3 Look Up Data by using the INDEX and MATCH Functions |
12. 3.3 Apply Advanced Date and Time Functions
1. 3.3.1 & 2 NOW, TODAY Functions and Serialise Numbers 3.3 Apply Advanced Date and Time Functions: 3. 3.1 & 2 NOW, TODAY Functions and Serialise Numbers |
13. 3.4 Perform Data Analysis and Business Intelligence
1. Introduction to Domain Objective 3.4 Perform Data Analysis and Business Intelligence: Introduction to Domain Objective 3.4 |
2. 3.4.1 (Part I) Connecting to Data with Power Pivot 3.4 Perform Data Analysis and Business Intelligence: 3. 4.1 (Part I) Connecting to Data with Power Pivot |
3. 3.4.1 (Part II) More on Power Pivot 3.4 Perform Data Analysis and Business Intelligence: 3. 4.1 (Part II) More on Power Pivot |
4. 3.4.1 (Part III) Measures 3.4 Perform Data Analysis and Business Intelligence: 3. 4.1 (Part III) Measures |
5. 3.4.1 (Part IV) Get and Transform 3.4 Perform Data Analysis and Business Intelligence: 3. 4.1 (Part IV) Get and Transform |
6. 3.4.2 Consolidate Data 3.4 Perform Data Analysis and Business Intelligence: 3. 4.2 Consolidate Data |
7. 3.4.3 Perform What-if Analysis by using Goal Seek and Scenario Manager 3.4 Perform Data Analysis and Business Intelligence: 3. 4.3 Perform What-if Analysis by using Goal Seek and Scenario Manager |
8. 3.4.4 Cube Functions Part I 3.4 Perform Data Analysis and Business Intelligence: 3. 4.4 Cube Functions Part I |
9. 3.4.4 Cube Functions Part II 3.4 Perform Data Analysis and Business Intelligence: 3. 4.4 Cube Functions Part II |
10. 3.4.4 Cube Functions Part III 3.4 Perform Data Analysis and Business Intelligence: 3. 4.4 Cube Functions Part III |
11. 3.4.5 Calculate Data by using Financial Functions 3.4 Perform Data Analysis and Business Intelligence: 3. 4.5 Calculate Data by using Financial Functions |
14. 3.5 Troubleshoot Formulas
1. 3.5.1 Trace Precedence and Dependence Troubleshoot Formulas: 3. 5.1 Trace Precedence and Dependence |
2. 3.5.2 Monitor Cells and Formulas by using the Watch Window Troubleshoot Formulas: 3. 5.2 Monitor Cells and Formulas by using the Watch Window |
3. 3.5.3 Validate Formulas by using Error Checking Rules Troubleshoot Formulas: 3. 5.3 Validate Formulas by using Error Checking Rules |
4. 3.5.4 Evaluate Formulas Troubleshoot Formulas: 3. 5.4 Evaluate Formulas |
15. 3.6 Define Named Ranges and Objects
1. 3.6.1 and 3.6.2 Name Cells and Data Ranges 3.6 Define Named Ranges and Objects: 3. 6.1 and 3.6.2 Name Cells and Data Ranges |
2. 3.6.3 Name Tables 3.6 Define Named Ranges and Objects: 3. 6.3 Name Tables |
3. 3.6.4 Manage Named Ranges and Objects 3.6 Define Named Ranges and Objects: 3. 6.4 Manage Named Ranges and Objects |
16. Domain Objective 3 - Exercises & Solutions
1. Domain Objective 3 Create Advanced Formulas - Exercises & Solutions Domain Objective 3 - Exercises & Solutions: Domain Objective 3 Create Advanced Formulas - Exercises & Solutions |
17. 4.1 Create Advanced Charts
1. 4.1.1 Add Trendlines to Charts Create Advanced Charts: 4.1.1 Add Trendlines to Charts |
2. 4.1.2 Create Dual-axis Charts Create Advanced Charts: 4.1.2 Create Dual-axis Charts |
3. 4.1.3 Save a Chart as a Template Create Advanced Charts: 4.1.3 Save a Chart as a Template |
18. 4.2 Create and Manage Pivot Tables
1. 4.2.1 Create Pivot Tables Create and Manage Pivot Tables: 4.2.1 Create Pivot Tables |
2. 4.2.2 Modify Field Selections and Options Create and Manage Pivot Tables: 4.2.2 Modify Field Selections and Options |
3. 4.2.3 Create Slicers Create and Manage Pivot Tables: 4.2.3 Create Slicers |
4. 4.2.4 Group Pivot Table Data Create and Manage Pivot Tables: 4.2.4 Group Pivot Table Data |
5. 4.2.5 Reference Data in a Pivot Table by using the GETPIVOTDATA Function Create and Manage Pivot Tables: 4.2.5 Reference Data in a Pivot Table by using the GETPIVOTDATA Function |
6. 4.2.6 Add Calculated Fields Create and Manage Pivot Tables: 4.2.6 Add Calculated Fields |
7. 4.2.7 Format Data Create and Manage Pivot Tables: 4.2.7 Format Data |
19. 4.3 Create and Manage Pivot Charts
1. 4.3.1 Create PivotCharts Create and Manage Pivot Charts: 4.3.1 Create PivotCharts |
2. 4.3.2 Manipulate Options in Existing PivotCharts Create and Manage Pivot Charts: 4.3.2 Manipulate Options in Existing PivotCharts |
3. 4.3.3 Apply Styles to PivotCharts Create and Manage Pivot Charts: 4.3.3 Apply Styles to PivotCharts |
4. 4.3.4 Drill Down into PivotChart Details Create and Manage Pivot Charts: 4.3.4 Drill Down into PivotChart Details |
20. Domain Objective 4 - Exercises & Solutions
1. Domain Objective 4 Create Advanced Charts and Tables - Exercises & Solutions Domain Objective 4 - Exercises & Solutions: Domain Objective 4 Create Advanced Charts and Tables - Exercises & Solutions |
21. CUBE Functions Special Video
1. CUBE Function Questions CUBE Functions Special Video: CUBE Function Questions |