Name: Microsoft Excel 2016: Part 3 (Expert Level)
Rating: 4.4 (16 reviews)

Udemy Business

Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Created bySONIC Performance Support - E-Learning Experts

Last updated 5/2019

English

What you'll learn

Become an Excel expert
Learn to perform advanced data analysis
Collaborate on workbooks with other users
Automate workbook functionality

Course content

6 sections • 38 lectures • 1h 37m total length

Course Overview2:11
Upon successful completion of this course, you will be able to perform advanced data analysis, collaborate on workbooks with other users, and automate workbook functionality.
Use Links and External References - Introduction1:04
Excel 2016 enables you to create formulas and functions that link data from multiple worksheets and workbooks in order to create a worksheet or workbook summary, all the while allowing you to maintain and view your original data.
Use Links and External References5:37
Excel 2016 provides you with the ability to connect one cell to the data entered into another cell. When you create this connection, you create a linked cell.
Use 3-D References - Introduction0:49
Excel 2016 provides you with the ability to summarize the data from a series of worksheets by applying calculations across all of them.
Use 3-D References4:23
Excel 2016 provides you with the ability to summarize the data from a series of worksheets by applying calculations across all of them.
Consolidate Data - Introduction0:31
Data consolidation enables you to summarize data from multiple worksheets regardless of whether or not the data is in the same location on each worksheet, or even if the worksheets are in the same workbook.
Consolidate Data5:01
Excel 2016 enables you to consolidate data based on either relative cell positions in the various source datasets or by categories, which are based on row and column labels.
Quiz 1 - Working with multiple worksheets and workbooks

Use Lookup Functions - Introduction0:49
By using a set of functions known as Lookup functions, you'll be able to look up or include in a formula or function any one particular entry in any dataset.
Use Lookup Functions8:14
Lookup functions search through a particular dataset to return a particular value based on some criteria. Although you could search for data and look up the value yourself, you wouldn't want to do this for multiple records over and over.
Trace Precedent and Dependent Cells - Introduction0:44
You need to learn and master tools to systematically check the data, formulas, or functions in only the cells that affect the erroneous result.
Trace Precedent and Dependent Cells2:58
Excel provides you with a clear, graphical method for determining precisely how the cells in your workbooks connect to one another helping you to find errors in your worksheet: cell tracing. This feature helps you to avoid reading over the content in numerous cells to track which other cells feed into them can be a painstaking and error-prone process.
Watch and Evaluate Formulas - Introduction0:44
Excel provides you with a couple of powerful tools that can help you watch formulas and their results and to break down complex functions argument-by-argument to home in on error in your worksheet.
Watch and Evaluate Formulas3:58
When you develop and work with workbooks that contain large numbers of complex functions or that have a lot of interconnected cells, it becomes tricky to fully troubleshoot and resolve all problems.
Quiz 2 - Using Lookup Functions and Formula Auditing

Collaborate on a Workbook - Introduction0:43
It is likely that multiple people will have some degree of input on some of your workbooks.
Collaborate on a Workbook5:32
Learn the essential tasks to be able to collaborate with colleagues, provide and receive feedback on workbooks, and ensure that everyone's input is reflected in the final version of your documents.
Compare and Merge Workbooks - Introduction2:52
If you are collaboratively working on a shared workbook that is not saved in a central location, such as OneDrive, you will still need to include the work other users contribute in the master copy of the workbook.
Protect Worksheets and Workbooks0:38
As you share your workbooks with more and more people, or as you collaborate on workbook files with others, you face an increased risk of someone accessing, modifying, or deleting your data without authorization.
Protect Worksheets and Workbooks4:02
Learn how to use the number of options for protecting your worksheets and workbooks from unauthorized access or changes.
Quiz 3 - Sharing and Protecting Workbooks

Apply Data Validation0:51
The single most important aspect of data analysis is having accurate data to analyze.
Apply Data Validation4:16
In Excel, you use data validation to restrict data entries in worksheet cells.
Search for Invalid Data and Formulas with Errors - Introduction0:39
In Excel INVALID DATA is any cell data that does not meet the criteria specified in data validation applied to the cell.
Search for Invalid Data and Formulas with Errors2:59
Learn how to check for errors and how to markup invalid data.
Work with Macros - Introduction0:40
A macro is in its simplest terms a series of steps or instructions that you can run from a single command or action.
Work with Macros5:17
There are several options you can use to run macros once you've created them.
Edit a Macro2:14
You write and edit VBA code by using Microsoft's Visual Basic Editor, which is included with Excel 2016.
Quiz 4 - Automating Workbook Functionality

Create Sparklines - Introduction0:34
Sparklines display data visiually in a single cell. Used correctly, sparklines can be of great value.
Create Sparklines3:50
With sparklines, you can combine the benefits of storing massive amounts of data on very large worksheets with the ability to discern important information and identify trends with just a glance.
Map Data - Introduction0:39
In previous versions of Excel, there was no method of showing the relationship of that data geographically over time. Fortunately, Excel 2016 now has a built-in feature that does just that, 3D Map.
Map Data4:07
Learn how to visualize your data on a map by plotting geographic and temporal or time-related data on a 3-D globe or custom map, show it over time, and create visual tours you can share with other people.
Quiz 5 - Creating Sparklines and Mapping Data

Determine Potential Outcomes Using Data Tables - Introduction0:44
Excel 2016 includes several features that can help you answer one simple question, "What if?" for a variety of possibilities.
Determine Potential Outcomes Using Data Tables5:21
What-if analysis enables you to perform calculations on the same formula or formulas with one or more variables included at a number of different values.
Determine Potential Outcomes Using Data Scenarios - Introduction0:22
If you need a whatif analysis with more than two variables you need to use SCENARIOS for your analysis.
Determine Potential Outcomes Using Data Scenarios5:06
Scenarios change the displayed values of both the cells containing the variables, which are known as changing cells in scenarios, and the cells with the formulas or functions fed by the variables.
Use the Goal Seek Feature - Introduction0:33
The WHATIF and the SCENARIO helps you to find a result based on your variables. The GOAL SEEK Feature allows you to do the opposite.
Use the Goal Seek Feature2:55
The Goal Seek feature is a type of what-if analysis tool that enables you to calculate the value of one input in order to arrive at a specific outcome.
Forecast Data Trends - Introduction0:15
In Excel 2016, the new Forecast Sheet feature provides a simple method to help explain your data and predict future trends.
Forecast Data Trends3:25
In Excel 2016, the new Forecast Sheet feature provides a simple method to help explain your data and predict future trends.
Quiz 6 - Forecasting Data
Bonus Lecture1:36
Congratulations. You finished Excel 2016: Part 3 (Expert Level). Because PivotTables are so powerful we created two more courses for you to learn more about Pivot Tables: Data Analysis with Pivot Tables and Data Analysis with PowerPivot.
Click here to go directly to the next level: Excel 2016: Data Analysis with PivotTables
https://www.udemy.com/pivottablesandpivotcharts/

Requirements

This course is intended for students who are experienced Excel 2016 users and have a desire or need to advance their skills in working with some of the more advanced Excel features.

Description

COURSE DESCRIPTION

Would you like to become an Excel specialist and use the enormous functionality of Excel 2016 to your advantage? In this expert course, you will get to know especially helpful features, such as collaborative editing of workbooks and management of more complex Excel documents, through the implementation of tools such as formula tracking and reference functions. The ability to collaborate with colleagues, automate complex or repetitive tasks, and use conditional logic to construct and apply elaborate formulas and functions will put the full power of Excel right at your fingertips. The more you learn about how to get Excel to do the hard work for you, the more you'll be able to focus on getting the answers you need from the vast amounts of data your organization generates.

METHOD

The trainer explains step by step all important and useful functionalities and features for working with Microsoft Excel. Benefit from the extensive additional materials that are integrated into the training. Look forward to practice files, handouts and quizzes.

THIS COURSE IS SUITABLE FOR YOU IF

you need to troubleshoot large, complex workbooks
you want to automate repetitive tasks
you need to construct complex Excel functions and use those functions to perform rigorous analysis of extensive, complex datasets

PREREQUISITES

This course is intended for students who are experienced Excel 2016 users and have a desire or need to advance their skills in working with some of the more advanced Excel features.

COURSE OBJECTIVES

Upon successful completion of this course, you will be able to perform advanced data analysis, collaborate on workbooks with other users, and automate workbook functionality. You will:

Work with multiple worksheets and workbooks
Use Lookup functions and formula auditing
Share and protect workbooks
Automate workbook functionality
Create sparklines and map data
Forecast data

This course covers Microsoft Office Specialist exam objectives to help students prepare for the Excel 2016 Exam and the Excel 2016 Expert Exam.

Who this course is for:

Those who need to troubleshoot large, complex workbooks, automate repetitive tasks, engage in collaborative partnerships involving workbook data, construct complex Excel functions, and use those functions to perform rigorous analysis of extensive, complex datasets.

What you'll learn

Explore related topics

Course content

Working with Multiple Worksheets and Workbooks7 lectures • 20min

Using Lookup Functions and Formula Auditing6 lectures • 17min

Sharing and Protecting Workbooks5 lectures • 14min

Automating Workbook Functionality7 lectures • 17min

Create Sparklines and Map Data4 lectures • 9min

Forecasting Data9 lectures • 20min

Requirements

Description

Who this course is for: