Excel 2013 Advanced

excel 2013 iconCourse Length

Full day

 Prerequisites

Excel 2013 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 view of the PivotTable Field List
  • Add, remove, and rearrange fields in the PivotTable
  • Create a PivotTable using 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 a validation rule and list
  • Circle invalid data, clear circles, and clear data validation
  • 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 the structure of a workbook
  • Protect and unprotect a worksheet

What Participants Receive

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