Data Analysis with Excel - Intermediate & Advanced - ICAS
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 £454.80Online Live from £454.80All courses Inc VAT and excludes exams
Choose your courses
Data Analysis with Excel - Advanced ICAS
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.- No extra learning materials
- No extra learning materials
Data Analysis with Excel - Intermediate ICAS
This one-day course offers quick tips to better utilising the power of Excel. The course involves analysing large data sets, using a variety of reporting tools, array formulae and CSE keystrokes, nesting functions, data manipulation, as well as an intro into macro automation. This course contains some more advanced functions and complex nesting techniques which are used to control datasets and charts. There are segments where we clean up data and create robust formulas.- 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
Decision makers
Financial controllers
Junior accountants
What will you learn?
Time Savers, fast keys
Pivot Tables & Charts
Control a chart via a Pivot Tabl
Summarise data and automate the chart
Deep dive into the ‘LOOKUP Family’
Flexible lookup techniques
Understanding the limitations of a VLOOKUP
Extensive practice with XLOOKUP
Practical solutions for complex data sets
Functions
Logical
Conditional & Error
Text-string (for manipulation)
Nesting (for increased power)
Array
The CSE keystroke method
Simplifying IF functions
Multi-cell array functions
Uncover hidden trends in business data
The calculative technique using buckets and intervals
Complex problem solvingQuick remodelling to meet targets
Bonus topics:
Extensive Conditional Formatting
Using formula-based rules
Homework topics for further study
Increased automation techniques using macros
Applying meaningful macro functionality
Automating formulas
All exercises and project files used on the course will be available to take home
How will this help your career
Interpret, communicate and search for data from large quantities of information.
Learn how to synthesise information into a logical framework for analysis.
Summarise messy data into a meaningful format.
Gain skills in performing advanced searches and lookups for data extraction
Who is it for?
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.
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
Finance professionals
Data analysts
Spreadsheet authors
Excel power users
Finance managers
Decision makers
Financial controllers
Junior accountants
What will you learn?
One of the key themes for the session is to keep data sets dynamic and expandable for minimum maintenance. We explore advanced functions and more complex techniques in nesting and facilitating calculations.
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 summarising & 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.
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.