Excel Advanced

Click on a location to see available dates, read more details and to register your interest:

• Oxford

Delegates Will Learn How To

• Creating, Maintaining, Filtering and using Subtotals in a List
• Recording, Assigning and Using Macros
• Creating and Using User Defined Functions
• Performing “What If” Analyses
• Working with Scenarios and Data Maps
• Working with Pivot Tables
• Querying a Database
• Importing and Exporting Files
• Using Object Linking and Embedding
• Protecting Data

Course outline

Working with Lists
Creating a List
Identifying the Parts of a List
Entering Column Labels in a List
Entering Data in a List
Maintaining a List
Using the Data Form
Adding and Deleting Records Using the Data Form
Editing Records Using the Data Form
Adding and Deleting Fields in a List
Finding Records in a List
Sorting a List
Filtering a List
Using AutoFilter
Customizing AutoFilter Criteria
Using the Top 10 Feature
Performing an Advanced Filter
Copying Filtered Data to Another Location
Using Subtotals in a List
Using Automatic Subtotals
Working with Subtotal Outline Symbols
Nesting Subtotals
Working with Macros and User Defined Functions
Recording and Using Macros
Recording a Macro
Running a Macro
Recording a Macro in the Personal Macro Workbook
Editing a Macro
Deleting a Macro
Recording a Macro Using Relative References
Assigning Macros
Assigning a Macro to a Keyboard Shortcut
Assigning a Macro to a Toolbar Button
Assigning a Macro to a Worksheet Button
Using User Defined Functions
Creating a User Defined Function
Using a User Defined Function
Adding Documentation to Visual Basic Code
Using Basic Analysis Tools
Performing “What If” Analyses
Using Goal Seek
Using Data Tables
Using Solver
Working with Scenarios
Creating a Scenario
Displaying the Results of a Scenario
Editing a Scenario
Creating a Scenario Summary Report
Deleting a Scenario
Working with Data Maps
Creating a Data Map
Changing the Size and Position of a Data Map
Formatting a Data Map
Editing the Title and Legends
Adding Features to a Data Map
Using Advanced Analysis Tools
Section Skills and Their Importance
Working with Pivot Tables
Identifying the Parts of a Pivot Table
Starting Pivot Table Wizard
Selecting the Source Data Range for a Pivot Table
Finishing PivotTable Wizard
Rearranging, Adding, and Removing Fields in a Pivot Table
Changing the Display of a Pivot Table
Changing the DATA Area Number Format
Querying a Database
Starting a Simple Query using Query Wizard
Selecting Columns in Query Wizard
Filtering and Sorting Data in Query Wizard
Finishing Query Wizard
Working with Other Applications and Users
Section Skills and Their Importance
Importing and Exporting Files
Importing a File from Another Application
Starting Text Import Wizard for a Delimited Text File
Setting Delimiters in Text Import Wizard
Setting the Column Data Format in Text Import Wizard
Exporting an Excel File
Using Object Linking and Embedding
Creating and Editing an Embedded Object
Creating and Editing a Linked Object
Working with Others
Setting Up a Shared Workbook
Tracking Changes
Showing a History of Changes
Incorporating Changes and Resolving Conflicts
Merging Workbooks
Protecting Data
Hiding and Unhiding Rows and Columns
Using Data Validation
Using Worksheet Protection
Password Protecting a Workbook