Microsoft Excel Advanced

Duration: 1 Day

Course Overview

This 1 day instructor led training course will extend user knowledge in using the more advanced features and capabilities of Excel. Delegates will learn how to use advanced functions, manipulate data, apply advanced analysis techniques and manage data in worksheets.

How can I attend my course?

On-line from
your chosen location

At our dedicated
training facility

On-site at
your premises

Target Audience

This course is designed to give experienced users of Microsoft Excel a deeper understanding of its advanced features

• Existing users of Microsoft Excel that wish to gain an in depth understanding of the more advanced features will benefit greatly from this course.
• They will learn to analyse data very quickly and learn time saving techniques.

COURSE OBJECTIVES

• Know and use the advanced logical and mathematical functions
• Apply lookups to data
• Create and manipulate data tables
• Create, modify and manage pivot tables to summarise data
• Create pivot charts
• Manipulate data using advanced analytical tools
• Import and export data in compatible formats
• Record, run and manage macros
• Customise the Ribbon

Course Content

More functions

• Statistical functions
• Text functions
• Information functions
• Financial functions
• Nesting various functions

Lookups

• Use vlookup function
• Using hlookup function
• Using MATCH and INDEX

Custom Views

Creating a view of a worksheet

Customise Excel

• Customise the Ribbon
• Create new tabs and groups
• Add the Developer tab

Pivot Tables and Pivot Charts

• Creating Pivot Tables
• Rearranging Pivot Tables
• Formatting PivotTables
• Use the slicer
• Creating Pivot Charts
• Working with Pivot Charts

Exporting and importing

• Exporting and importing text files
• Exporting and importing XML data
• Querying external databases

Data Tools

• Data validation
• What If Analysis Tools
• Scenario Manager
• Goal Seek
• Data Tables
• Consolidate
• Grouping and sub-totals

Auditing a Worksheet

• Auditing Toolbar
• Tracing precedents and dependents
• Adding Comments
• Work with comments

Macros and custom functions

• Running macros
• Recording new macros
• Working with VBA code
• Assign a macro to quick access toolbar or ribbon

COURSE PREREQUISITES

Delegates are recommended to have a good working knowledge of Excel or attended the Excel intermediate training course.

You will receive a full set of course notes
and all supporting materials for your course.

Hard Copy Delivered to your premises or Downloaded to a chosen device.