Jayne Wilkins & Associates Training Courses In Kent In London

01303 268357

jayne@jaynewilkins.co.uk

Microsoft Excel Courses - Advanced

Microsoft Excel Courses

Microsoft Excel Advanced

Pivot Tables are often the main focus of this Microsoft® Excel advanced course but there are many other useful forgotten features that can be discovered including shared workbooks and data validation.

The course is designed to be flexible and a discussion of the students' main points of interest will take place before the start of the day. Before attending this course students should be comfortable with the majority of the features on the introductory and intermediate courses.

Please note that Macro training on this advanced course is aimed at writing and running macros from the Excel interface. VBA programming and building custom dialog boxes is not covered. Courses are available in Excel VBA and please contact us for details.

From: £325
Level: Advanced
Duration: 1 Day

Data Analysis with PivotTables

Essentials

  • Preparing data for a Pivot Table
  • Creating & Editing a Pivot Table
  • Using the column, row, page/filter and value positions
  • Formatting a Pivot Table
  • Adding & editing a Pivot Table Chart
  • Using multiple fields
  • Filtering on multiple fields
  • Using the Slicer - from Excel 2010

Dates, Custom Groups, Multiple Tables

  • Working with dates and grouping dates
  • Creating custom groups
  • Creating multiple Pivot Tables
Changing the Pivot Table Calculation

  • Using Sum, Average, Max, Min
  • Adding a field more than once to the values area
  • Changing the calculation of the data
  • Calculating a % of column, row and table
  • Calculating a running total and % of base record
  • Comparing two years figures
  • Adding a calculated field
  • Creating a Pivot Table from multiple data sources

Data Validation

  • Setting up Data Validation to enable:
  • Pick from List
  • Specific numbers to be typed in cells
  • Controlling the error messages and input alerts

Sharing Workbooks

  • Setting User Information
  • Making a workbook shareable
  • Updating a shared workbook
  • Updates by multiple users at the same time
  • Resolving and viewing conflicts
  • Viewing the change history
  • Protecting a shared workbook

Macros

  • Recording and running a Macro
  • Macro storage
  • Absolute and Relative referencing
  • Assigning a Macro to a button, keystroke and menu
  • The Personal Macro Workbook

What IF Analysis

  • Goal Seek
  • Scenario Manager
    • Adding a Scenario
    • Displaying Scenarios
    • Merging Scenarios
    • Creating a Scenario Report
  • Data Tables
  • Solver (if required)
    • Using Solver to find a resolution for complex problems with many possible variables

Protecting Worksheets & Cells

  • Sheet Protection
  • Unlocking a Range of cells
  • Hiding Formulae in cells
  • Workbook Protection
  • Windows & Structure Protection
  • Saving a file with a Password

Outline and the View Manager

  • Using Manual & Automatic Outlining
  • Creating, Displaying and Deleting a View

Worksheet Auditing

  • The Audit Toolbar
  • Tracing Precedents and Dependents
  • Navigating and Removing Tracer Arrows
  • Tracing Arrows
  • Adding/Deleting/Printing Comments

Advanced Functions (if required)

  • Financial/Statistical Functions
  • Information Functions
  • Cell Test Functions

Using Styles and Templates

  • Creating a template and template storage
  • What are Styles in Excel?
  • Adding and Editing Styles
  • Changing the default Book.xlt workbook