Course description
Overview
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 tables and charts. There are segments where we clean up data and create robust formulas.
Who is this 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
- Pivot Tables & Charts
- Control a chart via a Pivot Table
- Summarise data and automate the chart
- The ‘LOOKUP Family’
- Flexible lookup techniques
- Understanding the limitations of a VLOOKUP
- 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
- Analyse trends in business data via a Frequency Distribution Table
- The calculative technique using “bins array”
- The non-calculative technique via a Pivot Table
- Bonus topics:
- Complex problem solving
- Extensive Conditional Formatting
- Using formula-based rules
- Introduction to scenario building
- Homework topics for further study
- Increased automation techniques using macros
- Applying meaningful macro functionality
- Viewing and simple editing of macro VBA code
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
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.
Other related courses
Introduction to Excel
Data Analysis with Excel - Advanced
Accounting & Financial Analysis - Introduction
Do you work at this organisation and want to update this page?
Is there out-of-date information about your organisation or courses published here? Fill out this form to get in touch with us.
Upcoming start dates
Suitability - Who should attend?
This course will benefit those using Excel on a regular basis and wanting to learn more about performing various forms of analysis and exploring more complex functions and calculations. Also support staff, finance professionals and analysts wanting to increase their functional knowledge of Excel.
It is strongly recommended that this course is attended prior to Data Analysis with Excel – Advanced as this course prepares delegates in working with many of the core functions and disciplines that are studied in more detail on the advanced course.
Outcome / Qualification etc.
By the end of the course, delegates will be able to:
- Apply more complex calculations to their spreadsheets
- Learn to make quicker decisions to synthesise and summarise lots of information
- Work with pivot charts and tables
- Create bespoke formats and write formula-based rules
- Apply advanced text manipulation and other types of calculations to create error-free spreadsheets
- Perform various forms of analysis on business data
Learning outcomes include obtaining a greater understanding of:
- Time savers / fast keys / shortcuts
- Key functions
- IF / MAX / IFERROR / ISERROR
- INDEX / MATCH / OFFSET / LOOKUP / VLOOKUP
- LEFT / RIGHT / MID / LEN
- Introduction to ARRAY functions
- Complex nesting techniques
- Lookup in-depth
- Flexible lookup techniques for unconventional datasets
- Problems and limitations of a VLOOKUP
- Practical solutions for complex datasets
- Data manipulation
- Advanced use of text-string functions
- Text extraction with nesting
- Additional forms of analysis
- Control a chart via a Pivot Table
- Summarise data and automate chart elements
- Frequency distribution table
- Bespoke and extensive conditional formatting
- Create immediate visuals using Data Bars
- Writing formulas to control range formatting
- Introduction to scenario building
- Experiment with goal seek
- Introduction to solver analysis
- Bonus macro and VBA exercises
- Applying meaningful macro functionality
- Viewing and simple editing of macro VBA code
All exercises and project files used on the course will be available to take home. Laptops with Excel 2013 will be provided on the course. The knowledge and skills gained can be used across all modern versions of Excel.
Course delivery details
- Location Name: Online Live
- Learning Mode: Blended