Call us at 1-800-413-0939

Excel 2016 Training

Over 100 metro locations. Attend face-to-face, remotely or on site at your facility.


Travel Packages Remote Attendance

Intermediate Microsoft Excel 2016: Functions, PivotTables, Macros and the VBA Environment

Duration: 1 day

Price: $175

Remote Attendance: Click here for more information.


Students Will Learn:

  • Creating advanced formulas
  • Analyzing data with logical and lookup functions
  • Organizing worksheet data with tables
  • Analyzing data with PivotTables, slicers and PivotCharts
  • Enhancing workbooks
  • Using and recording macros
  • Utilizing the VBA development environment to execute custom code

Microsoft Certified PartnerCourse Description: This course covers how to create and use advanced formulas, analyze data, organize worksheet data with tables, create macros, navigate the VBA development environment, and enhance workbooks. By the end of this course students should be comfortable in creating advanced formulas, analyzing data with functions, analyzing data using functions and PivotTables, working with tables, recording macros, using the VBA environment to execute custom code, and enhancing workbooks.

Comprehensive hands on exercises are integrated throughout to reinforce learning and develop real competency.

Course Prerequisites: Knowledge equivalent to attending the Introduction to Excel 2016 course.

Applying Range Names
  • Adding Range Names Using the Name Box
  • Adding Range Names Using the New Name Dialog Box
  • Editing a Range Name and Deleting a Range Name
  • Using Range Names in Formulas
Using Specialized Functions
  • Function Categories
  • The Excel Function Reference
  • Function Syntax
  • Function Entry Dialog Boxes
  • Using Nested Functions
  • Automatic Workbook Calculations
  • Showing and Hiding Formulas
  • Enabling Iterative Calculations
Using Text Functions
  • Text Functions
  • The LEFT and RIGHT Functions
  • The MID Function
  • The LEN Function
  • The TRIM Function
  • The UPPER, LOWER, and PROPER Functions
  • The CONCATENATE Function
  • The TRANSPOSE Function
Using Logical Functions
  • Logical Functions
  • Logical Operators
  • The AND Function
  • The OR Function
  • The IF Function
Using Lookup Functions
  • Lookup Functions
  • The LOOKUP Function
  • The VLOOKUP Function
  • The HLOOKUP Function
Using Date Functions
  • The TODAY Function
  • The NOW Function
  • Serializing Dates and Times with Functions
Using Financial Functions
  • The IPMT Function
  • The PPMT Function
  • The NPV Function
  • The FV Function
Creating and Modifying Tables
  • Table Components
  • The Create Table Dialog Box
  • The Table Tools – Design Contextual Tab
  • Styles and Quick Style Sets
  • Customizing Row Display
  • Table Modification Options
Sorting and Filtering Data
  • The Difference Between Sorting and Filtering
  • Sorting Data
  • Advanced Filtering
  • Filter Operators
  • Removing Duplicate Values
Using Subtotal and Database Functions to Calculate Data
  • SUBTOTAL Functions
  • The Subtotal Dialog Box
  • Summary Functions in Tables
  • Database Functions
Creating a PivotTable
  • PivotTables Overview
  • Start with Questions, End with Structure
  • The Create PivotTable Dialog Box
  • The PivotTable Fields Pane
  • Summarize Data in a PivotTable
  • The "Show Values As" Functionality of a PivotTable
  • External Data
  • PowerPivot
  • PowerPivot Functions
  • Filtering Data by Using Slicers
  • The Insert Slicers Dialog Box
Analyze Data with PivotCharts
  • PivotCharts Overview
  • Creating PivotCharts
  • Applying a Style to a PivotChart
Customizing Workbooks
  • Comments
  • Hyperlinks
  • Watermarks
  • Background Pictures
Managing Themes
  • About Themes
  • Customizing Themes
Creating and Using Templates
  • Templates
  • Template Types
  • Creating a Template
  • Modifying a Template
Protecting Files
  • Recovering Lost Data
  • The Changes Group
  • Worksheet and Workbook Protection
  • The Protect Worksheet Option
  • The Protect Workbook Option
Preparing a Workbook for Multiple Audiences
  • Displaying Data in Multiple International Formats
  • Utilize International Symbols
  • Modifying Worksheets Using the Accessibility Checker
  • Managing Fonts
Automating Tasks Using Macros
  • The Developer Tab
  • The Macros Dialog Box
  • Macro Security Components
  • Creating a Macro
  • The Record Macro Dialog Box
  • Creating Keyboard Shortcuts for Macros
  • Assigning Macros to Buttons
  • Managing Macros with the Organizer
Visual Basic for Applications (VBA)
  • What is VBA?
  • Navigating the VBA Development Environment
  • Executing Custom VBA Code
 
  • Course materials include student guide, hands-on lab manual and USB flash drive for examples and lab work
  • Students receive a certificate of completion at the end of class
  • Students can retake any portion of a class that has been completed, within 12 months at no extra cost
  • There are no registration fees or cancellation fees

Individual training passes: save up to 43%! For complete discount options » click here «

# Courses Total Cost Cost Per Course % Savings
2 $4,090 $2,045 22%
3 $5,490 $1,830 30%
4 $6,690 $1,672 36%
5 $7,790 $1,558 40%
6 $8,890 $1,482 43%

Multiple Training Locations - Convenience and Cost Control

Attend Regularly Scheduled Courses in More than 100 Cities Across North America and the United Kingdom or via Remote Attendance