Excel 2007 Advanced
**Please note that CompuWorks will only accept students registering through a company or organization. Please click here for more information**
Description: Students will work with advanced formulas, as well as, lookup functions such as VLOOKUP, MATCH, and INDEX. In addition, students will learn about data validation and database functions such as DSUM. They will learn how to work with PivotTables and PivotCharts, how to import and export data, and how to query external databases. Finally, students will learn about the analytical features of Excel (such as Goal Seek and Solver), running and recording macros, and sharing Excel data via the Web.
Days: 1
Prerequisites: Excel 2007 Intermediate or equivalent experience
Unit 1: Advanced functions
Topic A: Logical functions
· Using the IF function
· Using OR, AND, and NOT as nested functions
· Using nested IF functions
· Using the IFERROR function
Topic B: Math and statistical functions
· Using SUMIF
· Using COUNTIF
· Using AVERAGEIF
· Using SUMIFS, COUNTIFS, and AVERAGEIFS
· Using ROUND
Topic C: Financial functions
· Using the PMT function
Topic D: Displaying and printing formulas
· Showing, printing, and hiding formulas
Unit 2: Lookups and data tables
Topic A: Using lookup functions
· Examining VLOOKUP
· Using VLOOKUP to find an exact match
· Using VLOOKUP to find an approximate match
· Using HLOOKUP for exact matches
· Using HLOOKUP for approximate matches
Topic B: Using MATCH and INDEX
· Using the MATCH function
· Using the INDEX function
Topic C: Creating data tables
· Creating a one-variable data table
· Creating a two-variable data table
Unit 3: Advanced list management
Topic A: Validating cell entries
· Observing data validation
· Setting up data validation
Topic B: Exploring database functions
· Examining the structure of database functions
· Using the DSUM function
Unit 4: PivotTables and PivotCharts
Topic A: Working with PivotTables
· Creating a PivotTable
· Adding fields to a PivotTable
Topic B: Rearranging PivotTables
· Moving fields
· Hiding and showing details
· Refreshing the data in a PivotTable
Topic C: Formatting PivotTables
· Formatting by using a Pivot style
· Changing field settings
Topic D: PivotCharts
· Creating a PivotChart
Unit 5: Exporting and importing
Topic A: Exporting and importing text files
· Exporting Excel data to a text file
· Importing data from a text file into a workbook
· Converting text to columns
· Removing duplicate records
Topic B: Exporting and importing XML data
· Using the XML Source task pane
· Importing XML data into a workbook
· Exporting data from a workbook to an XML data file
· Deleting an XML map
Topic C: Querying external databases
· Using Microsoft Query to get data from an external database
· Discussing the Web query feature
· Using a Web query to get data from the Web
Unit 6: Analytical options
Topic A: Goal Seek and Solver
· Using Goal Seek to solve for a single variable
· Installing Solver and the Analysis ToolPak
· Using Solver to solve for multiple variables
Topic B: The Analysis ToolPak
· Using the Sampling analysis tool
Topic C: Scenarios
· Creating scenarios
· Switching among scenarios
· Merging scenarios from another worksheet
Topic D: Views
· Creating views
· Switching among views
Unit 7: Macros and custom functions
Topic A: Running and recording a macro
· Running a macro
· Recording a macro
· Assigning a macro to a button
Topic B: Working with VBA code
· Observing a VBA code module
· Editing VBA code
Topic C: Creating functions
· Creating a custom function
Unit 8: Conditional formatting and SmartArt
Topic A: Conditional formatting with graphics
· Creating data bars
· Using color scales
· Creating icon sets
Topic B: SmartArt graphics
· Inserting a SmartArt graphic
· Modifying a SmartArt graphic
Price: $295
Or you can purchase our CW vBook training:
Or you can purchase our CW vBook training:
