Current students
- Students
  
  View all the services available for students of the Institute
  
  Read More
Becoming a student
- Becoming a
  student
  
  Study with us
  
  Read More
Members
- Members
  
  View member services
  
  Read More
Employers
- Employers
  
  Services to support your business
  
  Read More

☰

Find a firm
Jobs

☰

Home
Knowledge centre
Professional development
About us
Shop
News

View Cart

0 Item

LOADING...

Please do not leave or close this window while the request is processing.

Data Analysis - Excel 365 – Power Query, Power Pivot, Dashboard Production and beyond

Summary

This course introduces two of the most powerful Excel 365 reporting and analysis tools - Power Query and Power Pivot, using them to produce an Excel Dashboard. We will examine Excel tools for ‘What if’ and newer techniques in the very important data analysis areas of LOOKUP (especially XLOOKUP) and FILTERING. Finally, we will use the Excel Dashboard we have created to communicate the results via exchanges between Excel and PowerPoint.

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

Important Notes

Two monitors: Having two monitors is very strongly recommended for this training. It allows you to view the trainer's 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 trainer's 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.

Category:

Technology and data

People at meeting

Travel Discounts

Customers who viewed this item also viewed

AI-Driven Data Automation

This course shows participants how to automate data collection, processing, and reporting using AI, PowerQuery, VBA, and scripting tools. Learn to streamline workflows, reduce manual effort, and improve data accuracy. Automate report generation, reconciliation, and compliance tasks while leveraging AI for smarter decision-making. Gain hands-on experience building efficient, end-to-end data automation solutions.

View product

Data Visualisation for Accountants with Excel and PowerPoint (ROI/NI)

This course is designed for those who want to appraise and improve the way they utilise the features of MS Excel and MS PowerPoint to communicate effectively. It will challenge how we present information and explore how we can engage our audience and transfer technical and financial information clearly and simply.

View product

SQL for Finance Professionals (ROI/NI)

This one-day course equips finance professionals with practical SQL skills for data analysis and reporting. Participants will learn SQL fundamentals, connect Excel to cloud databases, write tailored financial queries, and use Excel's data modelling and visualisation tools. With hands-on exercises, this course enhances financial data retrieval, aggregation, and visualisation, making data-driven decision-making more efficient.

View product

Building Effective Financial Models with Excel (ROI/NI)

The aim of the course is to provide a general introduction to spreadsheet modelling and related analysis. During the course, participants will create a model 'from scratch' and the use of this model for various outputs, as outlined below (ROI, dashboard outputs etc.). This is augmented by a feed of given data into the model from 'history' to allow comparisons etc. with the model outputs.

View product

AI-powered Presentations and Reports(ROI/NI)

Advances in AI coupled with modern features in PowerPoint greatly simplify creating eye-catching and informative presentations. Effective usage of Copilot or ChatGPT cuts the time and manual input required for presenting and reporting on data for regular meetings, ad-hoc assignments or regulatory requirements. This hands-on course will guide you through using AI Tools with Microsoft 365 to efficiently and professionally present information.

View product