Author
Jennifer McBee
Updated
1/31/2019Released
1/27/2017The course begins with an overview of the certification program and its costs. Next, Jennifer walks you through all of the certification objectives, including hands-on experience with downloadable sample documents. She wraps up with a full-length practice test that emulates exam 77-728, together with solutions to each of the exam challenges.
- Identify two locations that offer users the ability to control how formulas are calculated.
- Recall two strategies for formatting decimal places.
- Recognize the importance of creating a combo box with specific values on a worksheet.
- Identify the purpose of the Trace Dependents Formula Auditing tool.
- Explain why the Scenario Manager is useful.
- List four options for grouping date values in a PivotTable.
Skill Level Advanced
Duration
Views
- [Instructor] Hi, my name is Jen McBee, and I'm here to help you prepare for the Microsoft Office Specialist Expert exam for Excel 2016. I'm a certified MOS master instructor, and I've been mentoring students in Microsoft Excel since 2006. We'll start with an overview of the different MOS certifications, and how to prepare for the exam. Next, we'll cover all of the objectives for the Excel 2016 Expert exam. We'll review how to manage workbook options, reference data in another workbook using query, create formulas using structured references and protect worksheets, ranges and workbooks.
We'll also learn about advanced functions such as sumifs, countifs and averageifs. We will learn how to create nested functions, how to use lookup functions and how to use date and time functions. We'll learn how to create named ranges, create and modify pivot tables and pivot charts and how to use the getpivotdata function. I will give you opportunities for hands-on practice throughout the course, and we'll finish with a full-length practice exam to test your skills and your speed.
When you can finish the practice exam in under 40 minutes, then you're probably ready to take on the live Excel 2016 MOS Expert exam. Thanks for joining me on this journey to certification.
-
Introduction
-
Welcome1m 21s
-
-
1. Microsoft Office Specialist (MOS) Expert Exam Overview
-
Prepare for the test2m 41s
-
2. Manage Workbook Options and Settings
-
Manage workbooks4m 45s
-
-
3. Apply Custom Data Formats and Layouts
-
Configure data validation5m 32s
-
4. Create Advanced Formulas
-
Use AND/OR functions7m 53s
-
Use IFS functions5m 19s
-
Use the LOOKUP function6m 58s
-
Use date and time functions6m 12s
-
Troubleshoot formulas6m 10s
-
Use What-If Analysis5m 23s
-
Cube functions7m 52s
-
-
5. Create Advanced Charts and Tables
-
6. Final Challenge
-
Conclusion
-
Next steps23s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Welcome