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! |