Excel 2016 Advanced

Name: Excel 2016 Advanced
Rating: 4.4 (575 reviews)

Take Excel to the next level with advanced formulas, What-If Analysis tools, advanced PivotTables, Macros and much more

Created byIntellezy Trainers

Last updated 4/2018

English

What you'll learn

Use Advanced IF Statements
Use Advanced Lookup Functions
Use Complex Logical and Text Functions
Auditing Formulas
Working with What-If Analysis Tools
Protecting Worksheet and Workbook
Use Advanced PivotTables and PowerPivot Tools
Automate with Macros
Work with Form Controls
Ensure Data Integrity
Collaborate in Excel
Import and Export Data to a Text File

Course content

15 sections • 64 lectures • 2h 18m total length

Introduction1:04
Master advanced excel 2016 techniques, including sumif, averageif, countif, advanced lookups, and logical and text functions, plus formula auditing, simulation analysis, worksheet protection, Power Pivot, macros, and data integrity.

Summarizing Data with SUMIF4:27
Summarizing Data with AVERAGEIF2:35
Learn how to use averageif to compute the average of cells that meet a criterion. Explore averageifs for multiple criteria and verify results with named ranges and practical examples.
Summarizing Data with COUNTIF1:52
Learn to count cells that meet specific criteria using COUNTIF, explore COUNTIFS for multiple criteria, and apply these techniques to count items in rows.

Using VLOOKUP with TRUE3:40
Explore how to use VLOOKUP with TRUE in the range_lookup argument to perform approximate matches, finding the next highest value in a commission tier table.
Using HLOOKUP with TRUE1:22
Learn how to use HLOOKUP with an approximate match (true) to assign the correct letter grade from a grades table named range by looking up percent in the second row.
Using the INDEX Function3:09
Using the MATCH Function2:05
Combining INDEX and MATCH2:51
Comparing Two Lists with VLOOKUP1:42
Discover how to use VLOOKUP to compare two lists in Excel 2016 advanced. Identify items in list two that are missing from list one with exact match.
Comparing Two Lists with VLOOKUP and ISNA4:50
Explore how to compare two lists in Excel 2016 using VLOOKUP and handle NA errors with ISNA, IF, and IFERROR to display not found messages rather than errors.

Creating a Nested IF Function2:53
Explore how to build a nested if function in Excel 2016 to handle multiple conditions, using value_if_true and value_if_false with absolute references to calculate tiered commissions.
Using the IFERROR Function2:15
Using the LEN Function2:07
Explore how the len function counts characters, then subtract four from the total length and use the right function to extract the four-digit office code from employee IDs.
Using the TRIM Function1:03
Clean text data in Excel 2016 advanced by removing all extra spaces from a text string while preserving single spaces between words. Apply the TRIM function to a cell, then copy the formula down to obtain cleaned data from databases or other sources.
Using the SUBSTITUTE Function2:22

Showing Formulas1:01
Show formulas with the formulas tab or Ctrl+tilde to display all formulas in cells at once. Toggle the option to hide them again.
Tracing Precedents and Dependents2:33
Master tracing precedents and dependents in Excel to see which cells a formula uses and which cells it affects, and learn to clear the arrows after auditing.
Adding a Watch Window2:16
Monitor formula values in real time with the Excel watch window. Add watches to key cells across sheets and anchor the window to keep them visible while you work.
Error Checking1:55

Understanding Protection1:53
Encrypting Files with Passwords2:35
Protect worksheets and the workbook with passwords, choosing where to apply protection—from individual sheets to the entire workbook—and understand password handling to guard edits and access.
Allowing Specific Worksheet Changes1:27
Adding Protection to Selected Cells2:48
Protect specific cells by locking or unlocking them and hiding formulas, then shield the worksheet to control visibility and edits in Excel 2016 advanced.
Additional Protection Features1:45
Explore additional protection features in Excel 2016, including mark as final to make the workbook read-only, restricted access by user or group, and digital signatures to detect workbook changes.

Using the PivotTable and PivotChart Wizard2:02
Learn to enable and use the PivotTable and PivotChart Wizard in Excel 2016 advanced, add it to the quick access toolbar, and create a pivot table on a new worksheet.
Adding a Calculated Field1:33
Create a calculated field in a pivot table to compute commissions by multiplying sales totals by 12%, add it to the values area, and rename it to commission.
Adding a Calculated Item1:43
Create calculated items in a pivot table to compute team totals, such as team A (AD+CM+DS) and team B (NC+PT), without changing the field groupings.
Applying Conditional Formatting to a PivotTable2:35
Using Filters in a PivotTable1:42
Creating Filter Pages for a PivotTable1:16
Enabling the PowerPivot Add-In3:59

What are Macros?2:37
Displaying the Developer Tab2:41
Display and use the developer tab in Excel 2016 advanced, enabling VBA and macros, while understanding macro security and the default disable macros with notification.
Creating a Basic Formatting Macro2:58
Record a basic formatting macro in Excel, named Format_Totaux, assign Ctrl+Shift+F, and save it in this workbook to apply number formatting, fill color, and borders.
Running a Macro1:32
Learn how to run a macro in Excel 2016 advanced, using Ctrl+Shift+F or the Alt+F8 macro dialog, duplicate formatting, and understand that undo isn’t available after execution.
Assigning a Macro to a Button2:07
attach a macro to a form button to simplify macro execution for non-experts, using the developer tab, insert controls, and the assign macro option to link later.
Creating Complex Macros2:29
Viewing and Editing the VBA Code2:37
Navigate the VBA editor to view and edit macros, inspect Project Explorer and modules, and modify sub procedures like range selections; learn to save workbooks as macro-enabled to preserve macros.
Adding a Macro to the Quick Access Toolbar1:49

What are Form Controls?1:44
Explore form controls and ActiveX controls in Excel 2016 advanced, comparing easy data entry without VBA to flexible design with VBA, and enable developer tab to insert and customize controls.
Adding Spin Buttons and Check Boxes2:04
Explore inserting form controls in Excel 2016 advanced, including check boxes and spin buttons, using the Developer tab, resizing, aligning, copying, and assigning macros to actions.
Adding a Combo Box1:42

Requirements

Excel Introduction and Intermediate courses or equivalent experience.

Description

This course will teach students advanced concepts and formulas in Microsoft Excel 2016. Students will learn functions such as SUMIF, AVERAGEIF, and COUNTIF, advanced lookup functions such as HLOOKUP and VLOOOKP and MATCH and INDEX, and complex logical and text functions. Additionally, students will experiment with auditing formulas and error checking, use the What-If Analysis tools, learn the options for worksheet and workbook protection, review advanced use of PivotTables and PowerPivot add-in, work with Macros, use form controls, and ensure data integrity in their worksheets and workbooks. Students will also learn about Excel's many collaboration features, as well as how to import and export data to and from their workbooks.

With nearly 10,000 training videos available for desktop applications, technical concepts, and business skills that comprise hundreds of courses, Intellezy has many of the videos and courses you and your workforce needs to stay relevant and take your skills to the next level. Our video content is engaging and offers assessments that can be used to test knowledge levels pre and/or post course. Our training content is also frequently refreshed to keep current with changes in the software. This ensures you and your employees get the most up-to-date information and techniques for success. And, because our video development is in-house, we can adapt quickly and create custom content for a more exclusive approach to software and computer system roll-outs.

This course aligns with the CAP Body of Knowledge and should be approved for 2.25 recertification points under the Technology and Information Distribution content area. Email info@intellezy.com with proof of completion of the course to obtain your certificate.

Who this course is for:

Students who want to expand their Excel knowledge.

Excel 2016 Advanced

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 1min

Using Advanced IF Statements3 lectures • 9min

Using Advanced Lookup Functions7 lectures • 20min

Using Complex Logical and Text Functions5 lectures • 11min

Formula Auditing4 lectures • 8min

What-If Analysis Tools3 lectures • 8min

Worksheet and Workbook Protection5 lectures • 10min

Advanced Use of PivotTables and PowerPivot7 lectures • 15min

Automating with Macros8 lectures • 19min

Working with Form Controls3 lectures • 6min

Requirements

Description

Who this course is for: