Advanced Microsoft Excel Training 2007

Day 1 of Advanced Excel 2007

Lesson 1: Custom & Conditional Formats

• Creating a Custom Format
• Create a custom number format
• Conditional Formatting
• Creating Conditional Formatting
• Editing Conditional Formatting
• Adding Conditional Formatting
• Deleting Conditional Formatting from the Selected Range

Lesson 2: Using Formulas & Functions

• Relative Cell Reference
• Absolute Cell Reference
• Using Text Functions
• Left Function
• Upper Function
• Concatenating the text values
• Date Functions
• Using Today Function
• Calculating using Date Function
• Using Sum Functions
• Using CountA Functions

Lesson 3: Working with Range Names

• Using Range Names
• Creating Range Names
• Using Range Names in Formulas
• Creating Range Names from Headings
• Deleting Range Names
• Creating 3-D Range Names

Lesson 4: Using Advanced Functions

• Using If Functions
• Using Nested If Functions
• Using Sum If Functions
• Using Count If Functions
• Using Vlookup Functions
• Using HLOOKUP Functions

Lesson 5: Managing Tables

• Creating Tables
• Naming the Table
• Changing the Table Style
• Creating a Total Row
• Creating a calculated column
• Using Filtering in Table
• Removing the Duplicate Record

Lesson 6: Using Auto Filter

• Sorting Data
• Using AutoFilter
• Creating a Custom AutoFilter
• Using Advanced Filter
• Using Database Functions

Day 2 of Advanced Excel 2007

Lesson 7: Getting Most from your Data

• Creating Subtotal
• Using Automatic Outline
• Grouping Data Manually
• Using Data Validation

Lesson 8: Working with Charts

• Creating a Chart
• Formatting the Chart
• Adding Labels
• Changing the Chart Type, Data Source

Lesson 9: Working with Pivot Tables

• Creating Pivot Tables
• Adding Pivot Table Report Fields
• Refreshing Pivot Table Report
• Changing the summary function
• Creating report filter pages
• Creating Pivot Chart

Lesson 10: Using Shared Workbooks

• Sharing a Workbook
• Opening and Editing a Shared Workbook
• Tracking Changes
• Resolving Conflicts in a Shared Workbook

Lesson 11: Worksheet Protection

• Protecting your Worksheets
• Protecting range with password
• Protecting your Workbook & Excel Files

Lesson 12: Multiple Workbooks

• Linking Workbooks
• Editing the Link
• Consolidating Workbooks

Lesson 13: Working with Macros

• Display the Developers Tab
• Changing Macro Security Settings
• Recording & Running a Macro

Lesson 14: Scenarios in Excel 2007 (Optional)

• How to Create a Report from a Scenario (Optional)
• Auditing Tools (Optional)