ΠΕΡΙΓΡΑΦΗ

Many of us struggle with the sheer amount of data that is available in this information age. In many cases, reports are built to present the data, but find that the end result still has a limiting structure and usability. This one-day course provides attendees with hands-on techniques with a focus on keeping things dynamic and expandable. Covering structured references, dynamic listing techniques, scenario modelling, and through the exercises we will get to use some of the more unexplored functions and areas in Excel.

In addition to the Online Classroom, you will need to have your own version of Excel open to complete the exercises. It does prove to be challenging to follow-on with the exercises using only 1 screen as you will need to be regularly switching between open windows. We recommend to either:

• Extend from a computer to a 2nd screen - please refer to this link for guidance on how to correctly setup a second monitor https://support.microsoft.com/en-us/help/4340331/windows-10-setup-dual-monitors or
• Use 2 computers, or
• Use a tablet for the online classroom and a computer for the Excel

Please note that the course is based on Excel for Windows, and it is recommended that you are using at least Excel 2010 or a later version

ΣΕ ΠΟΙΟΥΣ ΑΠΕΥΘΥΝΕΤΑΙ

The course material includes advanced features of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheet users and / or have already attended the ‘Data Analysis with Excel– Intermediate’ course. This course is designed for users who use Excel on a regular basis and are looking to further their knowledge in producing more robust spreadsheets like data analysts, Finance managers, decision makers, financial controllers, junior accountants, excel power users. At minimum it is assumed that participants will know how to navigate confidently in Excel, use absolute reference ie $A$1 and have familiarity with nested functions.

ΠΕΡΙΣΣΟΤΕΡΕΣ ΠΛΗΡΟΦΟΡΙΕΣ

Topics to be Covered

Advanced listing techniques

• Tricks in creating dependent lists
• Dynamic selection tips
• Dynamic extraction of unique values

Facilitating calculations

• Dynamic and expandable named ranges
• Multi-nested functions / expandable referencing
• Structured table references / Table nomenclature
• Aggregate calculation using the ‘wildcard’ technique

Key functions

• DSUM / SUMPRODUCT / SUMIFS (& building conditions)
• Lookup: OFFSET / CHOOSE / INDEX / MATCH
• ROWS
• INDIRECT

Further advanced techniques for summarizing & presenting data

• Mini Pivot Table reports
• Fast calculations with Data Tables
• Data modelling with Scenario Manager

Bonus topics:

• Dashboard techniques to build an interactive screen
• Simple VBA exercises to speed up processes
• Tips and tricks in custom formatting

All exercises and project files used on the course will be available to take home

Instructor: Professionally BPP experienced tutors with real life industry experience and professionals of the subjects they teach.