Mar 21, 2019

MOS Excel 2016 Expert


I'm thankful that with the individual Learning Needs Analysis (LNA) in place in my current company (TÜV SÜD), I was able to indicate my interest in pursuing any courses/certifications of my choice (of course that would have to relate to my scope of work, or at least I would think so :D).

There is a whole pre-defined list of learning programs in the LNA form (which obviously does not interest me at all) alongside a section where I could fill in the programs which I'm interested in, subjected to my reporting officer's review and approval.

Below is what I filled in for that section:

Proposed Program Training Provider
Certified Quality Process Analyst (CQPA) American Society for Quality (ASQ)
Chartered Engineer (UK) Engineering Council (UK)
Microsoft Office Specialist (MOS) Excel 2016 Expert Chua Chu Kang Community Club IT Centre

Overview

  1. Manage workbook options and settings
    • Objective 1.1: Manage workbooks
      • Save a workbook as a template
      • Hide or display ribbon tabs
      • Enable macros in a workbook
      • Copy macros between workbooks
      • Reference data in another workbook
      • Reference table data by using structured references
    • Objective 1.2: Manage workbook review
      • Restrict editing
      • Protect workbook structure
      • Encrypt a workbook with a password
      • Manage workbook versions
      • Configure formula calculation options
  2. Apply custom data formats and layouts
    • Objective 2.1: Apply custom data formats and validation
      • Create custom data formats
      • Populate cells by using advanced Fill Series options
      • Configure data validation
    • Objective 2.2: Apply advanced conditional formatting and filtering
      • Create custom conditional formatting rules
      • Create conditional formatting rules that use formulas
      • Manage conditional formatting rules
    • Objective 2.3: Create and modify custom workbook elements
      • Create and modify cell styles
      • Create custom themes and theme elements
      • Create and modify simple macros
      • Insert and configure form controls
    • Objective 2.4: Prepare a workbook for internationalization
  3. Create advanced formulas
    • Objective 3.1: Apply functions in formulas
      • Insert functions into a formula
      • Perform logical operations by using the IF, AND, OR, and NOT functions
      • Perform logical operations by using nested functions
      • Perform statistical operations by using the SUMIFS, AVERAGEIFS, and
      • COUNTIFS functions
    • Objective 3.2: Look up data by using functions
    • Objective 3.3: 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
    • Objective 3.4: Perform data analysis and business intelligence
      • Import, transform, combine, display, and connect to data
      • Consolidate data
      • 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
    • Objective 3.5: Troubleshoot formulas
      • Insert functions into a formula
      • Perform logical operations by using the IF, AND, OR, and NOT functions
      • Perform logical operations by using nested functions
      • Perform statistical operations by using the SUMIFS, AVERAGEIFS, and
      • COUNTIFS functions
    • Objective 3.6: Define named ranges and objects
      • Name a cell or range
      • Name a table
      • Manage named ranges and objects
  4. Create advanced charts and tables
    • Objective 4.1: Create advanced charts
      • Add trendlines to charts
      • Create dual-axis charts
      • Save a chart as a template
    • Objective 4.2: Create and manage PivotTables
      • Create PivotTables
      • Modify PivotTable field selections and options
      • Create slicers
      • Group PivotTable data
      • Reference data in a PivotTable by using the GETPIVOTDATA function
      • Add calculated fields
      • Format data
    • Objective 4.3: Create and manage PivotCharts
      • Create PivotCharts
      • Modify PivotCharts
      • Drill down into PivotChart details

How did I prepare for MOS Excel 2016 Expert?

The step to getting certified in MOS Excel 2016 Expert is really simple - you just need to pass the 77-728 Exam (the passing score is 700/1,000).

Initially, I wanted to just attend the 3-day course with Chua Chu Kang Community Club IT Centre and sit for the exam directly with them. Unfortunately, there wasn't sufficient candidate to justify for a commencement of class (which is very common for every similar training provider), that's when I realized I need an alternate exam prep resources.

Owing to my experience preparing for PMP, I was pretty confident with learning from legitimate source of content from Udemy.

After going through some available MOS Excel 2016 Expert courses, I decided to purchase the following courses from Dave Murphy:
  1. 77-728 Complete Techniques for the MOS Excel Expert Exam (S$17.99)
  2. 77-728 MOS Expert Excel Practice Tests and Video Solutions (S$17.99)

Fundamentally, the first course is all you need to prepare for the 77-728 Exam, and it also comes with good amount of practice tasks.

For myself, I started preparing for the exam 10 to 14 days before the exam.

What kind of exam questions to be expected?

There are plenty of videos on YouTube with the instructors going through the practice tasks, and the actual exam questions are very similar to those. This is when the second course listed above comes in handy, where you can really time yourself completing the whole set of projects within the allowed duration (50 minutes).

I would advise one to go through a mock test if possible and jot down the areas where you need more practice (eg. many people may need more practice on conditional formatting, so they should look out for more practice with conditional formatting related tasks)

How did I do for the exam?

The exam is only delivered via a Certiport Authorized Testing Center (CATC). I took mine at Chua Chu Kang IT Centre, and it just happened to be very near where I live.

The exam costs S$149.80 for me (at least Chua Chu Kang IT Centre is transparent about that, where most of other testing centers are lack of such information to the public).

I completed the exam in about 25 minutes with a score of 963/1,000 (not to boast but I wasn't sure where did I not answer correctly though XD).



*NOTE: I've been using Excel pretty extensively, my experience may therefore differ from others'.

Javin Tham MSc, PMP, PMI-ACP, CSSBB, CQPA, CQIA, MSOE, MIPlantE Industrie 4.0 Consultant | Pop Piano Coach | YouTube Coverist