Microsoft Excel 2016: Part 3 (Expert Level)
4.5 (3 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
391 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Microsoft Excel 2016: Part 3 (Expert Level) to your Wishlist.

Add to Wishlist

Microsoft Excel 2016: Part 3 (Expert Level)

Become an Excel Superstar!
4.5 (3 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
391 students enrolled
Last updated 2/2017
English
English
Current price: $10 Original price: $25 Discount: 60% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 1.5 hours on-demand video
  • 6 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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.
View Curriculum
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

Excel is used in practically every business and in nearly all departments in those businesses – ranging from sales to accounting and administration. Many roles will require you to be proficient to this level.

The ability to collaborate with colleagues, automate complex or repetitive tasks, and use conditional logic to construct and apply elaborate formulas and functions will help you to create accurate worksheets and stand out from the crowd.

  • Work with multiple worksheets at the same time
  • Learn how large workbooks work and trace errors
  • Using the LOOKUP function are very useful, but can sometimes be a bit tricky so let's demystify and master them. 
  • Sparklines are small in cell charts. Learn how they allow you to compare data in a visual way and when they are better than Charts. 
  • Automate some repetitive tasks in Excel with macros.
  • If you have historical time-based data, you can use it to create a forecast.

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

Who is the target audience?
  • 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
38 Lectures
01:37:13
+
Working with Multiple Worksheets and Workbooks
7 Lectures 19:36

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.

Preview 02:11

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.

Preview 01:04

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.

Preview 05:37

Excel 2016 provides you with the ability to summarize the data from a series of worksheets by applying calculations across all of them.

Preview 00: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 References
04:23

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 Data - Introduction
00:31

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.
Consolidate Data
05:01
+
Using Lookup Functions and Formula Auditing
6 Lectures 17:27

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.

Preview 00:49

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.

Use Lookup Functions
08:14

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 Cells - Introduction
00:44

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.

Trace Precedent and Dependent Cells
02:58

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 Formulas - Introduction
00:44

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.

Watch and Evaluate Formulas
03:58
+
Sharing and Protecting Workbooks
5 Lectures 13:47
It is likely that multiple people will have some degree of input on some of your workbooks.
Preview 00:43

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.

Collaborate on a Workbook
05:32

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.

Preview 02:52

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 Workbooks
00:38

Learn how to use the number of options for protecting your worksheets and workbooks from unauthorized access or changes.

Protect Worksheets and Workbooks
04:02
+
Automating Workbook Functionality
7 Lectures 16:56

The single most important aspect of data analysis is having accurate data to analyze.

Preview 00:51

In Excel, you use data validation to restrict data entries in worksheet cells.

Apply Data Validation
04:16

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 Errors - Introduction
00:39

Learn how to check for errors and how to markup invalid data.

Search for Invalid Data and Formulas with Errors
02:59

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 Macros - Introduction
00:40

There are several options you can use to run macros once you've created them.

Work with Macros
05:17

You write and edit VBA code by using Microsoft's Visual Basic Editor, which is included with Excel 2016.

Edit a Macro
02:14
+
Create Sparklines and Map Data
4 Lectures 09:10
Sparklines display data visiually in a single cell. Used correctly, sparklines can be of great value.
Preview 00:34

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.

Preview 03:50

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 Data - Introduction
00:39

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.

Map Data
04:07
+
Forecasting Data
9 Lectures 20:17

Excel 2016 includes several features that can help you answer one simple question, "What if?" for a variety of possibilities.

Preview 00:44

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 Tables
05:21

If you need a whatif analysis with more than two variables you need to use SCENARIOS for your analysis.

Determine Potential Outcomes Using Data Scenarios - Introduction
00:22

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.

Determine Potential Outcomes Using Data Scenarios
05:06

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.

Preview 00:33

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.

Use the Goal Seek Feature
02:55

In Excel 2016, the new Forecast Sheet feature provides a simple method to help explain your data and predict future trends.

Forecast Data Trends - Introduction
00:15

In Excel 2016, the new Forecast Sheet feature provides a simple method to help explain your data and predict future trends.

Forecast Data Trends
03:25

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.

Course Follow-Up
01:36
About the Instructor
SONIC - High Quality Training for Over 20 Years
4.4 Average rating
356 Reviews
6,230 Students
51 Courses
Training Company

SONIC Performance Support is a leading provider of e-learning and performance support solutions. We proudly serve individuals, government agencies, schools and businesses of all sizes. SONIC Performance Support provides high-quality video training, that is used by well-known domestic and foreign companies. With video based training, you have a personal coach explaining and demonstrating the task at hand. You can stop the video, perform the task in the application, and rewind if you want to review.