by Dave Murphy | Publisher: Packt Publishing | Release Date: February 2019 | ISBN: 9781838648084

Become a Microsoft Certified Excel Expert - includes lectures, test exercises, and video solutionsAbout This VideoLearn the techniques to become a Microsoft Certified Excel Expert.A practical course designed to prepare you for the 77-728 Excel Expert certification examIn DetailThis 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-ExamDownloading the example code for this course: You can download the example code files for all Packt video courses you have purchased from your account at http://www.PacktPub.com. If you purchased this course elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

1. Chapter 1 : Introduction
   • Introduction 00:02:49
   • Understanding the Objective Domain 00:03:17
   • How is the exam be conducted and what will it be like? 00:05:44
2. Chapter 2 : 1.1 Manage Workbooks
   • 1.1.1 save a Workbook as a Template 00:03:01
   • 1.1.2 Copy Macros between Workbooks 00:04:01
   • 1.1.3 Reference Data in another Workbook 00:08:09
   • 1.1.4 Reference Data by using Structured References 00:08:26
   • 1.1.6 Display Hidden Ribbon Tabs 00:01:58
3. Chapter 3 : 1.2 Manage Workbook Review
   • 1.2.1 Restrict Editing 00:07:24
   • 1.2.2 Protect a Worksheet 00:10:05
   • 1.2.3 Configure Formula Calculation Option 00:02:34
   • 1.2.4 Protect Workbook Structure 00:01:14
   • 1.2.5 Manage Workbook Versions 00:06:12
   • 1.2.6 Encrypt a Workbook with a Password 00:02:37
4. Chapter 4 : Objective Domain 1 - Exercises & Solutions
   • Domain Objective 1: Manage Workbook Options and Settings - Exercises & Solutions 00:22:23
5. Chapter 5 : 2.1 Apply Custom Data Formats and Validation
   • 2.1.1 (Part I) Create Custom Number Formats 00:07:13
   • 2.1.1 (Part II) Create Custom Number Formats 00:08:54
   • 2.1.2 Populate Cells by using Advanced Fill Series Options 00:10:51
   • 2.1.2 (Part II) Issues with AutoFill using Dates 00:06:26
   • 2.1.3 Configure Data Validation 00:10:08
6. Chapter 6 : 2.2 Apply Advanced Conditional Formatting and Filtering
   • 2.2.1 Create Custom Conditional Formatting Rules 00:05:09
   • 2.2.2 Create Conditional Formatting Rules that use Formulas 00:03:23
   • 2.2.3 Manage Conditional Formatting Rules 00:03:57
7. Chapter 7 : 2.3 Create and Modify Custom Workbook Elements
   • 2.3.1 Create Custom Colour Formats 00:03:16
   • 2.3.2 Create and Modify Cell Styles 00:08:45
   • 2.3.3 Create and Modify Custom Themes 00:07:54
   • 2.3.4 (Part I) Create and Modify Simple Macros 00:06:50
   • 2.3.4 (Part II) Create and Modify Simple Macros 00:08:16
   • 2.3.5 (Part I) Insert and Configure Form Controls 00:12:01
   • 2.3.5 (Part II) Insert and Configure Form Controls 00:12:20
   • 2.3.5 (Part III) Insert and Configure Form Controls 00:08:23
8. Chapter 8 : 2.4 Prepare a Workbook for Internationalization
   • 2.4.1 and 2.4.2 Display Data in Multiple International Formats 00:10:33
   • 2.4.3 Manage Multiple Options for +Body and +Heading Fonts 00:02:48
9. Chapter 9 : Domain Objective 2 - Exercises & Solutions
   • Domain Objective 2 Apply Custom Data Formats and Layouts - Exercises & Solutions 00:21:49
10. Chapter 10 : 3.1 Apply Functions in Formulas
    • 3.1.1 Perform Logical Operations by using AND, OR, and NOT Functions 00:05:55
    • 3.1.2 Perform Logical Operations by using Nested Functions 00:06:44
    • 3.1.3 Perform Statistical Operations by using SUMIFS, AVERAGEIFS, and COUNTIFS 00:04:43
11. Chapter 11 : 3.2 Look up Data by using Functions
    • 3.2.1 and 3.2.2 Look Up Data by using the VLOOKUP & HLOOKUP Functions 00:15:17
    • 3.2.3 Look Up Data by using the INDEX and MATCH Functions 00:15:36
12. Chapter 12 : 3.3 Apply Advanced Date and Time Functions
    • 3.3.1 & 2 NOW, TODAY Functions and Serialise Numbers 00:14:26
13. Chapter 13 : 3.4 Perform Data Analysis and Business Intelligence
    • Introduction to Domain Objective 3.4 00:06:02
    • 3.4.1 (Part I) Connecting to Data with Power Pivot 00:10:37
    • 3.4.1 (Part II) More on Power Pivot 00:05:55
    • 3.4.1 (Part III) Measures 00:09:25
    • 3.4.1 (Part IV) Get and Transform 00:11:00
    • 3.4.2 Consolidate Data 00:06:30
    • 3.4.3 Perform What-if Analysis by using Goal Seek and Scenario Manager 00:09:36
    • 3.4.4 Cube Functions Part I 00:07:45
    • 3.4.4 Cube Functions Part II 00:05:09
    • 3.4.4 Cube Functions Part III 00:06:41
    • 3.4.5 Calculate Data by using Financial Functions 00:10:27
14. Chapter 14 : 3.5 Troubleshoot Formulas
    • 3.5.1 Trace Precedence and Dependence 00:02:55
    • 3.5.2 Monitor Cells and Formulas by using the Watch Window 00:02:42
    • 3.5.3 Validate Formulas by using Error Checking Rules 00:03:47
    • 3.5.4 Evaluate Formulas 00:05:35
15. Chapter 15 : 3.6 Define Named Ranges and Objects
    • 3.6.1 and 3.6.2 Name Cells and Data Ranges 00:06:23
    • 3.6.3 Name Tables 00:04:40
    • 3.6.4 Manage Named Ranges and Objects 00:03:06
16. Chapter 16 : Domain Objective 3 - Exercises & Solutions
    • Domain Objective 3 Create Advanced Formulas - Exercises & Solutions 00:42:41
17. Chapter 17 : 4.1 Create Advanced Charts
    • 4.1.1 Add Trendlines to Charts 00:05:38
    • 4.1.2 Create Dual-axis Charts 00:05:55
    • 4.1.3 Save a Chart as a Template 00:02:49
18. Chapter 18 : 4.2 Create and Manage Pivot Tables
    • 4.2.1 Create Pivot Tables 00:08:28
    • 4.2.2 Modify Field Selections and Options 00:05:26
    • 4.2.3 Create Slicers 00:04:53
    • 4.2.4 Group Pivot Table Data 00:03:20
    • 4.2.5 Reference Data in a Pivot Table by using the GETPIVOTDATA Function 00:05:40
    • 4.2.6 Add Calculated Fields 00:06:01
    • 4.2.7 Format Data 00:01:53
19. Chapter 19 : 4.3 Create and Manage Pivot Charts
    • 4.3.1 Create PivotCharts 00:03:17
    • 4.3.2 Manipulate Options in Existing PivotCharts 00:02:58
    • 4.3.3 Apply Styles to PivotCharts 00:01:10
    • 4.3.4 Drill Down into PivotChart Details 00:02:20
20. Chapter 20 : Domain Objective 4 - Exercises & Solutions
    • Domain Objective 4 Create Advanced Charts and Tables - Exercises & Solutions 00:19:40
21. Chapter 21 : CUBE Functions Special Video
    • CUBE Function Questions 00:06:53