Training Library
Microsoft Office Excel 2016

Microsoft Excel 2016 Video Training

Reveal trends and gain insights with
powerful analysis tools.

Microsoft Excel 2016 can help you organize, calculate, analyze, revise, update, and present your data  in ways that will help you or other decision makers in your organization steer you in the right  direction. 

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

Course Prerequisites

To ensure success, you need to be familiar with using personal computers and should have experience using a keyboard and mouse. You should also be comfortable working in the Windows environment.

Course Objectives

Upon successful completion of this course, you will be able to create and develop Excel worksheets and workbooks in order to work with and analyze the data that is critical to the success of your organization.

See a free preview of this course:

Download Table of Contents:

Part 1
Part 2
Part 3
Part 4
Part 5

Course Content Microsoft Excel 2016

Part 1: 50 Videos approximately 5-10 minutes in length totaling 2 hr 9 min of learning.



Getting Started with Excel 2016

Total (mm:ss): 34:25
  • Course Overview (02:26)
    My name is Alexander and I will provide you with all the knowledge you’ll need to create error-free and easy-to-read worksheets - from the beginner level to expert – from simply storing data to analyzing data in order to make profound business decisions. (Introduction, Table of Contents)
  • Navigate the Excel User Interface - Introduction (00:40)
    Before you can effectively use Excel 2016 to store and manage your data, you need to become familiar with the application's environment.
  • Navigate the Excel User Interface (06:18)
    In this topic, you'll examine Excel's interface, including its various commands and options, so that you can quickly and efficiently work with your data in the ways you see fit. (Spreadsheets, Worksheets, and Workbooks, Cells and Ranges, Cell Regions, Cell and Range References, The Excel User Interface, Excel Window Commands, The Backstage View, Basic Data Entry)
  • Use Excel Commands - Introduction (00:41)
    You'll need to be familiar with where to look to find the commands you need.
  • Use Excel Commands (04:14)
    You'll need to be familiar with where to look to find the commands you need. (The Ribbon, Tell Me, ScreenTips, The Quick Access Toolbar, Dialog Box Launcher, The Mini Toolbar and Context Menus)
  • Create and Save a Basic Workbook - Introduction (00:29)
    Learn how to save a workbook and how to save you workbook in previous excel versions, not everyone has Exycel 2016 and you want to provide a workbook in different file formats.
  • Create and Save a Basic Workbook (05:01)
    You'll find yourself creating a variety of different workbooks that you will need to save as separate items, as well as saving multiple versions of the same workbooks. (The New Tab, Excel 2016 File Formats, The Save and Save As Commands, Compatibility Mode, The Convert Option)
  • Enter Cell Data - Introduction (00:29)
    In this topic, you'll use fundamental Excel features to help you enter data.
  • Enter Cell Data (05:15)
    Excel has an incredible array of information types that you can work with in your workbooks. You'll need to be familiar with what these data types are and how Excel deals with them. (Data Types, The Undo and Redo Commands, The AutoFill Feature, AutoFill Options, Flash Fill, The Clear Command)
  • Flash Fill and Auto Fill (04:52)
    Learn more about time saving options you will apply every day. (Flash Fill, Auto Fill)
  • Use Excel Help - Introduction (00:47)
    Learn how to use Excel Help now could save you countless hours of research down the road and why you need help even after this training.
  • Use Excel Help (03:13)
    Learn how Microsoft Excel Help provides you with access to articles that can answer your Excel questions and show you how to perform various tasks within the application. (?, F1, The Excel 2016 Help Window)

Performing Calculations

Total (mm:ss): 19:22
  • Create Worksheet Formulas - Introduction (00:35)
    Excel formulas perform simple or complex mathematical computations in worksheets.
  • Create Worksheet Formulas (05:54)
    You can use formulas to perform tasks such as adding up a row or a column of numbers, multiplying sales figures by commission rates, and applying tax to sales. (Excel Formulas, The Formula Bar, Elements of Excel Formulas, Common Mathematical Operators, The Order of Operations, Reference Operators)
  • Insert Functions - Introduction (01:09)
    Learn how to use the massive set of built-in formulas.
  • Insert Functions (05:44)
    Learn how to use functions will save you time, effort, and possibly a few headaches down the road. (Functions, Basic Function Syntax, The Function Library Group, The Function Arguments Dialog Box, Graphical Cell and Range Reference Entry, The AutoSum Feature, Other Commonly Used Functions, The Formula AutoComplete Feature)
  • Reuse Formulas and Functions - Introduction (00:58)
    By reusing your content, you can eliminate the hassle and reduce the likelihood of entering numerous errors in your workbooks.
  • Reuse Formulas and Functions (05:02)
    Excel provides you with a number of methods to reuse nearly any of your content, including formulas and functions, basically by using a variety of copy and paste techniques. (Formulas and the Cut, Copy, and Paste Commands, Drag-and-Drop Editing, The Paste Options, The Paste Special Options, Relative References, Absolute References, Mixed References, AutoFill and Formulas, Worksheet References, Excel Errors)

Modifying a Worksheet

Total (mm:ss): 18:05
  • Insert, Delete and Adjust Cells, Columns and Rows - Introduction (00:17)
    Many times you’ll need to add a new column or row to your worksheet.
  • Insert, Delete and Adjust Cells, Columns and Rows (04:48)
    At some point in your data entry process, the amount of text you need to display in a cell may be more than the cell can hold or you may have to add a row or a column right in the middle of existing data. (The Insert and Delete Options, Width and Height Adjustments, The Hide and Unhide Commands)
  • Search for and Replace Data - Introduction (00:18)
    Learn how to find and correct mistakes throughout your workbooks quickly and easily.
  • Search for and Replace Data (06:18)
    Learn how to easily find and correct multiple errors in a worksheet contain thousands of rows' and columns' worth of data. (The Find Command, The Replace Command, The Go To Dialog Box)
  • Use Proofing and Research Tools (00:17)
    Learn how to make sure all of your content is correct before sharing some data with others.
  • Check the Spelling in a Worksheet (03:03)
    Learn how proofing tools can help you make sure your worksheets are accurate and look professional. (The Spelling Dialog Box, The AutoCorrect Feature)
  • Insights Task Pane (03:04)
    The Insights task pane uses the active cell's contents as a search query to run in Microsoft's Bing® search engine. (The Insights Task Pane, Smart Lookup)

Formatting a Worksheet

Total (mm:ss): 30:36
  • Apply Text Formats - Introduction (00:36)
    Excel 2016 provides you with a number of options to help you create spreadsheets that are easy to read and interpret, and that allow the important information to stand out.
  • Apply Text Formats (06:06)
    Learn how to add a level of visual appeal, which can facilitate a greater level of engagement when you present your data to others. (Fonts, The Font Group, Live Preview, The Format Cells Dialog Box, The Colors Dialog Box, Hyperlinks, The Format Painter)
  • Apply Number Formats - Introduction (00:57)
    By applying number formatting to your worksheet cells, you can control the display of items such as currency figures, dates and times, fractions, decimal places, and negative numbers.
  • Apply Number Formats (03:15)
    By understanding how the different number formats work, and by knowing how and when to apply them, you'll have the flexibility needed to work with and analyze all of your numeric data. (Number Formats, Number Format Categories, Custom Number Formats)
  • Align Cell Contents - Introduction (00:27)
    Excel 2016 provides you with the ability to control where your content appears horizontally and vertically within your worksheet cells.
  • Align Cell Contents (03:32)
    Familiarizing yourself with the text alignment options available in Excel 2016 will allow you keeping your organizational data orderly, readable, and functional. (Alignment Options, The Indent Commands, The Wrap Text Command, Orientation Options, The Merge & Center Options)
  • Apply Cell Styles and Themes - Introduction (00:23)
    Excel provides you with a number of options for quickly applying a variety of formatting options to your worksheet cells. This will help you consistently present data to your audiences in ways that are clear and instantly recognizable.
  • Apply Cell Styles and Themes (05:59)
    Learn how to apply a variety of formatting options to your worksheet cells, will not only save you time and effort, but can also help you consistently present data to your audiences in ways that are clear and instantly recognizable. (Cell Styles, Galleries, The Merge Styles Dialog Box, Themes, Custom Themes Considerations, Guidelines for Using Themes)
  • Apply Basic Conditional Formatting - Introduction (00:50)
    Using conditional formatting, Excel displays data that meets specified criteria with the specified formatting applied.
  • Apply Basic Conditional Formatting (03:38)
    Conditional Formatting can transform enormous sets of seemingly random bits of data into useful organizational intelligence that you can use to make sound decisions. (Conditional Formatting, The Highlight Cells Rules, The Top/Bottom Rules, Data Bars)
  • Create and Use Templates - Introduction (00:31)
    An Excel template is a file that contains a number of preconfigured or pre-entered workbook elements, such as formatting, formulas, themes, and functions, that you can use to create and work with new workbooks.
  • Create and Use Templates (04:22)
    Excel enables you to leverage your existing workbooks to create similar new workbooks. By taking advantage of this functionality, you can save a lot of effort, and research that went into creating your workbooks for future use. (Templates, Templates and the Backstage View)

Printing Workbooks

Total (mm:ss): 10:33
  • Preview and Print a Workbook (00:30)
    Although most information these days exists digitally, you will likely still, on occasion, require hard copies of a document. But spreadsheets are not the same as most other documents.
  • Configure and Preview a Print Job (03:02)
    Learn to use the variety of printing options that can save your document recipients from having to sift through mounds of irrelevant data. (The Print Tab, The Print Settings, Page Orientation, Margins, Print Preview)
  • Set Up the Page Layout - Introduction (00:21)
    Excel 2016 has many options for printing. Knowing what configurations are available and how to set them will allow you to create more visually helpful and appealing printouts.
  • Set Up the Page Layout (03:59)
    Learn what configurations are available and how to set them will allow you to create more visually helpful and appealing printouts. (The Page Setup Dialog Box, The Print Area, The Print Titles Command, Page Breaks, The Page Break Preview View)
  • Configure Headers and Footers - Introduction (00:28)
    Learn hot to add print-only elements quick and easy through the use of headers and footers.
  • Configure Headers and Footers (02:13)
    There's plenty of other identifying information you might want to include on all or most of your printed pages—like numbering each individual page, your company's name, the date the workbook was printed, and so on. (Headers and Footers, The Page Layout View, The Header & Footer Tools Contextual Tab, Header and Footer Options)

Managing Workbooks

Total (mm:ss): 16:02
  • Manage Worksheets - Introduction (00:17)
    Learn how to manage the overall structure of your workbooks.
  • Manage Worksheets (05:13)
    Workbooks with just a few worksheets are fairly easy to manage without much effort. But what if you're developing a workbook with dozens of worksheets or more? (Tab Formatting Options, Methods of Repositioning Worksheets, Methods of Inserting and Deleting Worksheets, The Hide and Unhide Worksheets Options)
  • Manage Workbook and Worksheet Views - Introduction (00:46)
    Learn how to leverage Excel's different view options, to be able to easily review data from different sources simultaneously.
  • Manage Workbook and Worksheet Views (04:35)
    With the View Options you can e.g. copy and paste data to the correct location without risking error, and save yourself time and headaches by avoiding scrolling through endless rows and columns of data. (Custom Views, The Add View Dialog Box, The Split Command, Syncronous Scrolling The Freeze Panes Options)
  • Manage Workbook Properties - Introduction (00:27)
    Workbook Properties can help you search through numerous workbook files to find precisely the one you need.
  • Manage Workbook Properties (03:15)
    A workbook property is, quite simply, a bit of information about a workbook file. Learn how workbook properties can help you to manage numerous workbook files. (Workbook Properties, Workbook Properties in the Backstage View, The Properties Dialog Box, Custom Workbook Properties)
  • Part 1 - Course Follow-Up (01:29)
    Hope you enjoyed this training and that I see you in the Microsoft Office Excel 2016: Part 2. (Thank You, What's next?, Table of Contents)

 

Part 2: 52 Videos approximately 5-10 minutes in length totaling 2 hr 26 min of learning. 


Working with Functions

Total (mm:ss): 08:00
  • Course Overview (02:27)
    In this training you learn averything that you’ll need to create error-free and easy-to-read worksheets - from the beginner level to expert – from working with Functions to analyze data in order to make profound business decisions. (Introduction, Table of Contents)
  • Work with Functions (01:04)
    Learn how to 'talk' to Excel at a higher level to get the most out of your data.
  • Work with Ranges (00:56)
    Learn how to ease your work by name ranges for use in functions and formulas.
  • Name and Edit Ranges (05:34)
    Cell names and range names are exactly what they sound like. They are meaningful names you assign to a given cell or range to make it easier to both understand what calculations are being performed in a formula and to reuse the references for a number of purposes. (Cell and Range Names, Names and the Name Box, Name Manager, The Create from Selection Command)
  • Use Defined Names in a Formula (03:12)
    Although it's certainly helpful to be able to name a range or a cell for easy navigation, the real power of this feature lies in the ability to easily identify references in formulas and to quickly and accurately insert references into multiple formulas. (Cell and Range Names in Formulas, Manually Entering Cell or Range Names, The Use in Formula Command Method, The Formula AutoComplete Method) 
  • Locate and Use Specialized Functions (05:06)
    Learn how to perform far more complex tasks in Excel beyond adding up rows and columns and multiplying the sum by some other figure. (Function Categories, The Excel Function Reference, Comparison Operator Basics, Function Syntax, The COUNTIF Function, The TODAY Function)
  • Work with Logical Functions (02:13)
    The logical functions in Excel provide a method for testing various conditions to calculate a result of a value, text, or a calculation which enable you to ask questions of your data.
  • IF Function (04:29)
    The IF function returns one value if the logical test you enter as an argument is true, and it returns a different value if the logical test is not true. (IF Function)
  • AND, OR and NOT Function (05:40)
    Learn how to combine logical tests. (AND Function, OR Function, NOT Function)
  • Combine Functions (06:18)
    The key to combining multiple calculations into a function in a single cell is nesting. Nesting is, simply, using a function as an argument within another function. (Nesting, Nested Function Syntax, Guidelines for Combining Functions)
  • Functions Similar to the IF Function (05:14)
    Learn how to use other powerful IF Functions. (Functions Similar to the IF Function (SUMIF, COUNTIF, AVARAGEIF))
  • Work with Date and Time Functions (01:24)
    Learn how to work with of some of the more commonly used date and time functions.
  • TODAY, NOW and DATE Functions (02:54)
    Learn how to master the most basic date and time functions. (TODAY, NOW, DATE)
  • NETWORKDAY, WEEKDAY, WORKDAY and ISOWeeknum Functions (05:28)
    Learn how to work with more advanced date and time functions - not only important to all, who frequently deal with scheduling and analyzing data for particular periods of time. (The NETWORKDAYS Function, The WEEKDAY Function, The WORKDAY Function, The ISOWeeknum Function)
  • Work with Text Functions (00:47)
    Excel's text functions let you manipulate text in cells to extract portions of text to other cells or combine them.
  • LEFT, RIGHT and MID Functions (02:40)
    This basic text functions let you manipulate text in cells to extract portions of text to other cells or combine them to produce e.g. full names or addresses. (The LEFT Function, The RIGHT Function, The MID Function)
  • FIND Function (03:19)
    To extract text from cells that contain values of varying characters, Excel also includes the FIND function. (The FIND Function)
  • Text to Columns Feature (03:38)
    This feature splits a single column of text into multiple columns. For example, you can create first and last name columns from one column of full names. (The Text to Columns Feature)
  • CONCATENATE Function (02:59)
    This function enables you to concatenate, or join together, text strings from multiple cells into a single cell. This function can save you massive amounts of time when you need to pull together data from multiple cells that already exists in your worksheets. (The CONCATENATE Function, Text Concatenation with the Ampersand, &) 
  • UPPER, LOWER and PROPER Functions (02:38)
    Excel contains three text functions that are very useful for formatting text to display it in the desired format. (The UPPER, LOWER and PROPER Function)

Working with Lists

Total (mm:ss): 14:33
  • Sort Data (00:30)
    By reordering your data, you can more easily locate and interact with specific entries, even in massive worksheets with tens of thousands of entries.
  • Sort Data (03:00)
    By reordering your data, you can more easily locate and interact with specific entries, even in massive worksheets with tens of thousands of entries. (Sorting, Clean Data, Multiple Column/Row Sorting, Quick Sorts, Custom Sorts)
  • Filter Data (00:36)
    In this topic, you will learn to filter data, which limits the rows of data you have to review in order to find what you are looking for.
  • Filter Data (02:29)
    When you filter data in Excel, you do not affect the actual data entries; you alter only how Excel displays your data. Learn how to use this feature to your advantage. (Filter, AutoFilters, Custom AutoFilters, Advanced Filtering, The Criteria Range, Filter Operators) 
  • Query Data with Database Functions (00:42)
    Similar to advanced filters, you can use database functions. These functions allow you to find the data you are looking for and perform calculations all in one step.
  • Database Functions (03:12)
    Learn how to query data with database functions. Database functions, essentially, combine the functionality of Excel functions with the functionality of advanced filters. (Database Functions, Database Function Syntax,) 
  • Outline and Subtotal Data (00:36)
    Learn how to outline and subtotal your data which may be especially useful when you are working on a spreadsheet with a large amount of data.
  • Use Subtotals to Summarize Data (03:28)
    Learn how to outline data and perform calculations on subsets of data. (Outlines, The SUBTOTAL Function, The Subtotals Feature) 

Analyzing Data 

Total (mm:ss): 18:26
  • Create and Modify Tables (00:44)
    By converting your raw data into tables, you will be able to take advantage of additional reporting features without affecting any of the data you have entered into your worksheets.
  • Create and Modify Tables (06:49)
    Excel tables contain robust functionality that enables you to organize, change the display of, and perform calculations on worksheet data quickly and easily. (Tables, Table Components, The Table Tools Design Contextual Tab, Table Styles and Quick Styles, Quick Analysis)
  • Use Summary Functions in Tables (03:21)
    Learn how to apply basic function to your data in tables. (Summary, Average, Count) 
  • Apply Intermediate Conditional Formatting (00:41)
    Learning additional conditional formatting options beyond the basics is the next step to help you analyze data trends.
  • Apply Intermediate Conditional Formatting (02:44)
    In addition to the preconfigured conditional formatting options available in Excel 2016, you have the option of creating completely custom conditional formats to suit your needs. A major step in becoming an Excel Expert! (Custom Conditional Formats, Rule Precedence) 
  • Apply Advanced Conditional Formatting (00:35)numerous columns based on criteria in a different column. 
  • Use Logical Functions to Apply Conditional Formatting (03:32)
    The tools available in Excel enable you to create specific rules you can use to apply conditional formatting and to tailor the display of conditionally formatted cells using an incredible array of options. (Use a Formula to Determine Which Cells to Format Rule, Cell References and Conditional Formatting, Guidelines for Applying Conditional Formatting to Cells Based on Values in Other Cells)

Visualizing Data with Charts 

Total (mm:ss): 23:20
  • Create Charts (00:38)
    Charts are a great way to interpret data, as many people need to see data visually to comprehend it better. Plotting data in charts can make spreadsheets less confusing when incorporated in your workbooks.
  • Create Charts (04:28)
    Learn how to create Charts, that help worksheet viewers to quickly and easily interpret the data in a worksheet. (Chart Basics, Chart Types, Chart Insertion Methods, Recommended Charts)
  • Modify and Format Charts (01:09)
    A well-formatted chart can mean the difference between simply delivering information and making an impact on your audience.
  • Modify and Format Charts (05:15)
    Excel 2016 provides you with a vast array of options when it comes to modifying and formatting your charts. (Modification vs. Formatting, Chart Elements, Chart Elements Guidelines, The Chart Tools Contextual Tab, The Format Task Pane, The Chart Tools Buttons) 
  • Use Advanced Chart Features (00:29)
    Excel 2016 includes a wide range of advanced charting features that enable you to display widely varying sets of data together, include forecasting trends on your charts. 
  • Create a Dual-Axis Chart (04:27)
    A dual-axis chart is, simply, a chart that displays two sets of information on the same chart. The main advantage to dual-axis charts is the ability to not only display two different sets of data simultaneously, but also to format the different sets of data independently of each other. (Dual-Axis Charts, Forecasting, Trendiness, Trendline Types, The Format Trendline Task Pane)
  • Create a Chart Template (03:02)
    Working with advanced charting features can require quite a bit of chart formatting and modification. And, it's likely you'll need to reuse at least some of your charts. (Chart Templates) 
  • Visualizing Data with Charts - Best Practice (03:52)
    If your data is misrepresented or presented ineffectively, key insights and understanding are lost, which hurts both your message and your reputation. (Order Data Appropiately, Space Bars Appropiately, Reduce Unnecessary Labeling, Increase Readability, Chart Type Considerations, Line Margins, Maintain Itegrity)

Using PivotTables and PivotCharts

Total (mm:ss): 22:05
  • Create a PivotTable (00:17)
    PivotTables combine some of the most powerful and useful types of Excel functionality, such as sorting, filtering, summary functions, and subtotals, to give you an incredible level of control over how you view your data.
  • Create a PivotTable (04:03)
    To take advantage of the functionality and flexibility of PivotTables, you must first understand how to create them. (PivotTables, Recommended PivotTables, The PivotTable Fields Task Pane)
  • Analyze PivotTable Data (00:38)
    Excel 2016 provides you with a number of different tools and commands you can use to organize the structure of your PivotTables. Knowing how these tools work and understanding how PivotTable structure translates into actionable intelligence are the keys to getting the answers you seek.
  • Analyze PivotTable Data (04:29)
    (The PivotTable Tools Contextual Tab, Summarize and Show Combinations, The GETPIVOTDATA Function)
  • Present Data with PivotCharts (00:50)
    Like standard Excel charts, PivotCharts are graphical representations of numeric values and relationships among those values. The main difference is simply that PivotCharts are linked to PivotTable data, whereas standard charts are linked to either a range of data or a table.
  • Present Data with PivotCharts (05:01)
    Learn how to translate your PivotTable data into PivotCharts. (PivotCharts, The PivotChart Fields Task Pane, PivotChart Filters)
  • Filter Data by Using Timelines and Slicers (00:54)
    Slicers are a tool that gives you a high level of control over PivotTable filtering. is easy to work with and easy to interpret regardless of how many filters you apply to your PivotTables.
  • Filter Data by Using Slicers and Timelines (04:18)
    Learn how to apply Slicers as a high level of control over PivotTable filtering, one that is easy to work with and easy to interpret regardless of how many filters you apply to your PivotTables and PivotCharts. (Slicers, The Slicer Tools Contextual Tab, The Report Connections Dialog Box, Timelines, The Timeline Tools Contextual Tab) 
  • Course Follow-Up (01:35)
    Hope you enjoyed this training and that I see you in the Microsoft Office Excel 2016: Part 3. In that course, you will build upon the skills you have acquired by working with multiple worksheets and workbooks, use lookup functions and formula auditing to troubleshoot your workbooks and fix errors, and to share and protect workbooks from unwanted changes. You will also learn to automate Excel functions, create sparklines and map data, as well as to forecast future data. 

Part 3: 38 Videos approximately 5-10 minutes in length totaling 1 hr 37 min of learning. 


Working with Multiple Worksheets and Workbooks

Total (mm:ss): 19:36
  • Course Overview (02: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 (01: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 References (05: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. (Linked Cells, External References in Formulas and Functions)
  • Use 3-D References (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)
    Learn how Excel 2016 provides you with the ability to summarize the data from a series of worksheets by applying calculations across all of them. (3-D References, Grouped Worksheets, 3-D References in Summary Functions)
  • Consolidate Data (00: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 Data (05: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. (Data Consolidation)

Using Lookup Functions and Formula Auditing

Total (mm:ss): 17:42
  • Use Lookup Functions (01:04)
    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 Functions (08: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. (INDEX, VLOOKUP, HLOOKUP)
  • Trace Precedent and Dependent Cells (00: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 Cells (02: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. (Trace Precedents, Trace Dependents)
  • Watch and Evaluate Formulas (00: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 Formulas (03: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. (The Watch Window, Formula Evaluation)

Sharing and Protecting Workbooks

Total (mm:ss): 13:47
  • Collaborate on a Workbook (00:43)
    It is likely that multiple people will have some degree of input on some of your workbooks.
  • Collaborate on a Workbook (05: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. (Comments, Shared Workbooks, Change Tracking, The Highlight Changes Dialog Box, Accept or Reject Changes, The Share Options)
  • Compare and Merge Workbooks (02: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. (Compare and Merge Workbooks)
  • Protect Worksheets and Workbooks (00: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 Workbooks (04:02)
    Learn how to use the number of options for protecting your worksheets and workbooks from unauthorized access or changes. (Element Protection, Protect Sheet, Protect Workbook, Password)

Automating Workbook Functionality

Total (mm:ss): 16:56
  • Apply Data Validation (00:51)
    The single most important aspect of data analysis is having accurate data to analyze.
  • Apply Data Validation (04:16)
    In Excel, you use data validation to restrict data entries in worksheet cells. (Data Validation, Data Validation Criteria, Input Messages, Error Alerts)
  • Search for Invalid Data and Formulas with Errors (00: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 Errors (02:59)
    Learn how to check for errors and how to markup invalid data. (The Circle Invalid Data Command, The Error Checking Command, Error Types)
  • Work with Macros (00: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 Macros (05:17)
    There are several options you can use to run macros once you've created them. (Macro Security Settings, Microsoft Visual Basic for Applications)
  • Edit a Macro (02:14)
    You write and edit VBA code by using Microsoft's Visual Basic Editor, which is included with Excel 2016.

Creating Sparklines and Mapping Data

Total (mm:ss): 09:10
  • Create Sparklines (00:34)
    Sparklines display data visiually in a single cell. Used correctly, sparklines can be of great value.
  • Create Sparklines (03: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. (Sparkling Types, The Sparkline Tools Contextual Tab)
  • Map Data (00: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 Data (04: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. (Data Structuring, 3D Map Elements, Tours)

Forecasting Data

Total (mm:ss): 20:17
  • Determine Potential Outcomes Using Data Tables (00: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 Tables (05: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. (WHAT- IF Analysis, Data Tables, One-Variable Data Tables, Two-Variable Data Tables)
  • Determine Potential Outcomes Using Data Scenarios (00: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 Scenarios (05: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. (Scenarios, The Scenario Manager Dialog Box, The Scenario Values Dialog Box)
  • Use the Goal Seek Feature (00: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 Feature (02: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. (Goal Seek, The Goal Seek Dialog Box, Iterative Calculations)
  • Forecast Data Trends (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)
    The result of using this feature is a new worksheet that shows you projected data to your forecast end in a table and a chart depicting the upper and lower bounds of the forecast. (The Forecast Sheet, The Create Forecast Worksheet Dialog Box)
  • Course Follow-Up (01:36)
    Because PivotTables are so powerful we created two training for you to learn more about Pivot Tables: Data Analysis with Pivot Tables and Data Analysis with PowerPivot.

Part 4 (Data Analysis with PivotTables): 21 Videos approximately 5-10 minutes in length totaling 1 hr 11 min of learning. 


Preparing Data and Creating Pivot Tables

Total (mm:ss): 27:39
  • Course Overview (01:20)
    Find out how to prepare data for PivotTable reporting and create PivotTables from various data sources, Analyze data by using PivotTables, Work with PivotCharts.
  • Prepare Data (00:37)
    In order to create a PivotTable that will effectively answer your questions, you need to ensure your data is in a format that will yield the desired results.
  • Prepare Data (05:35)
    In order to create a PivotTable that will effectively answer your questions, you need to ensure your data is in a format that will yield the desired results. Taking time to prepare your data before you create a PivotTable will help ensure you can drill down into your raw data and provide actionable intelligence for company leaders. By properly preparing your data for PivotTable analysis, you'll save yourself time and effort when it comes to actually analyzing that data. (Local Data Sources, Transactional Data, The need for Clean Data, Gap and Blank Cell Considerations)
  • Prepare Data (00:00)
  • Create a PivotTable (00:43)
    You can quickly create a basic PivotTable from a table or range within a workbook as the source data for your report.
  • Create a PivotTable from a Local Data Source (05:37)
    A PivotTable is a dynamic Excel data object that enables you to perform data analysis by reorganizing and summarizing data. (What are Pivot Tables, The Create PivotTable Dialog Box, The PivotTable Fields Task Pane, The PivotTable Tools Contextual Tab)
  • Create a PivotTable from Multiple Local Data Sources (07:06)
    Often you have data in multiple spreadsheets that you'd like to summarize. In this topic, you will create a PivotTable from multiple local data sources. (The Excel 2016 Data Model, Named Tables, The Create Relationship Dialog Box, The Manage Relationships Dialog Box)
  • Create a PivotTable from an External Data Sources (06:41)
    You won't always have source data in Excel ready to create a PivotTable. Many times, your source data is stored outside of Excel. Fortunately, Excel provides a way to create PivotTables from data sources outside of Excel. (Limitations of Excel as a Database, External Data Sources, The Select Data Source Window, The Import Data Dialog Box)

Analyzing Data by Using PivotTables

Total (mm:ss): 29:08
  • Summarize PivotTable Data (00:39)
    If you're looking for more than a sum or a count of the items in your fields or want to total only certain data in your PivotTable, you can customize how your data is presented and calculated using Value Field Settings.
  • Summarize Pivottable Data (07:17) addition to these default functions, Excel provides other summary options through two tabs in the Value Field Settings dialog box. (The Value Field Settings Dialog Box, The Show Values As Tab, The Insert Calculated Field Dialog Box)
  • Organize PivotTable Data (00:38)
    Excel makes it easy to adjust the display of PivotTable data to meet your business analysis needs. In this topic, you will summarize PivotTable data in various ways.
  • Organize PivotTable Data (04:08)
    For a PivotTable to be truly useful, you need to be able to read it and make sense of it quickly, so that you can make meaningful decisions from the data, sometimes it means simply to sort the data. (Sorting, The Expand and Collapse Feature, The Group and Ungroup Feature)
  • Filter Pivottable Data (06:52)
    Filtering PivotTable data comes in handy when you are analyzing a subset of your data and want to ignore all data that is not relevant to your current analysis. (The FILTERS Area, Slicers, The Insert Slicers Dialog Box, The Slicer Tools Contextual Tab, Timelines, The Timeline Tools Contextual Tab)
  • Format a PivotTable (00:28)
    Often, a well-constructed PivotTable can draw attention to issues and help springboard solutions. In this topic, you will format a PivotTable.
  • Format a PivotTable (02:35)
    After you've manipulated your PivotTable data, you're ready to present it in the best way. When you format a PivotTable, it's important to align the design efficiently to meet the business needs in question. Often, a well-constructed PivotTable can draw attention to issues and help springboard solutions. (Subtotals and Grand Totals, Styles)
  • Refresh and Change PivotTable Data (00:22)
    In this topic, you will refresh and change your PivotTable data, a necessary step if what if your source data changes.
  • Refresh and Change PivotTable Data (06:09) Menu, The Change PivotTable Data Source Dialog Box, The Connection Properties Dialog Box)

Working with PivotCharts

Total (mm:ss): 14:57
  • Create a PivotChart (00:41)
    Examining data visually helps you to easily see the story in the data. In this topic, you will create a PivotChart.
  • Create a PivotChart (03:53)
    A PivotChart is an interactive, graphical representation of numeric values and relationships among those values. Master examining data visually that helps you to easily see the story in the data. (PivotCharts, The Insert Chart Dialog Box, Chart Types)
  • Manipulate PivotChart Data (04:11)
    Instead of working with the PivotTable all the time to alter your PivotChart, you could just work directly in the PivotChart. (The PivotChart Fields Task Pane, The PivotChart Tools Contextual Tab)
  • Format a PivotChart (04:58)
    With PivotCharts, you are summarizing your data visually, so it's important to use layouts and colors that represent your data in ways that make it easy for users to interpret what the charts are portraying. (The PivotChart Tools Design and Format Tabs, Chart Formatting Buttons, The Format Task Pane)
  • Course Follow-Up (01:14)
    Whats the next step in becoming a real master of Microsoft Excel? (Microsoft Office Excel 2016: Data Analysis with PowerPivot)

Part 5 (Data Analysis with Power Pivot): 17 Videos approximately 5-10 minutes in length totaling 53 min of learning.


Getting Started with Power Pivot

Total (mm:ss): 16:16
  • Course Overview (01:39)
    Excel provides Power Pivot to help you organize, manipulate, and report on your data in the best way possible. Power Pivot is an add-in built by Microsoft that you can use to create sophisticated relationships among various data sources and perform powerful data analysis. (Trainer Introduction, Table of Contents)
  • Enable and Navigate Power Pivot (00:23)
    In Excel, you don't have access to Power Pivot unless you enable it
  • Enable and Navigate Power Pivot (04:26)
    Power Pivot is an add-in built by Microsoft that you can use to create sophisticated relationships among various data sources and perform powerful data analysis. (Power Pivot, Excel Add-Ins, The Power Pivot Tab, The Power Pivot Window, Navigating the PowerPivot Window)
  • Manage Data Relationships (03:06)
    Before you can analyze and report on your data, you must first get the data into Power Pivot to work with it.
  • Manage Data Relationships (06:42)
    For reporting to be accurate and function well, you must prepare the data for analysis by setting up relationships among tables and creating hierarchies. (The Excel 2016 Data Model, Data Sources, The Table Import Wizard, Diagram View, Relationships, Hierarchies)

Visualizing Power Pivot Data

Total (mm:ss): 10:34
  • Create a Power Pivot Report (00:16)
    Learn how to create a Power Pivot report and a Chart using the data in the Data Model.
  • Create a Power Pivot Report (05:07)
    Viewing rows and rows of data in disparate tables is exhausting, and it is nearly impossible to make any sense out of it. Data becomes meaningful only when you create a report, because you are able to organize and manipulate the data in ways that you can better analyze. (PivotTables, The PivotTable Fields Task Pane, PivotCharts, Power Pivot Report Options, The Create Report Dialog Box, Quick Explore, Slicers, The Report Connections Dialog Box)
  • Create Calculations in Power Pivot (00:28)
    With Power Pivot, you can create new columns and fields by performing calculations.
  • Create Calculations in Power Pivot (04:43)
    Once you have created a Power Pivot report, there may be additional information that you want to analyze, but that you don't have already in the Data Model. With Power Pivot, you can create new columns and fields by performing calculations with much more flexibilty than in Excel. (Calculated Columns, Calculated Fields, Explicit Calculated Fields, The AutoSum Feature,)

Working with Advanced Functionality in Power Pivot

Total (mm:ss): 27:03
  • Create a Key Performance Indicator (00:44)
    Excel provides a way to measure performance in your data with key performance indicators.
  • Create a Key Performance Indicator (05:26)
    In Power Pivot, key performance indicators (KPIs) are visual measures of performance - a great solution if you need to measure the performance of data against a target. (Key Performance Indicators (KPIs), The KPI Dialog Box, The Manage KPIs Dialog Box)
  • Work with Dates and Time in Power Pivot - Part 1 (03:05)
    Data Analysis Expressions (DAX), DAX Syntax, Time Intelligence
  • Work with Dates and Time in Power Pivot - Part 2 (04:19)
  • Work with Dates and Time in Power Pivot - Part 3 (04:40)
  • Work with Dates and Time in Power Pivot - Part 4 (04:31)
  • Create a Comparison Table with Power Pivot (03:05)
    Month over Month (MOM) or Year over year (YOY) is a common method of evaluating two or more measured events to compare the results at one time period with those of a comparable time period. Learn how to create these valuable comparisons.
  • Course Follow-Up (01:13)
    You have completed the Microsoft®Office Excel 2016: Data Analysis with Power Pivot course. What's next?

You may be also interested in this

  • Word 2016
  • Excel 2016
  • Outlook 2016
  • PowerPoint 2016
  • Access 2016
  • Windows 10
  • What´s new?
  • Office 365

Online Shop

After clicking 'Add to cart', please enter your email adress, name and your company name (if a company expense) and have your PayPal or Credit Card information ready. With your order you accept our General Business Terms and Conditions.

The 12-month online subscription allows access to the video training from any supported device (PC, iPad, Smartphones) connected to the Internet.