Data & Business Intelligence · Transights certificate
PEX
Data Analysis with Power Excel (PEX)
Why data analysis with Power Excel matters
Excel is where many business analyses begin. When analysts can import data from several sources, clean it with repeatable steps and relate tables in a data model, a monthly report becomes a refresh rather than a rebuild, and managers can slice the same numbers by product, region or period without asking for a new file.
Without these tools, the same data is copied, pasted and cleaned by hand every cycle, large files slow down, and lookups across flat tables break or disagree. This programme bridges everyday Excel and modern business analytics through hands-on exercises that end in an interactive dashboard, and leads to a Transights certificate of completion.
What you will be able to do
- Import, clean and combine data from files, folders and databases with Power Query
- Load tables into the Excel data model and create relationships between fact and lookup tables
- Build pivot tables from the data model, with slicers, timelines, rankings and running totals
- Write basic DAX measures and use CALCULATE to apply filters within them
- Use XLOOKUP, dynamic array functions and SUMIFS for analysis tasks
- Build an interactive dashboard and move Excel queries and data models into Power BI
Course outline: 8 modules
Each module below shows what it covers and what you will be able to do at the end of it.
1. Introduction to Data Analysis in Excel
Introduces the data analysis process and how Excel and its Power tools support modern business analytics.
- Describe the main stages of a data analysis process
- Structure data as Excel tables ready for analysis
- Identify when to use Power Query, Power Pivot and DAX
2. Data Preparation with Power Query
Covers importing, cleaning, reshaping and combining data from multiple sources using Power Query.
- Import data from files, folders and databases into Power Query
- Clean and reshape data with repeatable transformation steps
- Merge and append queries to combine data sets
- Refresh queries to update reports with new data
3. Data Modelling with Power Pivot
Covers building a data model in Power Pivot by loading tables and defining relationships between them.
- Load multiple tables into the Excel data model
- Create relationships between fact and lookup tables
- Explain the benefits of a data model over single flat tables
4. Pivot Tables and Advanced Summaries
Uses pivot tables built on single tables and data models to produce flexible, multi-level business summaries.
- Build pivot tables from tables and from the data model
- Group, filter and slice data using slicers and timelines
- Show values as percentages, rankings and running totals
5. Introduction to DAX
Introduces DAX for writing calculated columns and measures within the Excel data model.
- Distinguish between calculated columns and measures
- Write basic DAX measures for totals, counts and ratios
- Use CALCULATE to apply filters within measures
6. Advanced Excel Functions
Covers advanced worksheet functions, including lookup, dynamic array and conditional aggregation functions, for analysis tasks.
- Use XLOOKUP and INDEX/MATCH to retrieve data across tables
- Apply dynamic array functions such as FILTER, SORT and UNIQUE
- Summarise data with conditional functions such as SUMIFS and COUNTIFS
7. Data Visualisation and Dashboards
Covers designing charts and interactive Excel dashboards that present key business metrics clearly.
- Choose suitable charts for comparisons, trends and composition
- Build an interactive dashboard using pivot charts and slicers
- Apply layout principles for clear, readable dashboards
8. Integrating Excel with Other Tools
Covers connecting Excel with external data sources and other tools such as Power BI for wider analytics workflows.
- Connect Excel to external sources such as databases and online files
- Move Excel data models and queries into Power BI
- Share Excel analysis with colleagues through common business platforms
Programme details
| Award | Transights certificate of completion for Data Analysis with Power Excel. |
|---|---|
| Award criteria | Awarded on completing the programme; there is no external or vendor certification exam. |
| Duration | 24 training hours over 4 weeks. |
| Level | Intermediate. |
| Tools used | Microsoft Excel with Power Query, Power Pivot and DAX. |
| Prerequisites | Working knowledge of basic Excel (formulas, functions and simple tables), as covered in Excel for Business. |
| Format | Practical, hands-on exercises preparing, modelling and summarising business data, ending in dashboards. |
| Structure | Eight topics including Power Query, Power Pivot data modelling, pivot tables, DAX, advanced functions, dashboards and integration with other tools. |
| Next step | A natural bridge to Transights' Data Analysis with Power BI course, which reuses Power Query and DAX. |
Your path through PEX
- Enquire and choose your format. Classroom in Cairo, live online, or an in-house cohort for your team. You should already have a working knowledge of basic Excel formulas, functions and simple tables.
- Learn the eight modules with an instructor. 24 hours over 4 weeks, every live session recorded on our learning platform.
- Practise preparing, modelling and summarising data. Hands-on exercises with Power Query, Power Pivot and DAX, ending in dashboards.
- Receive your certificate. A Transights certificate of completion for Data Analysis with Power Excel, awarded on completing the programme. There is no external exam.
- Take the next step. Continue to Transights' Data Analysis with Power BI course, which reuses Power Query and DAX.
What you walk away with
- A repeatable way to prepare and model data that you can apply the next working day, so reports refresh instead of being rebuilt
- A Transights certificate of completion for Data Analysis with Power Excel
- Recordings of every live session and the course files on our learning platform
- A direct bridge to Data Analysis with Power BI, which reuses the Power Query and DAX skills you build here
Want the full picture?
The PEX brochure has the complete module outline, exam facts and delivery options in one PDF.
Download the PEX brochure (PDF) Enquire about the next cohort
How you will learn
- Classroom: Interactive sessions at our training and exam centre in Nasr City, Cairo, Egypt.
- Live online: Instructor-led virtual classes for participants anywhere in the region.
- In-house: A private cohort for your team, at your premises and tailored to your context.
- Every live session recorded: Watch any session again, or catch up on one you missed, on our learning platform.
- Official study material: Books, handbooks and course files for the programme, in one place.
Exam and certification
Transights
| Exams | No external exam |
|---|---|
| Certificate | Transights certificate of completion |
| Where you sit it | Awarded on completing the programme |
Who should attend
- Professionals handling large datasets
- Data analysts and finance professionals
- Managers upgrading analytical skills
- Users with basic Excel knowledge
Frequently asked questions
What is the PEX course?
Helps professionals turn Excel into a powerful analysis tool. Using Power Query, Power Pivot, DAX and advanced functions, it bridges the gap between Excel and modern business analytics.
Who should take PEX?
It suits: Professionals handling large datasets; Data analysts and finance professionals; Managers upgrading analytical skills; Users with basic Excel knowledge.
How long is the PEX course at Transights?
24 hours, 4 weeks. It runs live online, in class in Cairo, or in-house for company teams in Egypt and the GCC.
Is there an exam?
There is no external exam. Participants receive a Transights certificate of completion.