EMail Print


MS Excel Advance
17.08.2017 - 17.08.2017



Overview & 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 Prerequisite

Microsoft Excel: Intermediate

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 content

Module One

Pivot Table Terms; Create Pivot Tables; Modify Pivot Tables; Adding Data Fields; Using the

Page Field; Hiding & Showing Data; PivotTable Field Advanced Options; View Source Detail

Information; Remove Data from PivotTable; Change Summary Functions; Removing Grand

Totals; Format a PivotTable; Update & Refresh Data; Chart a PivotTable

Module Two

Using VLOOKUP Function; Using the HLOOKUP Function; The LOOKUP Wizard; Using

Logical Functions; IF Function; Nesting IF Functions; Using VLOOKUP with an IF Function;

Using Financial Functions; PMT Function; FV Function

Module Three

Create a One-Input Table; Create a Two-Input Table; Goal Seek and Solver; Goal Seek; Solver; Scenario Manager

Module Four

Automating with Visual Basic for Applications; Starting the Recorder – STEPS; Recording a Macro; Running a Macro; Recording a Formatting Macro; Examining the Procedure;

Viewing & Editing VBA Code; Attaching Procedures to Objects; Assign a Procedure to a

Button; Assign a Macro to a Toolbar; Changing the Button Image; Name the Toolbar Macro

Button; Edit the Macro Button; Macro Security Levels

Module Five

Microsoft Query; Start Microsoft Query; Using the Query Wizard; Working with Columns;

Filtering Data; Sorting Records; Finishing the Query; Editing & Updating Query Results;

Importing Text Data; Exporting Data; Saving a Worksheet as a Web Page; Publish a

Worksheet to the Web; Auditing Workbooks & Worksheets; Using Excel’s Auditing Tools;

Tracing Arrows; The Auditing Toolbar; Sharing Workbooks

Module Six

Templates; Conditional Formatting; Customizing MS Excel; Creating and Using Styles


Bookings:             Complete a registration form and fax to 023 626 1854 or mail to This e-mail address is being protected from spambots. You need JavaScript enabled to view it .