Data Analysis with Excel - Intermediate & Advanced
In the intermediate course, study quick tips to better utilising the power of Excel. Followed by the advanced course focusing on techniques to keep things dynamic and expandable.
3,000 employers partner with us to shape our courses
Pass rates up to 20% higher than the global average*
We train professionals from all four of the Big Four accountancy firms
Overview
- Price
- Face-to-Face from £882.00Online Live from £882.00Price includes VAT
Choose your courses
Data Analysis with Excel - Advanced
Take your Excel skills to the next level with this hands-on, one-day course focused on dynamic, flexible data analysis. Learn advanced techniques including structured references, dynamic lists, scenario modelling, and powerful Excel functions to transform raw data into actionable insights. Ideal for professionals seeking to enhance reporting and analytical capabilities.- No extra learning materials
- No extra learning materials
Data Analysis with Excel - Intermediate
Unlock the full potential of Excel with this one-day, intensive course. Learn how to analyse large datasets using advanced reporting tools, array formulas, and complex nested functions. Discover dynamic techniques for data manipulation, scenario modelling, and explore macro automation to streamline processes. Perfect for professionals looking to create robust, flexible models and transform raw data into actionable insights.- No extra learning materials
- No extra learning materials
Course structure
Who is it for?
Those using Excel on a regular basis wishing to learn more about performing various forms of analysis. Also support staff, finance professionals and analysts wanting to increase their functional knowledge of Excel.
Finance professionals
Data analysts
Spreadsheet authors
Finance managers
Financial controllers
Junior accountants
Learning Outcomes
By the end of this course, you will be able to:
Use advanced Excel techniques to save time and improve efficiency
Build and control Pivot Tables and charts to summarise and visualise data effectively
Apply advanced lookup techniques, including XLOOKUP, to manage complex data sets
Use logical, conditional, error-handling, text, and array functions for powerful data analysis
Simplify complex IF and array functions using the CSE keystroke method
Identify hidden trends in business data using buckets, intervals, and advanced modelling techniques
Automate repetitive tasks with macros and formula-based solutions
Apply extensive conditional formatting to highlight and interpret key insights
Course Coverage
This course will cover:
Time savers and fast keys
Pivot Tables and Charts:
Controlling charts via Pivot Tables
Automating chart summaries
The “Lookup Family”:
Flexible lookup techniques
V/HLOOKUP limitations
XLOOKUP in practice
Practical solutions for complex data sets
Functions:
Logical, conditional, and error functions
Text-string manipulation functions
Nesting for increased power
Array functions
The CSE keystroke method:
Simplifying IF functions
Multi-cell array functions
Identifying trends in business data:
Buckets and intervals
Quick remodelling to meet targets
Bonus topics:
Extensive conditional formatting with formula-based rules
Homework topics for further practice
Automation with macros:
Applying meaningful macro functionality
Automating formulas
All exercises and project files are provided to take home
Online Attendance Guidance
If you are attending this course online, please note:
You will need your own version of Excel open to complete the exercises
Using a single screen can be challenging due to switching between windows. For the best experience, we recommend:
Extending your computer to a second screen
Using two computers, or
Using a tablet for the online classroom and a computer for Excel
The course is based on Excel for Windows (Excel 2010 or later is recommended). Mac users are welcome, but some functionality differs, and the trainer will accommodate where possible.
Who is it for?
The course includes advanced features of Excel and you will gain the maximum benefit if you are already a competent spreadsheet user and/or have attended BPP's ‘Data Analysis with Excel – Intermediate’ course. This course is designed for professionals who use Excel on a regular basis and are looking to further their knowledge in producing more robust spreadsheets.
Please note: At a minimum, it is assumed that participants will know how to:
Navigate confidently in Excel
Use absolute cell references (e.g. =$A$1)
Familiarity with nested functions or at least have an understanding of the benefits in nesting functions together.
This course is ideally suited for:
• Finance professionals
• Data analysts
• Spreadsheet authors
• Excel power users
• Finance managers
• Financial controllers
• Junior accountants
Learning Outcomes
By the end of this course, you will be able to:
Create dynamic and expandable data sets that require minimal maintenance
Apply advanced listing and dependent list techniques for greater flexibility
Use dynamic extraction, selection, and referencing to streamline calculations
Build and apply multi-nested functions and structured table references
Perform aggregate calculations using advanced functions and wildcard techniques
Confidently use key Excel functions such as DSUM, SUMPRODUCT, SUMIFS, OFFSET, CHOOSE, INDEX, MATCH, ROWS, and INDIRECT
Apply advanced techniques for summarising and presenting data, including mini–Pivot Tables and Scenario Manager
Build interactive dashboards, automate processes with simple VBA, and apply custom formatting
Course Coverage
This course will cover:
Dynamic, low-maintenance data set design
Advanced listing techniques and dependent lists
Dynamic selection tips and unique value extraction
Expandable named ranges and multi-nested functions
Structured table references and expandable referencing
Aggregate calculations using wildcards
Key Excel functions:
DSUM, SUMPRODUCT, SUMIFS (building conditions)
OFFSET, CHOOSE, INDEX, MATCH
ROWS and INDIRECT
Advanced summarisation and presentation techniques:
Mini Pivot Table reports
Fast calculations with Data Tables
Scenario Manager for data modelling
Bonus topics:
Dashboard techniques for interactive screens
Simple VBA exercises to speed up processes
Custom formatting tips and tricks
All exercises and project files are provided to take home
Online Attendance Guidance
If you are attending this course online, please note:
You will need your own version of Excel open to complete the exercises
Using a single screen can be challenging due to switching between windows. For the best experience, we recommend:
Extending your computer to a second screen
Using two computers, or
Using a tablet for the online classroom and a computer for Excel
The course is based on Excel for Windows (Excel 2010 or later is recommended). Mac users are welcome, but some functionality differs, and the trainer will accommodate where possible.
Please read the following guidance carefully, which applies only if you are attending this course online:
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-set-up-dual-monitors or
Use 2 computers, or Use a tablet for the online classroom and a computer for the Excel
Please note that our courses are based on Excel for Windows and it is recommended that you are using at least Excel 2010 or a later version. MAC users are welcome but please note that there may be some tools and functionality that will differ from what is being presented. The presenter will accommodate for MAC versions on the course.
How is training delivered?
Learn through our live online classroom
Courses are delivered through Online Classroom Live, our premium online study mode comprising a virtual classroom with interactive features to replicate a real-life classroom..
Online Classroom Live offers a host of benefits to support your learning in the following ways:
Interactive features replicate a real-life classroom to keep you fully engaged
Course notes are shared by your tutor highlighting points and sharing knowledge
You will regularly share your opinions and ask questions via instant live chat messaging and contribute to class polls
Work with fellow learners in smaller group breakout rooms
Access library of online content and pre-recorded lectures 24/7 if you miss a class or want to recap on anything
View more about our Online Classroom Live.
Why choose BPP for training?
Ways to pay
Invoice your employer
Complete and return the booking form. We’ll contact your employer to arrange payment.Buy online
Choose a course from the above list and simply check out with your debit or credit cardBecome a BPP client
If you're an employer looking to upskill your staff get in touch about becoming a BPP client.FAQs
Most frequently asked questions.
If your employer is paying and wishes to be invoiced rather than pay online via credit card, you will need to complete this booking form and return it to us at service@bpp.com. If you are paying for yourself online or via the phone, you can request a copy of your settled invoice.
Related courses
Explore Data and Analytics courses with BPP.