How To Take This Class

Course Overview

In this Microsoft Excel 2013 Advanced training course, students will work with advanced functions and formulas, as well as lookup functions such as VLOOKUP, MATCH, and INDEX. In addition, students will learn about data validation, and will use advanced data filtering. They will apply advanced chart formatting options, and create more complex charts. They will work with PivotTables, PivotCharts PowerPivot, export and import data, and query external databases. Students will learn about the analytical features of Excel, such as Goal Seek, and how to create scenarios. Finally, they will run and record macros, and explore VBA code.

This course will also help students prepare for the Microsoft Office Specialist core-level exam for Excel 2013 (exam 77-420) and the Microsoft Office Specialist Expert exam for Excel 2013 (exam 77-421). For comprehensive certification training, students should complete the Introduction, Intermediate, and Advanced courses for Excel 2013. Students interested in certification will be provided with an online test preparation application for this course to help them prepare for the exam.

Course Prerequisites

Students should have attended, or have experience with the topics covered in the Microsoft Excel 2013 Introduction and Microsoft Excel 2013 Intermediate training courses.

Course Audience

The target students for this course are students who desire to gain the skills necessary to create templates, sort and filter data, import and export data, analyze data, and work with Excel on the web. In addition, this course helps prepare students who desire to take the Microsoft Office Specialist exam in Excel and who already have knowledge of the basics of Excel, including how to create, edit, format, and print basic worksheets.

What You'll Learn

After successful completion of this course students will be able to:
  • Use the IF, AND, OR, NOT, and IFERROR functions to calculate a value based on specified criteria; use conditional functions to summarize data; use the PMT function to calculate periodic payments for a loan; use text functions to extract data strings and modify text; use date functions to calculate duration in years, months, and days; create array formulas to perform multiple calculations on multiple sets of data at one time; and change calculation options and iteration limits.
  • Use the VLOOKUP, HLOOKUP, and LOOKUP functions to find values in worksheet data; and use data tables to project values.
  • Use the Data Validation feature to validate data entered in cells; and use advanced filter options to display the data you specify.
  • Format data points in charts; create combination charts and trendlines; insert sparklines; use chart templates; and add and modify drawing objects and shapes.
  • Create a PivotTable for analyzing and comparing large amounts of data; modify the PivotTable view by using slicers to filter data and by rearranging fields; improve the appearance of a PivotTable by changing its field settings and applying a style; create a PivotChart to graphically display data from a PivotTable; and use PowerPivot to create a PivotTable from imported data.
  • Export data from Excel to other formats, and import data from a text file into an Excel workbook.
  • Use the Goal Seek utility to meet a target output for a formula by adjusting the values in the input cells; create scenarios to save various sets of input values that produce different results; and analyze data instantly with Quick Analysis.
  • Run a macro to perform tasks automatically; record macros; assign a macro to a button in the worksheet; edit a macro by editing VBA code; copy a macro to another workbook.
  • Use the Accessibility Checker to resolve accessibility issues; insert international symbols, and install a different language.

Course Duration

1 Day

Course Outline

  1. Advanced functions and formulas
    • Logical functions
    • Conditional functions
    • Financial functions
    • Text functions
    • Date and time functions
    • Array formulas
    • Calculation options
  2. Lookups and data tables
    • Using lookup functions
    • Creating data tables
  3. Advanced data management
    • Validating cell entries
    • Advanced filtering
  4. Advanced charting
    • Chart formatting options
    • Combination charts
    • Graphical objects
  5. PivotTables and PivotCharts
    • Working with PivotTables
    • Modifying PivotTable data
    • Formatting PivotTables
    • Using PivotCharts
    • Using PowerPivot in Excel
  6. Exporting and importing data
    • Exporting and importing text files
  7. Analytical tools
    • Goal Seek
    • Scenarios
    • Instant data analysis
  8. Macros and Visual Basic
    • Running and recording a macro
    • Working with VBA code

What Students Receive

Each student will receive:
  • A printed courseware manual for use during and after class.
  • Access to an electronic version of the courseware manual for reference after class (available for 12 months after class)
  • 24/7 access via on-demand chat to a Microsoft Excel Instructor (available for six months after class)
  • Access to an online skills assessment to test your knowledge
  • A PC headset for communicating with your instructor and fellow students during your Microsoft Excel 2013 Training Class
  • A course completion certificate upon successful completion of your Microsoft Excel 2013 Training course