# MS Excel Fundamentals Masterclass

## This course gives the learners a solid foundation in financial mathematics. The time value of money, compounding and discounting form the initial base. Learners will evaluate bond prices versus yields as well as performing annuity, amortisation and IRR calculations. The principles of statistics are outlined and participants will gain an understanding of arithmetic and geometric means, normal distributions, standard deviation and correlation and their application in financial markets.

### Suitability

This course is suitable for anyone new to Microsoft Excel.

### Learning Outcomes

• Work with spreadsheets, worksheets, and workbooks
• Familiarise yourself with the Excel interface
• Work more efficiently with Excel
• Selecting rows and columns using shortcuts
• Selecting and navigating a range of cells using shortcuts
• How to use other time saving shortcuts
• Format numbers and borders
• Remove gridlines and freeze panes
• How to use formatting shortcuts
• What they key elements of an Excel formula are
• Create formulas in Excel
• Use mathematical operators in Excel
• Use relative referencing
• Use absolute referencing
• Name cells and ranges
• Use the VLOOKUP function
• Use the MATCH function
• Use the INDEX function
• Use the IF function
• Use the AND and OR functions
• Use the IFERROR function
• Insert a chart into a worksheet
• Customise a chart
• Implement charting best practices
• Navigate the conditional formatting wizard
• Implement 3 types of conditional formatting rule
• Manage formatting rules
• Outline the advantages of using data tables
• Convert ranges to tables and back
• Use simple data table structured references
• Create a pivot table
• Implement 2+ dimensional pivots with the wizard
• Filter pivot table
• Convert appropriate text and numbers to dates
• Decompose a dates into day, month and year
• Deploy 4 commonly-used date functions
• Define the optimum use cases for filters, slicers and timelines
• Sort on multiple values
• Use filtering to eliminate duplicates
• List the types of validation Excel offers
• Apply a data validation rule
• Create a dropdown list with validation and formulae
• Describe reasons for using macros, and macro security
• Record a simple macro
• Attach a macro to a form control

### Course Content

Unit 1 – Navigating Excel

Unit 2 – Customising the toolbar

Unit 3 – Shortcuts

Unit 4 – Formatting

Unit 5 – Formulas basics

Unit 6 – “Formula Referencing”

Unit 7 – Lookup functions

Unit 8 – Logical functions

Unit 9 – Charting

Unit 10 – Conditional formatting

Unit 11 – Data tables

Unit 12 – Pivot tables

Unit 13 – Working with dates

Unit 14 – Sorting and filtering

Unit 15 – Data validation

Unit 16 – Recording macros

### This Course Includes

• Learn: 16 micro-learning on-demand videos
• Check: 14 knowledge checks to validate understanding
• Apply: 15 hands-on practical exercises and projects
• Accessibility: Access on PC, mobile or TV
• Certification: Certificate of completion with CPD record

100%

90%

96%

## Why study with Alpha?

### Innovation and creativity born from experience

We are thought leaders in instructional and learning journey design and holistic solution architects. We have extensive finance and investments experience combined with skills application to deliver performance improving results. We develop immersive learning environments that maximize time to productivity, support talent retention and added value to improving quality of hires.

### Knowledge Exchange Evangelists

We are focused on mining the embedded organisational intellectual capital for the benefit of the next generation. We create and curate best in class practice gathered from our experience with the leading financial institutions. We design our programmes with the end in mind – what results are you trying to achieve with this intervention? What metrics will we set ourselves to achieve that?

### Generation Proof

Quality and innovation, using current market and industry best practices, have made us a trusted partner in delivering dynamic and motivating training for the financial and capital markets. Our programmes are generation proof and responsive to evolving learner needs and styles. Our solutions use a multi-stakeholder engagement strategy that expands beyond relationships between the learner and learning provider. We create connections with managers, peers and the wider business to drive impactful return on investment..