Excel Advanced - Dashboards, What-if Scenarios, more functions and an introduction to Macros

Course Outline

Audience

This one-day course is for people who need to analyse large data sets, manipulate data with a range of Excel functions, set up data models using What If scenarios and use a variety of Excel tools.

Objectives

At the end of the course delegates will be able to:

  • Use Excel Functions to manipulate and calculate with Dates and Times
  • Use Excel Functions to manipulate and clean text and numeric data
  • Use Excel Functions to look up data stored in other worksheets and workbooks
  • Carry out “What-Ifs” using Goal Seek, and Scenario Manager
  • Use Consolidation commands to consolidate data
  • Apply 3D formulae and functions
  • Excel Analysis Tool Pack to create histograms
  • Import and Export data
  • Create dashboard interfaces using multiple Pivot Tables and Charts
  • Use Form Controls on dashboards
  • Introduction to recording Macros

Course Content

Functions

  • Complex DATE, TIME, INFORMATION and TEXT functions
  • LOOKUP functions – Index, Match, Offset
  • 3D formula and functions

Pivot Tables

  • Recap of Excel Tables and Pivot Tables
  • More advanced Pivot Tables
  • Calculation Fields and Calculated Items
  • Pivot Table Functions
  • Multiple Pivot Tables on worksheets
  • Creating related Pivot Tables with Primary and Foreign Keys

Using Goal Seek and Scenario

  • Using Goal Seek
  • Using the Scenario Manager
  • Displaying a Scenario
  • Creating a Scenario Summary Report
  • Data Tables and Break Even Charts

Excel Add-in Analysis tools

  • Histograms
  • Descriptive Statistics

Importing and exporting data

  • Importing data from text files, Excel files, Access Database files and from the Web
  • Query definitions
  • Exporting data

Form Controls

  • Work with text boxes, combo boxes, lists, option buttons, check boxes, spinner buttons
  • Macros and introduction to VBA
  • Introduction to automating processes

Macro Recorder

  • Recording with absolute and relative cell references
  • VBA Editor

Show the next months in

Location Dates Time Price exc. VAT   Status
Growtrain Ltd 8C PO20 2BX 25 Jul 2019 09:00h – 16:00h £145.00 Registration Closes
Jul 24, 2019 10:00AM


Growtrain Ltd 8C PO20 2BX

25 Jul 2019

09:00h – 16:00h

£145.00 exc. VAT

 

Register Now » 8 Places Available!
Register Now » 8 Places Available!
Growtrain Ltd 8C PO20 2BX 25 Oct 2019 09:00h – 16:00h £145.00 Registration Closes
Oct 24, 2019 10:00AM


Growtrain Ltd 8C PO20 2BX

25 Oct 2019

09:00h – 16:00h

£145.00 exc. VAT

 

Register Now » 7 Places Available!
Register Now » 7 Places Available!
Growtrain Ltd 8C PO20 2BX 22 Nov 2019 09:00h – 16:00h £145.00 Registration Closes
Nov 21, 2019 9:00AM


Growtrain Ltd 8C PO20 2BX

22 Nov 2019

09:00h – 16:00h

£145.00 exc. VAT

 

Register Now » 8 Places Available!
Register Now » 8 Places Available!
Contact Nina or Graham on 01243 216278 or email courses@growtrain.co.uk to book this course
Share This