Microsoft Excel Advanced

Duration: 1 Day 

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?

Who will Benefit:

  • 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.

Target Audience:

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


Objectives.

You will learn how to:

  • 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


Prerequisites.

We recommend that attendees of this course have the following prerequisites:

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


Course Outline.

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

My Course Notes

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.
 

     OR  

To book this course please call +44 (0) 1444 410296 or email at Info@kplknowledge.co.uk

© Copyright 2020 KPL KnowledgeSolutionsWeb Design By Toolkit Websites