ICAS ICAS logo

Quicklinks

  1. About Us

    Find out about who we are and what we do here at ICAS.

  2. Find a CA

    Search our directory of individual CAs and Member organisations by name, location and professional criteria.

  3. CA Magazine

    View the latest issues of the dedicated magazine for ICAS Chartered Accountants.

  4. Contact Us

    Get in touch with ICAS by phone, email or post, with dedicated contacts for Members, Students and firms.

Login
  • Annual renewal
  • About us
  • Contact us
  • Find a CA
  1. About us
    1. Governance
  2. Members
    1. Become a member
    2. Newly qualified
    3. Manage my membership
    4. Benefits of membership
    5. Careers support
    6. Mentoring
    7. CA Wellbeing
    8. More for Members
    9. Area networks
    10. International communities
    11. Get involved
    12. Top Young CAs
    13. Career breaks
    14. ICAS podcast
    15. Newly admitted members 2022
    16. Newly admitted members 2023
  3. CA Students
    1. Student information
    2. Student resources
    3. Learning requirements
    4. Learning updates
    5. Learning blog
    6. Totum Pro | Student discount card
    7. CA Student wellbeing
  4. Become a CA
    1. How to become a CA
    2. Routes to becoming a CA
    3. CA Stories
    4. Find a training agreement
    5. Why become a CA
    6. Qualification information
    7. University exemptions
  5. Employers
    1. Become an Authorised Training Office
    2. Resources for Authorised Training Offices
    3. Professional entry
    4. Apprenticeships
  6. Find a CA
  7. ICAS events
    1. CA Summit
  8. CA magazine
  9. Insight
    1. Finance + Trust
    2. Finance + Technology
    3. Finance + EDI
    4. Finance + Mental Fitness
    5. Finance + Leadership
    6. Finance + Sustainability
  10. Professional resources
    1. Anti-money laundering
    2. Audit and assurance
    3. Brexit
    4. Business and governance
    5. Charities
    6. Coronavirus
    7. Corporate and financial reporting
    8. Cyber security
    9. Ethics
    10. Insolvency
    11. ICAS Research
    12. Pensions
    13. Practice
    14. Public sector
    15. Sustainability
    16. Tax
  11. CPD - professional development
    1. CPD courses and qualifications
    2. CPD news and updates
    3. CPD support and advice
  12. Regulation
    1. Complaints and sanctions
    2. Regulatory authorisations
    3. Guidance and help sheets
    4. Regulatory monitoring
  13. CA jobs
    1. CA jobs partner: Rutherford Cross
    2. Resources for your job search
    3. Advertise with CA jobs
    4. Hays | A Trusted ICAS CA Jobs Partner
    5. Azets | What's your ambition?
  14. Work at ICAS
    1. Business centres
    2. Meet our team
    3. Benefits
    4. Vacancies
    5. Imagine your career at ICAS
  15. Contact us
    1. Technical and regulation queries
    2. ICAS logo request

Data analysis with Excel – advanced

Member price in Scotland and for Online Live: £335 + VAT
Non-member price in Scotland and for Online Live: £395 + VAT

Member price in England: £566 + VAT
Non-member price in England: £666 + VAT

Please note, due to differences regarding external costs, courses outside of Scotland are priced separately. ICAS members are eligible for a discounted price on all courses running in England.

Timing: 9.30am-4.30pm


Online Live, 20 April 2023
Online Live, 3 August 2023
Online Live, 25 October 2023

Book with BPP


London, 5 July 2023 
London, 14 December 2023

Email BPP to book


Edinburgh, 12 December 2023

Book with BPP


Overview

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.

Who should attend

The course material includes advanced features of Excel and participants will gain the maximum benefit from this course if they have already attended the Data analysis with Excel – intermediate course and / or are already competent spreadsheet users. 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:

  • Use some of the functions explained on the Intermediate level of this course
  • Navigate confidently in Excel
  • Use absolute cell references (e.g =$A$1)
  • Create and use nested functions, or at least have an understanding of the benefits of nesting functions together.

What you will 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.

Information for Online Live dates

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

Questions?

BPP | Tel: 0330 060 3303 | Email: ldicas@bpp.com

Data analysis with Excel - intermediate

"This course involves analysing large data sets, familiarity with a variety of reporting tools, array formulae and CSE keystrokes, nesting…

ICAS logo

Footer links

  • Contact us
  • Terms and conditions
  • Modern slavery statement
  • Privacy notice
  • CA magazine

Connect with ICAS

  • Facebook (opens new window) Facebook Icon
  • Twitter (opens new window) Twitter Icon
  • LinkedIn (opens new window) LinkedIn Icon
  • Instagram (opens new window) Instagram Icon

ICAS is a member of the following bodies

  • Consultative Committee of Accountancy Bodies (opens new window) Consultative Committee of Accountancy Bodies logo
  • Chartered Accountants Worldwide (opens new window) Chartered Accountants Worldwide logo
  • Global Accounting Alliance (opens new window) Global Accounting Alliance
  • International Federation of Accountants (opens new window) IFAC
  • Access Accountancy (opens new window) Access Acountancy

Charities

  • ICAS Foundation (opens new window) ICAS Foundation
  • SCABA (opens new window) scaba

Accreditations

  • ISO 9001 - RGB (opens new window)
© ICAS 2022

The mark and designation “CA” is a registered trade mark of The Institute of Chartered Accountants of Scotland (ICAS), and is available for use in the UK and EU only to members of ICAS. If you are not a member of ICAS, you should not use the “CA” mark and designation in the UK or EU in relation to accountancy, tax or insolvency services. The mark and designation “Chartered Accountant” is a registered trade mark of ICAS, the Institute of Chartered Accountants of England and Wales and Chartered Accountants Ireland. If you are not a member of one of these organisations, you should not use the “Chartered Accountant” mark and designation in the UK or EU in relation to these services. Further restrictions on the use of these marks also apply where you are a member.

ICAS logo

Our cookie policy

ICAS.com uses cookies which are essential for our website to work. We would also like to use analytical cookies to help us improve our website and your user experience. Any data collected is anonymised. Please have a look at the further information in our cookie policy and confirm if you are happy for us to use analytical cookies: