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 and What is Covered
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 a worked example 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 FILTERING.
- 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.
Benefits
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 tools
for more succinct and surgical analysis of datasets.
- Understanding better the relationship between Excel and PowerPoint and issues associated with 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:
- Microsoft Office/ Apps Version
Participants should have a Microsoft 365 subscription but supported older desktop versions are workable.
- Mac Users – course not suitable
Power Pivot is not available on Microsoft Excel 365 for Mac. This course is not suitable for Excel for Mac users. Contact us before booking if you are in any doubt about your setup.
We prepare the files and deliver the course using Windows 11 but Windows 10 on Extended Support is fine.
This allows better viewing of the trainer's screen and relevant Microsoft 365 application or AI site. If you only have a single screen, please inform us prior to the course.
If you would like to test your connection and setup prior to your course, please contact info@professional.ie or 01 6627700 and we will arrange this with you.
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.
Note for Legacy CPA Members
Members who joined Chartered Accountants Ireland following the amalgamation on 1 September, please contuine to book on the CPA Portal here to ensure you receive your member discount.
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.
LOADING...