Excel 2016 Advanced

Coexcel 2013 iconurse Length

Full day

 Prerequisites

Excel 2016 Intermediate or equivalent experience

 

Course Description

This class is designed for the user interested in the more advanced features of Excel. Users will learn how to use data validation, perform what-if analyses; and create, run, edit, and delete macros. Custom views, formula auditing, array formulas, and PivotTables are also included.

Course Topics

  • Create a blank PivotTable from an Excel list or table
  • Work with the PivotTable Field List
  • Change the PivotTable Field List view
  • Add, remove, and rearrange fields in the PivotTable
  • Use Recommended PivotTables
  • Sort Data
  • Refresh a PivotTable and change the data source
  • Move, clear, and delete a PivotTable
  • Change number format of data fields
  • Change the calculation for value fields
  • Select cells with special criteria
  • Use the Fill command
  • Create and clear a validation rule and list
  • Circle invalid data and clear circles
  • Create, display, edit, and delete a scenario
  • Create a scenario summary report
  • Use goal seek
  • Create one and two-variable data tables
  • Display the Developer tab
  • Specify the macro security level
  • Enable macro content for a workbook
  • Save a workbook containing macros
  • Record and run macros & view macro code
  • Edit the shortcut key and/or description
  • Create a macro button on a worksheet
  • Edit and delete a macro button
  • Add a button to the Quick Access toolbar and assign an existing macro
  • Remove a command from and reset the Quick Access Toolbar
  • Change cell references in a macro from absolute to relative
  • Delete macros
  • Create, show, and delete a custom view
  • Create names and move to named cells
  • Use names in a formula
  • Manage names
  • Trace precedents and dependent cells
  • Remove precedent & dependent arrows
  • Evaluate and show formulas
  • Trace an error and check for common errors in formulas
  • Keep a watch on cells
  • Create an array formula
  • Combine duplicates
  • Consolidate data from multiple worksheets
  • Change calculation options
  • Require and remove a password to open or modify a workbook
  • Protect and unprotect a workbook and worksheet

What Participants Receive

Each participant receives a guide containing step-by-step instructions and practice exercises.