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
  • Import data and Power Query for Load, Transform and Edit imported data
  • Create dashboard interfaces using multiple Pivot Tables and Charts
  • Introduction to recording Macros

Course Content

Functions

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

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

Importing data

  • Importing data from text files, Excel files
  • Power Query

Macros and Introduction to VBA

  • Introduction to automating processes
  • Macro Recorder
  • Recording with absolute and relative cell references
  • VBA Edit

Show the next months in

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


Growtrain Ltd 8C PO20 2BX

25 Oct 2019

09:00h – 16:00h

£155.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 £155.00 Registration Closes
Nov 21, 2019 9:00AM


Growtrain Ltd 8C PO20 2BX

22 Nov 2019

09:00h – 16:00h

£155.00 exc. VAT

 

Register Now » 8 Places Available!
Register Now » 8 Places Available!
Growtrain Ltd 8C PO20 2BX 13 May 2020 09:00h – 16:00h £155.00 Registration Closes
May 13, 2020 10:00AM


Growtrain Ltd 8C PO20 2BX

13 May 2020

09:00h – 16:00h

£155.00 exc. VAT

 

Register Now » 8 Places Available!
Register Now » 8 Places Available!
Growtrain Ltd 8C PO20 2BX 30 Jul 2020 09:00h – 16:00h £155.00 Registration Closes
Jul 30, 2020 10:00AM


Growtrain Ltd 8C PO20 2BX

30 Jul 2020

09:00h – 16:00h

£155.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