Format
This course will be delivered via Zoom. The Zoom link will be emailed to participants the day before the course is due to take place.
Who should attend
This course is aimed at experienced Excel users who have to:
Analyse data using Excel
Use data which has been analysed in Excel Present
Excel-based data to an audience
What is covered and how?
This course has four main parts:
Using worked examples to introduce two of the most powerful Excel tools; Power Query and Power Pivot. The ultimate output here will be an Excel-based dashboard.
Examining some self-generated techniques and the Excel tools for 'What if' simulations - both input to output and output to input.
Comparing legacy Excel to newer techniques in the very important data analysis areas of LOOKUP and the 365 based FILTERING function.
Using the dashboard output of 1 above, communicating the
results via exchanges between Excel and PowerPoint.
This course will operate with
a mixture of instructor-delivery and practical exercises using a case-study
approach. The emphasis will be on delivering practical techniques which can be
used in everyday work, rather than on esoteric theory.
Key learning outcomes
By the end of this course, participants will benefit from:
- An introduction to Power Query, giving an appreciation of its power and where you might use it in your organisation.
- An introduction to Power Pivot to help participants see where they might enhance reporting by switching to from legacy pivot tables.
- Seeing how Excel can assist in sanity checking and validating analyses, forecasts etc. through the various WHAT IF tools available.
- Exploring the use of the new LOOKUP and FILTER function for more succinct and surgical analysis of datasets.
- Understanding better the relationship between Excel and PowerPoint and issues associated.
- Creating a dashboard in PowerPoint from linking to Excel and validating and repairing links between the two where required.
Course Content and approach
All of the techniques covered will be applied to the same supplied case study, the output of which will be a number of reports culminating in a dashboard:
- Querying using Power Query
- The Data Model concept
- Power Pivot
- DAX measures and Syntax
- What If analysis: IF, LOOKUP, Data Tables, Scenario Manager, Goal Seeker, Solver and FORECAST.
- XLOOKUP and XMATCH - superseding VLOOKUP AND HLOOKUP - with real-world examples and dynamic array formulas
- Filtering - a comparison of the legacy desktop with the FILTER function making the old methods defunct
- Excel and PowerPoint - linking vs embedding, using the
CAMERA tool for live-linking and applying these techniques to our Case Study
dashboard
All participants should please note the following
- Two monitors. Having two monitors is very strongly recommended for this training. It allows you to view the trainers PC in one monitor and the application that is the subject of the training in the other. On a single screen it can be difficult to arrange the trainers screen and your own application comfortably. If you only have a single screen please inform the trainer - ideally before the test session - and they can help you to get the best from the training session.
- Microsoft Office version. We use Microsoft Office 365 Professional. If you are using an older version please be aware that you might not be able to do some of the exercises that are using the latest features.
- Windows 10/11. We prepare the files and deliver the
course using Windows 10/11. If you are using a MacBook or other platform please
be aware that there can sometimes be incompatibilities and there are certain
features that are not available on the Macintosh edition of Microsoft Office
365 Professional. These issues can be identified and resolved during the test
session.
Speaker bio
Professional Training is a group of experienced specialists providing focused training in IT and Management skills. Our trainers use a combination of tailored, flexible approaches and real-life business knowledge to develop and provide techniques and solutions for immediate implementation by our course participants. We have delivered a range of CPD programmes with Chartered Accountants Ireland since 1997. Website: www.professional.ie
Contact us
If you have any questions regarding this or any other CPD course or requirements, please feel free to contact the team here or call us at
(+353) 01 523 3930