MS Excel Masterclass (Intermediate)

Learn at your own pace eLearning

Suitability

This course is suitable for anyone who already has basic knowledge of Excel and wants to learn more.

Learning Outcomes

Work with spreadsheets, worksheets, and workbooks
Familiarise yourself with the Excel interface
Use keyboard navigation
How to copy and paste data statically
How to link a resource in Word dynamically to its source
How to choose between source or target document formatting
How to copy and paste data statically
How to link a resource in Powerpoint dynamically to its source
How to choose between source or target document formatting
How to insert the map chart into a worksheet
Setting the source data to the Geographic type
Constraining the map to regions or subregions
How to chain logical operators (IF, AND, OR, XOR)
How to format nested formulas in the formula editor
When to choose alternatives to nested formulas
How to use basic cashflow formulae such as PV, FV, IRR
The limitations of these formulae
How to use more complex formulae such as YIELD, PRICE
The importance of parameters such as day count convention
Other frequently-used formulae
How to password-protect at worksheet, spreadsheet and range level
How to set privileges for password-protected objects
How to import from text, web and database sources
Managing connections
Handling custom cell division/newline formats
What the Data Model is for
Adding a source to the Data Model
Installing the PowerPivot add-in
How to import tables into PowerPivot
Setting relationships between tables
Creating a pivot table from >1 related sources
How to synchronise pivot charts to >1 table
Starting up Goal Seek
identifying dependent and independent variables
Setting constraints
Enabling Solver on the toolbar
Identifying dependent and independent variables
Setting more complex constraint rules
How to create, name and manage scenarios
How to toggle between scenario outcomes
The possibilities and limitations of the manager

Course Content

Unit 1 – The Excel ecosystem and linking worksheets

Unit 2 – Exporting from Excel to Word

Unit 3 – Exporting from Excel to PowerPoint

Unit 4 – Advanced visualisation with maps

Unit 5 – Nested formulae

Unit 6 – Common financial formulae (Cashflows)

Unit 7 – Common financial formulae (Rates)

Unit 8 – Importing data into Excel

Unit 9 – Spreadsheet security

Unit 10 – The data model

Unit 11 – Pivot tables from multiple tables

Unit 12 – Advanced pivot charts

Unit 13 – What is analysis (Goal Seek)

Unit 14 – What if analysis (Solver)Unit 15 – What if analysis (Scenario Manager)

This Course Includes

Learn: 15 micro-learning on-demand videos
Check: 15 knowledge checks to validate understanding
Apply: 15 hands-on practical exercises and projects
Requirements: Access to a computer with an internet connection
Accessibility: Access on PC, mobile or TV
Certification: Certificate of completion with CPD record

100%

of our clients would recommend Alpha to a colleague or peer

90%

of a cohort was promoted within 2 years after participating in an Alpha course.

96%

of participants felt more confident in their role, following an Alpha commercial leadership course.

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

Enquire now

"*" indicates required fields

The team are so friendly and pleasant to work with, everyone is very professional and keen to help us. Building a relationship over the past couple of years helps us to feel like the Alpha team are even more able to understand our needs and provide more proactive solutions.