MOS Excel Expert 2016 Microsoft Certification Exam 77-728 from Udemy

UPDATED January 2021

Featured questions in the Q & A section of the course

UPDATED June 2019

Added additional project assignments

It is targeted to people, who want to succeed in the 77-728 exam.

The Course at a glance

In this course you will find:

52 assignments properly categorized
52 distinct files for each of the above assignments
52 solution videos for every assignment
10 project questions similar to the ones you will face in the exam
40 lectures explaining certain interesting subjects of the MOS 77-728 syllabus
2 lectures explaining the examination process and the structure of the multi-project question

The Course in detail

52 assignments properly categorized

In this course you will practice on the following categories:

1. Manage Workbook Options and Settings

2. Apply Custom Data Formats and Layouts

3. Create Advanced Formulas

4. Create Advanced Charts and Tables

5. Financial Functions

6. Features present in Excel 2016 Pro

7. Projects

52 distinct files for each of the above assignments

Each and every assignment comes with its distinct file(s) that are necessary to answer the assignment

52 solution videos for every assignment

All assignments are accompanied by detailed video solutions. We recommend you try to solve the assignments without viewing the solution. This way you will better understand your limitations and be able to focus on them. You can then see the video after you have solved it, to know if you answered the question correctly.

10 project questions similar to the ones you will face in the exam

Since the release of Office 2016, Microsoft introduced a new kind of examination. This examination consists of multiple project questions. We have included 10 such project questions in this course

Fast feedback from our instructors

We give feedback to all assignments, usually within one or two working days. Although in many cases we have been known to answer within a few hours.

What's inside

Learning objective

By completion of the course, the students will be well prepared to take the mos 77-728 exam

Syllabus

Introduction

How to submit an assignment

Manage Workbook Options and Settings

Copy Macros Between Workbooks

Protect a Worksheet

1257

1407

1439

1448

1528

1578

1990

1445

Apply Custom Data Formats and Layouts

Removing a custom number format

Apply Advanced Conditional Formatting using a formula

Work with Form Controls

Use the Accessibility Checker

1114

1455

1456

1457

1462

1860

1459

Create Advanced Formulas

The SUMIFS Function

The AVERAGEIFS Function

The COUNTIFS Function

The INDEX Function

The MATCH Function

The OFFSET Function

The VLOOKUP Function

The HLOOKUP Function

Advanced Lookup Formulas I (VLOOKUP-MATCH, VLOOKUP-HLOOKUP)

Advanced Lookup Formulas II (OFFSET-MATCH-MATCH)

Advanced Lookup Formulas III (INDEX-MATCH, INDEX-MATCH-MATCH)

Goal Seek

The TRANSPOSE Function

1074

1096

1263

1384

1409

1421

1486

1941

1970

1991

Create Advanced Charts and Tables

Pivot Tables

Calculated Fields and Items

Pivot Charts

Slicers

1039

1041

1044

1054

1281

1859

1917

1919

1988

This is a small practice test to help you dive in a little further into the CUBE functions. You have to do some research and studying to find the correct answer. No explanation or solution will be provided. Of course, if you can't find the answer feel free to ask and we would be happy to lead you to the proper resource.

We have decided to split these functions into a distinct category, to help you organize your studying better. We advise you to have the general idea about how they work before taking the exam.

Introduction to Financial Functions

The Functions PMT, IPMT, PPMT

The Functions PV, FV, NPER, RATE

The Functions NPV, XNPV

The Functions IRR, MIRR, XIRR

The Functions DOLLARFR, DOLLARDE

The Functions YIELD, YIELDDISC, YIELDMAT

The Functions DISC, RECEIVED, INTRATE

The Functions PRICE, PRICEMAT, PRICEDISC

The Functions ACCRINT, ACCRINTM

The Functions COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD

The Functions DURATION, MDURATION

The Functions SLN, SYD, DB, DDB, VDB

The Functions TBILLEQ, TBILLPRICE, TBILLYIELD

1489

1491

1498

1551

Consists of the features that are not included in the Office 365 version. Since it is not clear if these features are included in the syllabus we decided to include them in a separate category.

Power Pivot

Power Pivot Hierarchies

1047

1058

1436

1441

Multi-Project Question (as seen in the exam)

MOS 2016 exam procedure

MOS 2016 Multi-project questions description

Project 1 of 5: CAR-PART company sales

Project 2 of 5: Movie Fans video club rentals

Project 3 of 5: Hyper Market stock price analysis

Project 4 of 5: Theopetra Cave guest data

Project 5 of 5: PCI-X computer hardware company sales

Multi-Project Question 2 (as seen in the exam)

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Includes project questions similar to those found on the MOS 77-728 exam, providing targeted practice for the exam's format

Offers 52 assignments with distinct files and video solutions, which allows learners to practice and review their work

Covers advanced Excel features like Power Pivot and financial functions, which are essential for the MOS 77-728 exam

Focuses on Excel 2016, which may not be the latest version, so learners should verify if this version aligns with their needs

Requires learners to have access to Excel 2016, which may require a purchase if they do not already have it

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in MOS Excel Expert 2016 Microsoft Certification Exam 77-728 with these activities:

Review Excel Fundamentals

Show steps

Solidify your understanding of fundamental Excel concepts like cell referencing, basic formulas, and formatting to build a strong foundation for the advanced topics covered in the course.

Browse courses on Excel Fundamentals

Show steps

Review basic formulas (SUM, AVERAGE, COUNT).
Practice cell formatting and data entry.
Familiarize yourself with Excel's interface.

Review 'Microsoft Excel 2016 Step by Step'

Show steps

Use this book to reinforce your understanding of Excel concepts through hands-on exercises.

View Microsoft Excel 2019 Step by Step on Amazon

Show steps

Work through the exercises in the book.
Review the explanations and examples.
Apply what you learn to your own projects.

Review 'Excel 2016 Bible'

Show steps

Use this book as a reference to deepen your understanding of Excel features and functions.

View Excel 2016 Bible on Amazon

Show steps

Read chapters related to exam topics.
Work through examples in the book.
Use the book as a reference when needed.

Four other activities

Expand to see all activities and additional details

Show all seven activities

Practice Advanced Formulas

Show steps

Reinforce your understanding of advanced Excel formulas by working through practice problems that require you to apply these formulas in different scenarios.

Show steps

Solve problems using INDEX, MATCH, and OFFSET.
Practice using SUMIFS, AVERAGEIFS, and COUNTIFS.
Work through scenarios involving VLOOKUP and HLOOKUP.

Follow Advanced Charting Tutorials

Show steps

Enhance your charting skills by following online tutorials that demonstrate how to create advanced chart types like waterfall charts, box plots, and heatmaps.

Show steps

Find tutorials on advanced Excel charts.
Replicate the charts in Excel using sample data.
Experiment with different chart options.

Create a Financial Model

Show steps

Apply your knowledge of financial functions to build a financial model that calculates key metrics like NPV, IRR, and payback period.

Show steps

Gather data for a hypothetical investment.
Build a model to calculate NPV and IRR.
Analyze the results and draw conclusions.

Automate a Task with Macros

Show steps

Apply your knowledge of macros to automate a repetitive task in Excel, such as data cleaning, report generation, or data validation.

Show steps

Identify a repetitive task in Excel.
Record a macro to automate the task.
Edit the macro to improve its efficiency.
Test the macro and debug any errors.

Career center

Learners who complete MOS Excel Expert 2016 Microsoft Certification Exam 77-728 will develop knowledge and skills that may be useful to these careers:

Data Analyst

A data analyst uses software to organize, analyze, and visualize data, often identifying trends and opportunities. This course, with its focus on advanced formulas, charts, and tables, directly helps a data analyst perform their daily tasks. Proficiency in Excel, as developed through the assignments and projects in this course, is a core skill for this role. The content on financial functions and pivot tables helps someone in this position extract and present key insights from data, contributing to better decision-making.

See salaries and explore the career path for Data Analyst

Financial Analyst

Financial analysts use data to provide insights for investment decisions, financial planning, and risk assessment. This course helps a financial analyst develop the necessary Excel skills through its thorough coverage of financial functions like PMT, PV, NPV, and IRR. The course’s focus on advanced formulas and data manipulation techniques directly helps this role analyze financial data. The course's project questions, which simulate real-world scenarios, further prepare a financial analyst to tackle complex financial problems.

See salaries and explore the career path for Financial Analyst

Business Intelligence Analyst

A business intelligence analyst translates data into actionable insights to enhance business strategy and operations. This course provides a business intelligence analyst with the tools for their role, as it covers advanced Excel features such as pivot tables, charts, and data manipulation in depth. The course’s assignments and projects, along with lessons on data formatting and workbook management, help this role organize and analyze large datasets. The course curriculum, in particular, helps a business intelligence analyst present their findings effectively.

See salaries and explore the career path for Business Intelligence Analyst

Market Research Analyst

Market research analysts study market conditions to assess the potential sales of a product or service. The skills developed in this course supports a market research analyst by giving them tools to analyze market data, understand trends, and prepare reports. The course's section on data formatting and workbook management is useful for organizing large datasets. The advanced charting and table features taught in this course also enables them to present market analysis findings clearly and concisely.

See salaries and explore the career path for Market Research Analyst

Budget Analyst

Budget analysts help organizations manage their finances by preparing budget reports and monitoring spending. The course's coverage of financial functions in Excel, like PMT, PV, and NPV, helps a budget analyst forecast and analyze financial data. This course's emphasis on advanced formulas and data manipulation provides the essential skills needed for budget preparation and monitoring. The practical assignments and project questions contained in the course help prepare a budget analyst for challenges in their work.

See salaries and explore the career path for Budget Analyst

Operations Analyst

An operations analyst improves the efficiency of organizational processes through data analysis and process optimization. This course helps an operations analyst use Excel to manage and analyze operational data. The course materials on generating advanced charts and tables, along with data manipulation techniques, help in understanding and visualizing complex operational data. The project-based questions in the course help prepare an operations analyst to work with real-world datasets.

See salaries and explore the career path for Operations Analyst

Project Manager

Project managers plan, execute, and finalize projects, ensuring they meet deadlines and budget constraints. This course may be useful to a project manager by helping them create project plans, track timelines, manage resources, and report on progress. The course’s training on Excel’s data management and financial functions would assist in budget tracking and generating project status reports. Excel's capabilities, as taught in this course, also help improve efficiency and accuracy in project management.

See salaries and explore the career path for Project Manager

Accountant

Accountants prepare and analyze financial records, ensuring accuracy and compliance with regulations. This course may be useful for an accountant in their daily tasks, such as analyzing data, creating reports, and performing financial calculations. The course’s instruction on financial functions in Excel provides tools for financial analysis and reporting. The training on data manipulation and conditional formatting featured in the course may also help with auditing and reconciliation processes.

See salaries and explore the career path for Accountant

Risk Analyst

A risk analyst identifies and assesses potential risks to an organization. This course may be useful to a risk analyst to manage financial data and develop models. The course’s lessons on financial functions, along with the advanced formula training, are relevant to risk assessment and forecasting. The course activities on data visualization using charts and tables may help provide clear reports of potential risks.

See salaries and explore the career path for Risk Analyst

Management Consultant

Management consultants advise organizations on improving performance and efficiency. This course may be useful to a management consultant because it trains them to analyze data and generate insights. The course's material on data manipulation, along with training on advanced formulas and charts, can help a management consultant in their work. The project-based tasks included in the course may be valuable in training them prepare analyses.

See salaries and explore the career path for Management Consultant

Sales Operations Analyst

Sales operations analysts improve sales processes and analyze sales data to enhance performance. This course may be useful for a sales operations analyst, giving them the skills to manage large datasets, track sales metrics, and generate sales performance reports. The course helps one prepare sales reports. The course's focus on data management and manipulation, along with the sections on charting and pivot tables, are useful for analyzing sales trends and making data-driven recommendations. The course's training on project management may also be helpful in organizing and managing sales projects.

See salaries and explore the career path for Sales Operations Analyst

Research Associate

A research associate supports research projects by collecting, analyzing, and interpreting data. This course may be helpful to a research associate looking to use Excel to manage and analyze data. The course offers training in data formatting, advanced formulas, and generating charts. The course's training on data manipulation and the use of Excel tools contributes to the effectiveness of a research associate in their work.

See salaries and explore the career path for Research Associate

Statistician

Statisticians analyze and interpret numerical data and develop statistical models. This course may be useful to a statistician by increasing their Excel competency. The course offers training in data manipulation, the creation of charts and tables, and the management of workbooks. The course’s training may be helpful to a statistician who wants to use Excel, although their work tends to involve more specialized tools and languages. An advanced degree is typically required for this role.

See salaries and explore the career path for Statistician

Teacher

Teachers educate students in various subjects, developing curriculum and assessing student progress. This course may be useful for a teacher who wishes to use Excel for their own record keeping and administrative tasks, or for teaching basic spreadsheet skills to students. The course’s training materials on workbook management, conditional formatting, and data organization, may be helpful for teachers. This course may be useful in streamlining their administrative work. It may be helpful for those who teach subjects that require data analysis.

See salaries and explore the career path for Teacher

Office Manager

Office managers oversee administrative and operational tasks in an office, ensuring smooth daily operations. This course may be useful for an office manager. The course’s training on data management, workbook organization, and spreadsheet functions may be helpful for office administration, tasks such as generating reports and tracking office expenses. The course may help an office manager maintain organized records.

See salaries and explore the career path for Office Manager

Reading list

We've selected two books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in MOS Excel Expert 2016 Microsoft Certification Exam 77-728.

Excel 2016 Bible

Save

The 'Excel 2016 Bible' comprehensive guide to all things Excel. It serves as an excellent reference for understanding the features and functions covered in the MOS 77-728 exam. provides in-depth explanations, examples, and tips for mastering Excel's capabilities, making it a valuable resource for both beginners and advanced users. It is commonly used as a textbook at academic institutions.

Microsoft Excel 2019 Step by Step

Save

Provides a hands-on, step-by-step approach to learning Excel 2016. It's particularly useful for those who prefer learning by doing. The book covers a wide range of topics, from basic to advanced, and includes practice exercises and real-world examples. It is more valuable as additional reading than it is as a current reference, and is helpful in providing background knowledge.

Microsoft Excel 2019 Step by Step

Paperback

Microsoft Excel 2019 Step by Step

Kindle Edition

MOS Excel Expert 2016 Microsoft Certification Exam 77-728

What's inside

Learning objective

Syllabus

Good to know

Save this course

Activities

Career center

Reading list

Share

Similar courses