Computer Magic Training Logo
Course Outline

Excel Lookups, Functions, and Macros Course


  • Excel Intermediate or the equivalent in work experience.

Advanced Functions in Excel

The training starts by taking you deeper into Excel's powerful data calculation capabilities. You will go beyond simple sums and use different types of targeted functions to get the answers you need. This section of the course covers how to:

  • Manipulate text strings in cells with text functions
  • Calculate the differences between two dates with the TODAY and NETWORKDAYS functions
  • Create an array formula to perform multiple calculates on multiple data sets
  • Apply math and statistical functions to calculate conditional sums and averages
  • Use financial functions, such as the PMT function, which calculates loan payment amounts

V Lookups and MATCH/INDEX Functions in Excel

This section of the Advanced Excel training class introduces built-in Excel functions for extracting specific values and information from ranges of data. Lookup functions help you find corresponding values in different rows or columns, for example, the fifteenth employee's fourth quarter earnings. You will learn how to:

  • Create lookup functions to find exact or approximate matches in lists of data
  • Use the MATCH function to determine a specified value's relative position in a list
  • Use the INDEX function to return a value based on a specified cell in a range
  • Create "what-if" scenarios with one-variable and two-variable data tables

Macros & Custom Excel Functions

Save time and reduce errors by automating your routine Excel tasks. Refine your mastery of macros and make yourself indispensable! You can customize and automate any procedure or calculation which you find yourself repeating regularly. You will learn how to:

  • Determine when a macro makes sense
  • Record and run macros that work for you
  • Enable and disable macros in a spreadsheet
  • Make changes to existing macros using the Visual Basic Editor

Exporting and Importing within Excel

XML is one of the most popular formats for data transfer, supported by most modern data-related applications. We'll provide in-depth exploration and hands-on experience with Excel's XML-related features. We'll also show you ways to save time by getting data into and out of Excel fast, such as text files and the Microsoft Query and Web Query features. You will learn how to:

  • Export your Excel data to a text file that can be read by other programs
  • Import data from a text file and organize it automatically into appropriate columns
  • Easily remove duplicate rows from your imported data
  • Use Microsoft Query to access data in external databases
  • Set up a Web Query to automatically pull data such as online currency quotes or stock quotes from the web

Analytical Options

Excel can help you make informed decisions with built-in data analysis tools like the Goal Seek and Solver utilities, the tools in the Analysis ToolPak, and the Scenario Manager. In this exciting part of the Advanced Excel training, you will discover how to:

  • Perform a simple one-variable "what-if" analysis using the Goal Seek utility
  • Perform complex "what-if" analyses with multiple variables using the Solver utility
  • Simplify your budget planning by creating and easily switching between Scenarios

Revised 1/14/16