The Microsoft Excel Expert (Office 2016) – Exam 77-728 is part of the Microsoft Office Specialist (MOS) certification track, designed to validate an individual’s advanced proficiency in Microsoft Excel 2016. Earning this certification demonstrates that the candidate possesses the skills necessary to utilize complex features of Excel and guide others in its professional application.
Who Should Take This Exam
This certification is intended for individuals who:
- Have a deep and thorough understanding of the Excel 2016 environment
- Can provide guidance on best practices for using Excel’s advanced tools and features
- Work in roles requiring sophisticated data analysis and reporting capabilities
Typical job roles include:
- Accountants
- Financial analysts
- Data analysts
- Commercial bankers
- Other professionals who frequently work with complex datasets or financial models
– Key Skills Measured
Expert-level candidates are expected to demonstrate the ability to:
- Design and manage professional-grade spreadsheets tailored to specific business scenarios
- Customize the Excel environment for project-specific needs and workflow efficiency
- Develop and maintain tools such as amortization tables, financial dashboards, inventory trackers, and custom business templates
- Integrate multiple features for enhanced productivity and reporting
– Benefits of Certification
Successfully passing Exam 77-728 confirms that the candidate can:
- Confidently use advanced Excel features in professional contexts
- Enhance business productivity through efficient spreadsheet solutions
- Serve as a resource for others within an organization needing guidance on Excel usage
- Advance their career in fields where data management and financial modeling are critical
Exam Details
The Exam 77-728: Microsoft Excel Expert (Office 2016) is designed for individuals operating at an advanced proficiency level, particularly those in business-related roles who require expert knowledge of Excel functionalities. This proctored assessment, which must be completed within a 50-minute time frame, evaluates a candidate’s ability to perform high-level tasks using Microsoft Excel 2016. It is a closed-book examination and may include interactive elements that simulate real-world usage of Excel features. To accommodate a global audience, the exam is available in multiple languages, including English, Simplified and Traditional Chinese, German, French, Spanish, Japanese, Korean, and Dutch.
Furthermore, the MOS 2016 Excel Expert exam introduces a performance-based testing model that focuses on real-world scenarios rather than theoretical knowledge. Key changes in the 2016 format include:
- Task-based Projects: Candidates work through multiple mini-projects, each designed to simulate tasks found in actual work environments.
- Descriptor-Based Instructions: Unlike earlier versions, task prompts no longer include direct command names or function names. Instead, they describe desired outcomes, requiring candidates to have a functional understanding of Excel’s tools and capabilities.
Course Outline
The exam covers the following topics:
1. Learn about managing workbook options and settings (10-15%)
Manage workbooks
- Save a workbook as a template, copy macros between workbooks, reference data in another workbook, reference data by using structured references, enable macros in a workbook, display hidden ribbon tabs (Microsoft Documentation: Copy a macro module to another workbook, Save a macro)
Managing workbook review
- Restrict editing, protect a worksheet, configure formula calculation options, protect workbook structure, manage workbook versions, encrypt a workbook with a password (Microsoft Documentation: Protect a worksheet, Protect a workbook)
2. Implementing custom data formats and layouts (20-25%)
Apply custom data formats and validation
- Create custom number formats, populate cells by using advanced Fill Series options, configure data validation (Microsoft Documentation: Create and apply a custom number format, Create a custom number format)
Applying advanced conditional formatting and filtering
- Create custom conditional formatting rules, create conditional formatting rules that use formulas, manage conditional formatting rules (Microsoft Documentation: Highlight patterns and trends with conditional formatting)
Create and modify custom workbook elements
- Create custom color formats, create and modify cell styles, create and modify custom themes, create and modify simple macros, insert and configure form controls (Microsoft Documentation: Customize or create new styles, Change a theme and make it the default in Word or Excel)
Prepare a workbook for internationalization
- Display data in multiple international formats, apply international currency formats, manage multiple options for +Body and +Heading fonts (Microsoft Documentation: Format numbers as currency)
3. Creating advanced formulas (35-40%)
Apply functions in formulas
- Perform logical operations by using AND, OR, and NOT functions (Microsoft Documentation: Using IF with AND, OR, and NOT functions in Excel)
- perform logical operations by using nested functions (Microsoft Documentation: Use nested functions in a formula, Using functions and nested functions in Excel formulas)
- perform statistical operations by using SUMIFS, AVERAGEIFS, and COUNTIFS functions
Look up data by using functions
- Look up data by using the VLOOKUP function, look up data by using the HLOOKUP function, look up data by using the MATCH function, look up data by using the INDEX function (Microsoft Documentation: VLOOKUP function, Look up values with VLOOKUP, INDEX, or MATCH)
Apply advanced date and time functions
- Reference the date and time by using the NOW and TODAY functions, serialize numbers by using date and time functions (Microsoft Documentation: TODAY function, DATE function, Date and time functions)
Perform data analysis and business intelligence
- Reference the date and time by using the NOW and TODAY functions (Microsoft Documentation: TODAY function, Date and time functions)
- import, transform, combine, display, and connect to data (Microsoft Documentation: Import data from a folder with multiple files)
- consolidate data (Microsoft Documentation: Combine data from multiple sheets)
- perform what-if analysis by using Goal Seek and Scenario Manager
- use cube functions to get data out of the Excel data model
- calculate data by using financial functions (Microsoft Documentation: Financial functions)
Troubleshoot formulas
- Trace precedence and dependence, monitor cells and formulas by using the Watch Window, validate formulas by using error checking rules, evaluate formulas (Microsoft Documentation: Display the relationships between formulas and cells, Detect errors in formulas)
Define named ranges and objects
- Name cells, name data ranges, name tables, manage named ranges and objects (Microsoft Documentation: Use the Name Manager in Excel)
4. Understand about creating advanced charts and tables (25-30%)
Create advanced charts
- Add trendlines to charts, create dual-axis charts, save a chart as a template (Microsoft Documentation: Create a chart from start to finish, Save a custom chart as a template, Add a trend or moving average line to a chart)
Creating and managing PivotTables
- Create PivotTables, modify field selections and options, create slicers, group PivotTable data, reference data in a PivotTable by using the GETPIVOTDATA function, add calculated fields, format data (Microsoft Documentation: GETPIVOTDATA function, Create a PivotTable to analyze worksheet data)
Create and manage PivotCharts
- Create PivotCharts, manipulate options in existing PivotCharts, apply styles to PivotCharts, drill down into PivotChart details (Microsoft Documentation: Create a PivotChart, Overview of PivotTables and PivotCharts)
Microsoft 77-728 Exam FAQs
Microsoft Certification Exam Policies
To uphold the credibility and global recognition of its certification program, Microsoft implements a robust set of exam policies. These guidelines are designed to ensure a secure, consistent, and equitable testing environment for all candidates, whether they choose to test remotely or at an authorized testing center. Adherence to these policies is essential for maintaining the high standards and professional integrity associated with Microsoft certifications.
Exam Retake Policy
Candidates who do not pass the exam on their first attempt are required to wait a minimum of 24 hours before retaking it. For any subsequent retakes (from the second through the fifth attempt), a 14-day waiting period is enforced between each session. Microsoft limits exam attempts to five within 12 months, beginning on the date of the first exam. This policy is intended to promote fairness and ensure that the certification remains a credible measure of skill and knowledge.
Rescheduling and Cancellation Guidelines
Microsoft offers candidates the flexibility to manage their exam appointments according to their schedules. Rescheduling or canceling an exam at least six business days in advance incurs no additional charges. However, changes made within five business days of the scheduled exam may be subject to a rescheduling fee. Failure to attend the exam or canceling less than 24 hours before the appointment will result in the forfeiture of the exam fee. In exceptional circumstances, such as emergencies or accessibility-related concerns, candidates may request exceptions by providing appropriate supporting documentation, which Microsoft reviews on an individual basis.
Microsoft 77-728 Exam Study Guide
Step 1: Review and Understand the Exam Objectives
Begin your preparation by thoroughly reviewing the official exam objectives published by Microsoft. These objectives serve as the foundation of the exam and outline the specific skills and tasks you’ll be tested on. For Exam 77-728, this includes advanced data management, custom formatting, using complex formulas, creating dynamic charts, and managing workbooks efficiently. Take time to understand each domain, identify your strengths and weaknesses, and align your study plan accordingly. Having a clear grasp of the required competencies ensures that your learning efforts are focused and relevant.
Step 2: Utilize Official Microsoft Training Resources
Leverage Microsoft’s self-paced learning modules and instructor-led training to build a solid understanding of the exam content. The self-paced resources, available through Microsoft Learn or official training partners, are structured for flexibility and allow you to study at your own rhythm. These modules often include hands-on exercises, examples, and guided walkthroughs that mirror real-world scenarios. Alternatively, instructor-led training is ideal for learners who benefit from direct interaction, structured lessons, and expert guidance. These courses are typically conducted by certified professionals and offer opportunities to ask questions and receive feedback in real time.
Step 3: Join Online Study Groups and Communities
Engaging with study groups and professional communities can enhance your learning experience. Online forums, discussion boards, and certification-focused groups (such as on LinkedIn, Reddit, or Microsoft Tech Community) provide valuable insights, shared experiences, and peer support. You can gain tips from certified individuals, ask for help on difficult topics, and stay updated on the latest exam trends. Collaborating with others also keeps you motivated and accountable throughout your study journey.
Step 4: Practice with 77-728 Exam Simulations and Sample Tests
Taking practice exams is a crucial part of effective preparation. These simulations help you familiarize yourself with the exam format, time constraints, and question types. They also help you assess your readiness and highlight areas that may need additional attention. Be sure to use reputable practice test providers that align closely with the actual exam structure. Review your results carefully, understand the rationale behind each correct answer, and revisit topics where performance was weak. Practicing under timed conditions also improves your ability to manage pressure during the actual exam.
Step 5: Reinforce Learning Through Hands-On Practice
Excel expertise cannot be achieved through theory alone. Regular hands-on practice is essential to mastering advanced features. Use real datasets to create pivot tables, custom charts, advanced formulas, and macros. Reproduce the types of spreadsheets mentioned in the exam scope, such as financial models, inventory logs, or amortization schedules. The more you apply your skills in realistic scenarios, the more confident and proficient you will become.
