Excel 2007 Advanced

Excel 2007
Advanced

Topic-Level Outline

�� Days:� 1

�� Prerequisites:� Excel 2007: Intermediate or equivalent experience

�� Unit 1 :� Advanced functions

�� Topic A: 0� Logical functions

�� A-1:�� Using the IF function

�� A-2:�� Using OR, AND, and NOT as nested functions

�� A-3:�� Using nested IF functions

�� A-4:�� Using the IFERROR function

�� Topic B: 0� Math and statistical functions

�� B-1:�� Using SUMIF

�� B-2:�� Using COUNTIF

�� B-3:�� Using AVERAGEIF

�� B-4:�� Using SUMIFS, COUNTIFS, and AVERAGEIFS

�� B-5:�� Using ROUND

�� Topic C: 0� Financial functions

�� C-1:�� Using the PMT function

�� Topic D: 0� Displaying and printing formulas

�� D-1:�� Showing, printing, and hiding formulas

�� Unit 2 :� Lookups and data tables

�� Topic A: 0� Using lookup functions

�� A-1:�� Examining VLOOKUP

�� A-2:�� Using VLOOKUP to find an exact match

�� A-3:�� Using VLOOKUP to find an approximate match

�� A-4:�� Using HLOOKUP for exact matches

�� A-5:�� Using HLOOKUP for approximate matches

�� Topic B: 0� Using MATCH and INDEX

�� B-1:�� Using the MATCH function

�� B-2:�� Using the INDEX function

�� Topic C: 0� Creating data tables

�� C-1:�� Creating a one-variable data table

�� C-2:�� Creating a two-variable data table

�� Unit 3 :� Advanced list management

�� Topic A: 0� Validating cell entries

�� A-1:�� Observing data validation

�� A-2:�� Setting up data validation

�� Topic B: 0� Exploring database functions

�� B-1:�� Examining the structure of database functions

�� B-2:�� Using the DSUM function

�� Unit 4 :� PivotTables and PivotCharts

�� Topic A: 0� Working with PivotTables

�� A-1:�� Creating a PivotTable

�� A-2:�� Adding fields to a PivotTable

�� Topic B: 0� Rearranging PivotTables

�� B-1:�� Moving fields

�� B-2:�� Hiding and showing details

�� B-3:�� Refreshing the data in a PivotTable

�� Topic C: 0� Formatting PivotTables

�� C-1:�� Formatting by using a Pivot style

�� C-2:�� Changing field settings

�� Topic D: 0� PivotCharts

�� D-1:�� Creating a PivotChart

�� Unit 5 :� Exporting and importing

�� Topic A: 0� Exporting and importing text files

�� A-1:�� Exporting Excel data to a text file

�� A-2:�� Importing data from a text file into a workbook

�� A-3:�� Converting text to columns

�� A-4:�� Removing duplicate records

�� Topic B: 0� Exporting and importing XML data

�� B-1:�� Using the XML Source task pane

�� B-2:�� Importing XML data into a workbook

�� B-3:�� Exporting data from a workbook to an XML data file

�� B-4:�� Deleting an XML map

�� Topic C: 0� Querying external databases

�� C-1:�� Using Microsoft Query to get data from an external database

�� C-2:�� Discussing the Web query feature

�� C-3:�� Using a Web query to get data from the Web

�� Unit 6 :� Analytical options

�� Topic A: 0� Goal Seek and Solver

�� A-1:�� Using Goal Seek to solve for a single variable

�� A-2:�� Installing Solver and the Analysis ToolPak

�� A-3:�� Using Solver to solve for multiple variables

�� Topic B: 0� The Analysis ToolPak

�� B-1:�� Using the Sampling analysis tool

�� Topic C: 0� Scenarios

�� C-1:�� Creating scenarios

�� C-2:�� Switching among scenarios

�� C-3:�� Merging scenarios from another worksheet

�� Topic D: 0� Views

�� D-1:�� Creating views

�� D-2:�� Switching among views

�� Unit 7 :� Macros and custom functions

�� Topic A: 0� Running and recording a macro

�� A-1:�� Running a macro

�� A-2:�� Recording a macro

�� A-3:�� Assigning a macro to a button

�� Topic B: 0� Working with VBA code

�� B-1:�� Observing a VBA code module

�� B-2:�� Editing VBA code

�� Topic C: 0� Creating functions

�� C-1:�� Creating a custom function

�� Unit 8 :� Conditional formatting and SmartArt

�� Topic A: 0� Conditional formatting with graphics

�� A-1:�� Creating data bars

�� A-2:�� Using color scales

�� A-3:�� Creating icon sets

�� Topic B: 0� SmartArt graphics

� B-1:� Inserting a SmartArt graphic

� B-2:� Modifying a SmartArt graphic