Excel 2007 Advanced Study Guide

1-4239-1816-9 © 2008 Publish date: March 31, 2007

 

Microsoft Excel 2007 Training Courseware
Advanced Level	$25.99
<="style5 style8">** Unlimited Courseware Printing License:	Call 1-866-718-7246

 

Courseware ID: excel-2007-adv-sg

The final course in our three-part series of computer training books dedicated to teaching you how to use Excel 2007 is a highly-rated (4.9/5.0 in overall quality by ProCert Labs) course that builds on the basic and intermediate skills you learned in the first two books. The Excel 2007 Advanced study guide will teach you how to work with advanced formulas and a variety of functions including VLOOKUP, MATCH, and INDEX.

Like the other two study guides, the Microosoft Excel 2007 Advanced courseware is approved by Microsoft for the Microsoft certified Application Specialist program. These three courses prepare you for the Excel 2007 exam #70-602.

Learn advanced Excel skills and get completely comfortable using the 2007 version of the program. You will work with data validation and database functions like DSUM. In addition, you will work with PivotTables and PivotCharts, importing and exporting data, and querying external databases.

Finally, the Excel training courseware covers the analytical features of Excel including both Goal Seek and Solver, recording and running macros, and sharing your Excel data over the Internet. All of these advanced concepts are presented in eight comprehensive units featuring hands-on activities that reinforce all that you've learned.

Courseware Objectives

After completing this Excel 2007 courseware, you will be comfortable using the IF and SUMIF functions for calculating values based on specified criteria, using nested IF functions for evaluating complex conditions, using the ROUND function for rounding off numbers, and using the PMT function for calculating periodic payments for a loan.

You will also know how to find values in a worksheet list using the VLOOKUP function, find the relative position of a value in a range with the MATCH function, using the INDEX function for finding the value of a cell at a specific range position, ; and using data tables for projecting values.

Other topics include using Data Validation and various database functions to validate and summarize list values according to your specifications, creating PivotTables to analyze and compare large amounts of data, and creating PivotCharts to graphically display your PivotTable's data. You will learn about exporting data from Excel to text files, importing data from text files into a workbook, importing XML data, and exporting data to XML data files.

Other Excel 2007 courseware topics include using Microsoft Query and the Web query feature for importing data from external databases, using Goal Seek and Solver utilities, using the Analysis ToolPak for statistical analysis, and creating scenarios examine different input values and produce different results.

The advanced Microsoft Excel 2007 courseware shows you how to create views for saving different sets of display and print settings, how to work with SmartArt graphics, and how to use macros for a variety of advanced tasks. Finally, you will use three forms of conditional formatting - data bars, color scales, and icon sets - to represent data graphically within cells.

Courseware Contents

Unit 1 covers advanced functions

• Logical Excel functions
• Math and statistical functions
• Financial Excel functions
• How to display and print formulas

Unit 2 covers lookups and data tables

• Working with lookup functions
• Working with MATCH
• Working with INDEX
• Creating data tables

Unit 3 goes over advanced list management

• Validating your cell entries
• Exploring various database functions

Unit 4 covers PivotTables and PivotCharts

• Using PivotTables
• How to rearrange PivotTables
• Formatting your PivotTables
• Working with PivotCharts

Unit 5 goes over importing and exporting

• Importing and exporting text files
• Importing and exporting XML data
• Querying external databases

Unit 6 covers analytical options

• Working with Goal Seek and Solver
• Working with the Analysis ToolPak
• Scenarios
• Views

Unit 7 discusses macros and custom functions

• Running and recording macros
• Working with VBA code
• Creating functions

Unit 8 covers conditional formatting and SmartArt

• Conditional formatting with graphics
• SmartArt graphics