Course 098:
Excel Essentials and Advanced Data Management

(2 days)

 

Course Description

Whatever your role, Excel is a fundamental tool to help you analyze your strategy, reconcile positions, or manage projects. 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 formulae, shortcuts, and advanced functionality used in creating dashboards to present data and trends effectively. This course has been designed for those in financial markets by leading practitioners to ensure that the content is relevant for those taking the course, leading to maximum return on investment.

Course Outline

Overview of Excel

  • Navigating Workbooks and Worksheets
  • Setting up Excel for Easy and Speedy Navigation
  • Shortcut Master Class

Formatting Data

  • Entering and Formatting Data Using Advanced Excel Features
  • Cut, Copy, Paste, and Paste Special
  • Data Validation
  • Conditional Formatting

Entering and Manipulating Formulae and References

  • Core Formulae: SUM, AVERAGE, MIN, MAX, COUNT
  • Absolute vs. Relative References
  • Naming Cells and Ranges for More Robust Referencing

Logical Functions

  • The IF Statement and Its Arguments
  • Building a Reconciliation Report Using IF, AND, OR, COUNTIF, SUMIF, SUMPRODUCT
  • Error Handling Using ISERROR
  • Circular References and Formula Auditing to Trace Errors
  • Deliberate Circular References
  • Use of Iteration and Binary Switches
  • Text Strings / Concatenation to Reference Multiple Cells

Text and Lookup Functions

  • Formatting Text Using Formulae
  • Using LEFT, RIGHT, LEN, SEARCH to Consider Certain Characters
  • Lookups Functions to Cross Reference Data: VLOOKUP, HLOOKUP, MATCH, INDEX

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 and Calculated Fields

Please Contact Your ROI Representative to Discuss Course Tailoring!