Excel – advanced
This course focuses on pivot tables to comparing percentages, create field calculations, and build reports. It also looks at utilising powerful formulas to generate results from a range of criteria and retrieving data from an external file with lookup formulas.
Also included are what-if analyses tools to meet projected targets and looks at forecasting by generating varying scenarios.
This course finishes with a look at recording macros to quickly automate what would otherwise be repetitive manual tasks. Understanding how macros work and how to utilise them to add an extra dimension to spreadsheets.
Learning objectives
- Nest formulas
- Get the most from pivot tables
- Use conditional formatting
- Write array formulas
- Explore the lookup functions
- Calculate by criteria
- Use ‘goal seek’ and ‘scenario manager’ for what-if analysis
- Record macros
Pre-requisites
Attendees are required to be familiar with Excel intermediate level content, for example knowledge of writing formulas, and basic conditional formatting before attending this course.
Format
A very practical, interactive one-day session for a maximum group size of 12. Comprehensive materials provided.
Expert trainer
Alan now freelances and almost exclusively specialises in analytics using Microsoft Power BI as a platform, however he still provide clients with Excel training when requested to. His previous 25+ years within this sector includes both freelancing, and full time employment at various companies including, Lead Trainer at Go Courses, and Training Manager at Ultima Concepts.
See what some of the participants have said about the workshops he’s delivered for us:
‘Fantastic. Well-paced and easy to follow.’
‘Great experience.’
‘Very personable, relatable, patient and able to present complicated processes in an understandable way.’
‘Friendly and good at checking people are up to speed.’
‘Very good and explained and answered all questions.’
‘Explains everything well.’
‘Excellent teacher and very intelligent.’
‘Very knowledgeable would love to have him again if there is another course – thank you.’
‘Alan explains things very clearly and addresses issues on questions before they are visible. Thank you very much!’
1 Nesting formulas
- Principals of nesting formulas together
- Combining the AND function, and OR function with an IF function to answer questions based on multiple criteria in your data
2 Data validation
- Using data validation
- Control data input
- Set up criteria for entry of text, number, and date data types
- Create a dropdown list
3 Lookup functions
- Retrieving data from external files using an INDEX and MATCH function
- VLOOKUP and XLOOKUP function
- Understand all the different parts that make up an XLOOKUP
- Exact match look up and closest match lookup
- Control a lookup’s search direction
4 Advanced pivot tables
- Generate subtotals by grouping date, number, and text values
- Run percentage analyse
- Run comparison analysis
- Insert a field calculation into a pivot table
- Setup a worksheet as a report dashboard
5 Advanced conditional formatting
- Using conditional formatting to alert us of variables in data
- Colour table rows based on criteria within it
- Apply colour to approaching dates
- Work with different condition rule types
6 Summing by criteria
- Use a SUMIFS function to sum numbers based on a range of different criteria
7 What-if analysis
- Use Goal Seek to reach financial targets
- Produce forecasted reports with the Scenario Manager
8 Recording macros
- Understand macro security
- What is the difference between a relative referenced macro and no relative?
- Record, run, and edit macros
- Save files as a Macro Enabled Workbook
- A brief look at VBA (Visual Basic for Applications) coding
- Adding buttons to Excel to run macros
Any questions? Please just give us a call on 01582 463463 – we’re here to help!