77-728 Complete Techniques for the MOS Excel Expert Exam

77-728 Complete Techniques for the MOS Excel Expert Exam

by Dave Murphy
Released February 2019
Publisher(s): Packt Publishing
ISBN: 9781838648084

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

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.

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.

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.

Table of contents

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

Product information

  • Title: 77-728 Complete Techniques for the MOS Excel Expert Exam
  • Author(s): Dave Murphy
  • Release date: February 2019
  • Publisher(s): Packt Publishing
  • ISBN: 9781838648084