Microsoft Excel: Advanced

Microsoft Excel: Advanced

Overview and Objectives

This one-day course is designed to help a delegate develop spreadsheets using more advanced functions and creating more complex formulas. A delegate will learn to use LOOKUP functions to extract data from a table, distribute data in different formats and publish a spreadsheet on the web.

Course Outline

This course consists of 6 modules
Module 1Understanding Tables
Advantages of Tables when Creating PivotTables
PivotTables Terminology
Creating PivotTables
Adding Data Fields
Using the Report Filter, Show/Hide Data
PivotTable Options
View Source Detail
Remove Data from PivotTables
Change Summary Functions
Format PivotTable Values
Removing Subtotals & Grand Totals
Update & Refresh Data
Chart PivotTables
Using Slicers
Module 2Using the VLOOKUP Function
Using the HLOOKUP Function
Understanding the True or False in the Range Lookup
Using the IF Function
Nesting IF Functions
Using VLOOUP with an IF Function
Financial Function
PMT Function & FV Function
Module 3Create a One-Input Data Table
Create a Two-Input Data Table
Goal Seek
Solver
Scenario Manager
Module 4Automating with Visual Basic for Applications
Recording a Macro
Running a Macro
Examining the Procedure
Viewing & Editing VBA Code
Attach Macros to Objects
Assign Macros to a Button
Assign Macros to the Quick Access Toolbar
Change the Button Image
Save Macro-Enabled Workbooks
Macro Security Levels
Module 5Importing Data using the Microsoft Query
Working with Columns
Filtering Data
Sorting Data
Finishing the Query
Editing & Updating Query Results
Importing Text Data
Exporting Data
Saving a worksheet as a Web Page
Publishing a Worksheet to the Web. Auditing Workbooks 7 Worksheets
Using Excels Auditing Tools
Using the Watch Window
Sharing Workbooks
Module 6Creating & Saving Templates
Using Templates
Editing Templates
Conditional Formatting
Customize Excel
Creating & Using Styles

Microsoft Excel: Advanced

Who should attend?

Any delegate wanting to develop their skills in using Microsoft Excel to design spreadsheet solutions and use tools for creating and formatting more complex spreadsheets, that solve more difficult problems.

Course Prerequisite

Microsoft Excel: Intermediate

Duration

1 Day

Cost

R1 270,00 (excl. VAT)

Includes

  • Comprehensive Manual
  • Lunch & Refreshments (not applicable to on-site training)
  • Electronic Certificate (on successful completion of the course)
  • Electronic Trainer Feedback Report
  • Electronic Delegate Feedback Questionnaire
Download Course Information

Microsoft Excel: Advanced

Course Schedule

View the available course dates