Excel 2007 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; use the ROUND
  function to round off numbers; and use the PMT function to calculate periodic
  payments for a loan.
• Use the VLOOKUP function to find a value in a worksheet list; 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 list values that meet criteria you specify.
• Create a PivotTable for analyzing and comparing large amounts of data; change
  PivotTable views by moving fields and hiding and showing details; improve the
  appearance of a PivotTable by changing its field settings and applying a style;
  and create a PivotChart to graphically display data from a PivotTable.
• Export data from Excel to a text file, 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; install and use the Analysis ToolPak to
  perform statistical analysis; create scenarios to save various sets of input values
  that produce different results; and create views to save different sets of
  worksheet display and print settings.
• Run a macro to perform tasks automatically; record macros; assign a macro to a
  button, and use the button to run the macro; edit a macro by editing VBA code;
  and create a custom function to perform calculations when built-in functions are
  not available.
• Represent data graphically within cells by applying three forms of conditional
  formatting: data bars, color scales, and icon sets; and insert and modify SmartArt
  graphics.