Advanced Excel Tools

Microsoft Excel is a very powerful spreadsheet application designed to save you time and make easy work of calculating, displaying and sharing data. This Excel advanced training course will provide you with an understanding of the most advanced features of MS Excel.

Course Prerequisites

Participants should have a basic understanding of Excel.

Course Outline

Absolute Cell Addressing

• Using Absolute References

Naming Cells and Ranges

• Using the Create Method to Name Cells
• Using the Names Box to Name Cells
• Using Insert Name Define
• Working with Named Ranges

Working external databases

• Consolidating Data across Worksheets
• Consolidating Data across Workbooks
Using Formulae and Functions

• How to Enter a Formula
• Working with Formulae
• How to Use Autosum
• What are Functions
• Using simple Functions (Average, Min, Max)
Conditional Functions

• Introduction to If
• Using Nested If's
• Using SumIf and CountIf
Lookup Functions

• Using Vlookup
• Using Hlookup
Data Validation

• Using the Data Validation feature
• Finding Invalid Data
Conditional Formatting

• Setting Up Conditional Formats
• Using Formulae in Conditional Formats
• Using Conditional Formats to find MIN and MAX
Working with Data Lists

• Sorting
• Subtotals
• Filtering
Pivot Tables

• Using the Pivot Table Wisard
• Modifying the Pivot Table
Data Tables

• Creating Data Tables
• One Way and Two Way Tables
UserForms

• Designing & Creating Forms
• Working with Controls
• Creating Custom Dialog Boxes
• Userform Properties Methods & Events