Excel 2010 Advanced

Target student

The target student for this course should be comfortable using a personal computer and
Microsoft Windows (preferably, Windows 7). Students should have some experience
with Excel 2010 and should be familiar with intermediate-level tasks, such as sorting
data, linking worksheets, and outlining and consolidating data. Students will get the
most out of this course if their goal is to become proficient in performing advanced
tasks, such as using logical, statistical, financial, and date functions, creating nested
functions, working with data tables, exporting and importing data, performing what-if
analyses, and recording macros.

After completing this course, students will know how to:

• Use the IF and SUMIF functions to calculate a value based on specified criteria;
  use a nested IF function to evaluate complex conditions; and use the ROUND
  function to round off numbers;
• Use the PMT function to calculate periodic payments for a loan; use Date and
  Time functions to calculate duration in years, months, and days or time; display,
  print, and hide formulas; 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 and HLOOKUP functions to find values in worksheet data;
  use the MATCH function to find the relative position of a value in a range; use
  the INDEX function to find the value of a cell at a given position in a range; and
  use data tables to project values.
• Use the Data Validation feature to validate data entered in cells; and use
  database functions to summarize data values that meet criteria you specify.
• Export data from Excel to other formats, and import data from a text file into an
  Excel workbook; import XML data into a workbook, and export data from a
  workbook to an XML data file; and use Microsoft Query and the Web query
  feature to import data from external databases.
• Use the Goal Seek and Solver utilities to meet a target output for a formula by
  adjusting the values in the input cells; use the Analysis ToolPak to perform
  statistical analysis; and create scenarios to save various sets of input values that
  produce different results.
• Run a macro to perform tasks automatically; record macros; assign a macro to a
  command button and a button in the worksheet; use a button to run the macro;
  create an Auto_Open macro; edit a macro by editing VBA code; and create a
  custom function to perform calculations when built-in functions are not
  available.