Description

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

What You Will Learn

•  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

Audience

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.

Approach

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.

Key Features

• 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

Github Repo

https://github.com/packtpublishing/77-728-complete-techniques-for-the-mos-excel-expert-exam

About the Author

Dave Murphy

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.

Course Outline

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

Course Images

77-728 Complete Techniques for the MOS Excel Expert Exam

By Packt

Booking options

Highlights