Advanced - Microsoft Excel Training Course:  

 

 

Synopsis:

Microsoft Excel 2007 offers a vast array of functionality to assist in manipulating and analysing data. This course looks at importing data using queries, whether from the web or databases, prepping the data and analysing it with What If, Scenario building and Pivot tables. You will also discover how to record and run your own macros.

Pre-Requisites:

No pre course work is required for this excel advanced training course, however learners must be proficient with Microsoft excel and have attained the excel intermediate level prior to attending this course.

Course Outline:

By the end of this Microsoft Excel Advanced course you will know how to:

  • Use advanced Excel functions
  • Create Lookups and data tables
  • Manage large lists of data
  • Create and use PivotTables and PivotCharts
  • Export and import data
  • Analyse data effectively
  • Create basic macros
  • Use conditional formatting

Course Content:

Microsoft excel advanced course outline:

Advanced excel functions

  • Logical functions
  • Math and statistical functions
  • Financial functions
  • Displaying and printing formulas

Lookups and data tables

  • Using lookup functions
  • Using MATCH and INDEX
  • Creating data tables

Advanced excel list management

  • Validating cell entries
  • Exploring database functions

PivotTables and PivotCharts

  • Working with PivotTables
  • Rearranging PivotTables
  • Formatting PivotTables
  • PivotCharts

Exporting and importing

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

Analytical options

  • Goal Seek and Solver
  • The Analysis ToolPak
  • Scenarios
  • Views

Macros and custom functions

  • Running and recording a macro
  • Working with excel VBA code
  • Creating functions

Conditional formatting and SmartArt

  • Conditional formatting with graphics
  • SmartArt graphic

Learning Outcomes;

  • The skills to import data into Excel, prepare it and set up pivot tables to analyse it
  • The knowledge to record macros and create icons on the toolbars to run them
  • Understand how to locate errors in formulas and evaluate them
  • Be able to use analysis tools to forecast figures based on a range of scenarios
  • Ensure important data is highlighted by summarising large workbooks and sorting data into key elements
  • Useful practical exercises

Duration:

1 Day

No of Delegates – The Total Number of delegates that can sit this is course is a maximum of 12 to uphold the quality of our training and to ensure the course is properly embedded to achieve the companies/individual goals.

Prices – £849 (ex VAT)  Per day for up to 12 delegates trained onsite

(An additional charge for travel will be applied at 40p per mile from Sequence HQ to your premises)