Modules

Excel For Analysts - Introduction
This module covers:

• Key Excel formulas for analysts, emphasising that a few well-understood formulas suffice for most tasks, with the internet as a resource for occasional needs.
• Excel's enduring relevance, comparing its utility to alternatives like Google Sheets and its role in various professional settings.
• An introduction to the Excel for Analysts Box Set, outlining a journey from basic to advanced functionalities, promoting a practical, hands-on learning approach.

1. Basic Mathematical Formulae
This module covers:

• Introduction to basic mathematical formulae in Excel, emphasising the transition from calculator functions to Excel's built-in functions for addition, subtraction, multiplication, division, and powers.
• Detailed explanation of Excel's notation for mathematical operations, including the use of symbols like the plus sign (+) for addition, asterisk (*) for multiplication, forward slash (/) for division, and caret (^) for exponentiation.
• Practical application of these formulae in Excel, including calculating square roots, using cell references in formulae, and converting hours to seconds, with an emphasis on enhancing forecasting and modelling skills.

2. Structuring Formula
This module covers:

• The importance of structuring formulae correctly in Excel, emphasising the ability to combine functions to perform complex tasks and distinguishing between spreadsheet proficiency levels.
• The basic rules of Excel formula structure, including order of operations (multiplication and division before addition and subtraction, and the use of brackets to dictate operation order), and how Excel processes formulae from left to right unless directed otherwise.
• Advanced techniques such as pinning or locking cell references to ensure accurate formula copying across a spreadsheet, facilitating the construction of dashboards and complex data analysis tasks.

3. Averages & Ranges
This module covers:

• The importance and calculation of averages (mean, median, mode) in Excel for analysts, emphasising the most common use of the mean for dashboard metrics and the pitfalls of averaging averages.
• An explanation on the calculation and application of ranges, including max, min, and range formulas, to identify performance variances and opportunities for improvement.
• A walkthrough of exercises using Excel formulas to calculate averages and ranges, highlighting practical examples and common mistakes to avoid in data analysis and reporting.

4. Decision Formulae
This module covers:

• Basics of Decision Trees and IF Formulas: Introduction to using decision trees and the IF formula in Excel for simple decision-making processes.
• Criteria Application Across Data Types: Instructions on applying decision criteria to boolean values, numbers, dates, times, and text strings using Excel formulae.
• Complex Decision Making Techniques: Advanced techniques involving nested IF functions, AND/OR logic, and text analysis for sophisticated data categorisation and decision-making strategies.

5. Reference Formulae
This module covers:

• The critical skill of linking data from various sources using unique identifiers and reference formulas for insightful analysis.
• Utilising Excel's VLOOKUP for data matching and error handling techniques for clean results.
• Alternatives like INDEX MATCH for scenarios where VLOOKUP may not be suitable, showing the need for adaptable data analysis methods.

6. Dashboard Formulae

This module covers:

• The basics of building dashboards in Excel, focussing on counting and summing metrics, which can be combined to create averages, percentages, and scores.
• Detailed explanations and examples of formulas such as COUNT, COUNTA, COUNTIF, COUNTIFS, SUM, SUMIF, SUMIFS, and SUMPRODUCT, demonstrating how to apply them for practical dashboard creation.
• Encouragement for learners to practise these techniques with their data or provided sandbox data to gain comfort and proficiency in dashboard construction.

7. Pivot Tables
This module covers:

• An introduction to creating dashboards with pivot tables in Excel, focusing on basics for beginners and encouraging exploration of advanced techniques.
• A step-by-step demonstration on creating, adjusting, and filtering data in pivot tables, highlighting the importance of column headings and manual data refreshes.
• The advantages and drawbacks of pivot tables compared to formula-based reports, including ease of use, customisation through filters, and challenges such as potential for incorrect data reporting and the necessity for frequent updates.
   

The cost of this mini series including accreditation is great value at £325 + VAT and free for students on our assisted learning pathway.

Watch the free introduction to find out more.

On completion of the box set you can become accredited at a foundation level in our Professional Accreditation Programme by sharing an example of how you have applied these techniques in your own work. Email accreditation@theforum.social with your learning