Microsoft Excel 2013 Level 3

You will create advanced functions and formulas; lookup data using data tables, validating and filtering data, create charts and pivot tables/charts; import/export data; create scenarios and seek goals, work with basic VBA code and macros.

Upon successful completion of this course, students will be able to:

  • Use IF and IFERROR functions

  • Use VLOOKUP and HLOOKUP functions in concert with the MATCH and INDEX functions

  • Validate cell data and use advanced filtering tools to focus data

  • Create and format charts

  • Create Pivot Tables/Charts

  • Export/Import data to/from other MS Office® compatible formats

  • Use the Goal Seek utility

  • Run a macro and edit macros using basic VBA code

Course Outline

Unit 1: Advanced Functions and Formulas

  • Topic A: Logical Functions

  • Topic B: Conditional Functions

  • Topic C: Financial Functions

  • Topic D: Text Functions

  • Topic E: Date Functions

  • Topic F: Array Formulas


Lesson 2: Unit 2: Lookups and Data Tables

  • Topic A: Using Lookup Functions

  • Topic B: Using MATCH and INDEX

  • Topic C: Creating Data Tables


Lesson 3: Advanced Data Management

  • Topic A: Validating Cell Entries

  • Topic B: Advanced Filtering


Lesson 4: Advanced Charting

  • Topic A: Chart Formatting Options

  • Topic B: Combination Charts

  • Topic C: Graphical Objects


Lesson 5: Pivottables and Pivotcharts

  • Topic A: Working With Pivottables

  • Topic B: Rearranging Pivottables

  • Topic C: Formatting Pivottables

  • Topic D: Pivotcharts


Lesson 6: Exporting and Importing Data

  • Topic A: Exporting and Importing Text Files

  • Topic B: Getting External Data


Lesson 7: Analytical Tools

  • Topic A: Goal Seek

  • Topic B: Scenarios


Lesson 8: Macros and Visual Basic

  • Topic A: Running and Recording a Macro

  • Topic B: Working With VBA Code

Audience

This course is designed for persons who are able to create and modify worksheets and workbooks in Microsoft Excel 2013, but need to know how to create or modify documents using advanced tools and efficiency procedures. It also aims to assist persons preparing for the Microsoft Office Specialist exams for Microsoft Excel 2013.