FREE CONSULTATION

Microsoft Excel

Learning Outcomes

  • Gain an understanding of how to use standard functions in complex scenarios
  • Understand how to manipulate complex and ‘unclean data’
  • Use advanced functions to quickly pull insights from data
  • Learn how to tell a data story
  • Know how to efficiently pull data from a variety of sources
  • Appreciate how VBA can be used to improve work flows

Discourse

Whatever your role, Excel is a fundamental tool to help you analyze your strategy, reconcile positions or manage accounts. Sometimes you know that there is a formula, or combination of formulas that can help you solve the data conundrum you face, however you just don’t know how to construct it. This intense course will show you from the ground up how to become an expert in formula, shortcuts and advanced functionality used in creating dashboards to present data and trends effectively. This course has been designed to ensure that the content is relevant, leading to maximum return on investment. We use a case study driven approach, building spreadsheets in advance that are relevant to your division and the typical problems you encounter.

Program Overview

Recap: Entering and manipulating Formulae and References
  • Core Formulae: SUM, AVERAGE, MIN, MAX, COUNT
  • Absolute Relative References
  • Naming Cells and Ranges for more robust referencing
  • Conditional formatting
Text and lookup functions
  • Formatting text using formulae
  • Lookups Functions to cross reference data: VLOOKUP, HLOOKUP, MATCH, INDEX
  • Using the Wildcard character to reference data across multiple sources
  • where regular lookup functions won’t work
Pivot Tables – Presenting financial data analysis effectively
  • Understanding what data is relevant for a Pivot
  • Learning how to use row, column and data fields effectively
  • Presenting the data in the most efficient and effective way
  • Advanced features such as grouping, slicing and calculated fields
Logical functions
  • Building a reconciliation report using IF, AND, OR, COUNTIF, SUMIF, SUMPRODUCT
  • Error handling using ISERROR
  • Circular references and formula auditing to trace errors
  • Use of iteration and binary switches
Working with Data
  • Linking xls files
  • Text to columns
  • Data Tables
  • Pulling data from databases/websites
Introduction to VBA
  • Running: assigning to form buttons
  • Windows: editing, project explorer, properties, immediate
  • Working with modules (coding)
  • User Defined Functions (UDFs)

Have a questions about any of the content?

Schedule a Free Consultation

FREE DIGITAL IQ PROGRAM DEMO

An Introduction to
Digital IQ

FREE ESG PROGRAM PREVIEW

An Introduction to ESG Investing

FREE TRAINING PROGRAM

Authentic Empowerment: Becoming powerful whilst remaining true to your Authentic self