Microsoft

Microsoft Office Excel 2016 Expert (77-727 & 77-728)

Prepare for the Excel Certification Exam with 100+ video tutorials and 50+ Live Lab exercises.

(77-727-77-728) / ISBN : 978-1-64459-140-6
Lessons
Lab
TestPrep
AI Tutor (Add-on)
17 Reviews
Get A Free Trial

About This Course

Upgrade your excel skills with this Microsoft Excel 2016 Expert certification preparatory course. Gain a deep understanding of Excel's advanced features to manage complex data analysis and reporting tasks using Microsoft Excel 2016. This training course covers the Excel expert exam objectives focusing on how to create and optimize worksheets and workbooks, manage data cells and ranges, create tables, perform operations with formulas and functions, create charts and objects, and create advanced charts and tables. By the end of this Expert 77-727 & 77-728 certification exam training course you’ll learn how to apply advanced data analysis techniques to extract meaningful insights and create professional reports.

Skills You’ll Get

  • Ability to create, configure, review and manage multiple worksheets within a workbook
  • Apply custom data formats for data consistency and accuracy
  • Highlight data values or conditions with conditional formatting 
  • Data validation to prevent errors and maintain integrity
  • Complex formulas for logical, mathematical, statistical, and financial functions
  • Lookup functions (VLOOKUP, HLOOKUP, INDEX, MATCH) to retrieve data
  • Date and time functions for calculations and formatting
  • Summarize and analyze large datasets with PivotTables and PivotCharts 
  • Identify trends, patterns, and outliers with data analysis tools
  • Create and customize different chart types to visualize data
  • Insert and customize shapes, text boxes, and objects within the worksheets
  • Filter and sort data within tables
  • Define named ranges and simplify formula creation to automate tasks

Download Course Outline

1

Introduction

  • Course Overview
  • About 77-727 and 77-728 Exams
  • Prerequisites
2

Creating and Formatting Worksheets

  • Creating Worksheets and Workbooks
  • Navigating in Worksheets and Workbooks
  • Formatting Worksheets and Workbooks
  • Customizing Options and Views for Worksheets and Workbooks
  • Summary
3

Configuring and Managing Workbooks

  • Configuring Worksheets and Workbooks for Distribution
  • Managing Workbooks
  • Managing Workbook Review
  • Summary
4

Applying Custom Data Formats and Layouts

  • Applying Custom Data Formats and Validation
  • Applying Advanced Conditional Formatting and Filtering
  • Creating and Modifying Custom Workbook Elements
  • Preparing a Workbook for Internationalization
  • Summary
5

Managing Data Cells and Ranges

  • Inserting Data in Cells and Ranges
  • Formatting Cells and Ranges
  • Summarizing and Organizing Data
  • Summary
6

Creating Tables

  • Creating and Managing Tables
  • Managing Table Styles and Options
  • Filtering and Sorting Tables
  • Summary
7

Performing Operations with Formulas and Functions

  • Summarizing Data by Using Functions
  • Performing Conditional Operations by Using Functions
  • Formatting and Modifying Text by Using Functions
  • Summary
8

Creating Advanced Formulas

  • Applying Functions in Formulas
  • Looking up Data Using Functions
  • Applying Advanced Date and Time Functions
  • Performing Data Analysis and Business Intelligence
  • Troubleshooting Formulas
  • Defining Named Ranges and Objects
  • Summary
9

Creating Charts and Objects

  • Creating Charts and Objects
  • Formatting Charts
  • Inserting and Formatting Objects
  • Summary
10

Creating Advanced Charts and Tables

  • Creating Advanced Charts
  • Creating and Managing PivotTables
  • Creating and Managing PivotCharts
  • Summary

1

Creating and Formatting Worksheets

  • Creating a New Workbook
  • Inserting a Column
  • Creating a Workbook from a Template
  • Printing a Workbook
  • Importing Data from a File
  • Copying and Pasting Contents
  • Exploring the Format Task Pane
  • Auto-Hiding the Ribbon
  • Entering the Date and Time
  • Adding Worksheets to Workbooks
  • Understanding Cell References
  • Changing the Workbook View
  • Setting and Saving the Print Area
  • Changing the Width of the Column to Automatically Fit the Contents
  • Changing the Row Height to Fit Contents
  • Adding Commands to a New Group
  • Modifying a Worksheet
  • Adding New Commands to the Quick Access Toolbar
  • Hiding a Column
  • Formatting Numbers
  • Hiding or Unhiding the Content
2

Configuring and Managing Workbooks

  • Arranging Excel Windows
  • Protecting a Worksheet
  • Freezing the Top Row
  • Protecting a Workbook's Structure
3

Applying Custom Data Formats and Layouts

  • Changing Date Format
4

Managing Data Cells and Ranges

  • Entering Text and Values
  • Using Relative, Absolute, and Mixed References
  • Changing the Text Color
  • Using the Middle Align and Center Command
  • Formatting a Heading
5

Creating Tables

  • Deleting and Editing the Cell Content
  • Converting a Table to a Cell Range
  • Changing the Table Style
6

Performing Operations with Formulas and Functions

  • Using the UPPER Function
  • Using the PROPER Function
7

Creating Advanced Formulas

  • Using the INDEX Function
  • Using the MATCH Function
  • Using the DATE Function
  • Using the WORKDAY Function
  • Using the TODAY Function
  • Creating a Data Table
  • Creating Names for Cell Ranges
8

Creating Charts and Objects

  • Removing Gridlines and Headings
  • Changing the Chart Title
  • Inserting and Configuring a Shape
  • Inserting SmartArt
  • Inserting WordArt
  • Inserting and Modifying an Image
9

Creating Advanced Charts and Tables

  • Creating a PivotTable Automatically

Any questions?
Check out the FAQs

Get more insights about this advanced Microsoft Office Excel 2016 certification training here.

Contact Us Now

Both are separate Microsoft Office Specialist (MOS) exams conducted to validate your Excel 2016 skills. Exam 77-727 is the prerequisite for Exam 77-728 so you’ll need to pass both for this certification. While Exam 77-727 tests your basic Excel skills, Exam 77-728 demonstrates advanced Excel capabilities.

This course is especially beneficial for those from the finance, accounting, marketing, or data science backgrounds who need to work with large datasets for performing in-depth analysis.

This exam has a performance based format with different types of questions including multiple-choice, multiple select, fill-in-the-blank, drag and drop, and matching.

Yes, it is definitely worth your time and investment if you want to learn advanced Excel skills and techniques to improve your productivity and efficiency in data analysis and reporting.

Exam 77-727 has a limit of 90 minutes whereas exam 77-728 has a time limit of 110 minutes.

The exam fee is $100.

Related Courses

All Course
scroll to top