Advanced Excel Training

Day 1 - Data Foundations & Transformation  

Objective  

Enable participants to structure and clean real-world Excel data to create a reliable foundation for analysis.  

Theory & Concepts  

• Why data structure matters for analysis and automation  
• Common real-world data quality issues (text, numbers, dates)  
• Raw data vs clean data: separation of concerns   
• Principles of reproducible data cleaning  
• When to use formulas vs Power Query  

Key Topics  

• Structuring datasets using Excel Tables  
• Best practices for working with raw vs clean data   
• Cleaning and standardising text fields  
• Identifying and fixing hidden data quality issues  
• Handling missing and inconsistent numeric values  
• Cleaning and standardising date fields for time-based analysis  
• Creating calculated business fields such as variance and status  
• Using lookup logic to standardise categories  
• Introduction to Power Query as an alternative data transformation approach  

Hands-on Practice (Supervised Lab)  

Participants will independently:  

• Convert raw datasets into structured tables  
• Create a clean data layer from raw data  
• Standardise text, numeric, and date fields  
• Apply basic business calculations  
• Validate data quality and identify errors                  

Day 2 - Analysis with Advanced Pivot Tables  

Objective   

Analyse cleaned data using advanced PivotTable techniques to uncover trends, variances, and performance insights.  

Theory & Concepts  

• Analytical thinking: turning data into questions  
• Time-based analysis concepts  
• Comparative and trend analysis techniques  

Key Topics  

• Building core PivotTables for budget vs actual analysis  
• Variance analysis by project and department  
• Time-based analysis using grouped dates  
• Categorising numerical data into meaningful ranges  
• Creating advanced pivot metrics  
• Trend analysis using running totals  
• Designing multiple analytical views from the same dataset  
• Interactive filtering using slicers and timelines  
• Connecting slicers across multiple PivotTables 

Hands-on Practice (Supervised Lab)  

Participants will independently:  

• Build multiple PivotTables from the same dataset  
• Analyse variance by different dimensions  
• Perform time-based and trend analysis  
• Create interactive views using slicers and timelines  
• Answer business questions using PivotTables

Day 3 - Visualisation, Dashboards & Automation  

Objective  

Transform analysis into executive-ready dashboards and introduce automation using Python in Excel.  

Theory & Concepts  

Visualisation & Storytelling  

• How decision-makers consume data  
• Choosing visuals based on insight, not aesthetics   
• Avoiding common dashboard design mistakes  
• Principles of effective data storytelling  

Automation & Python in Excel  

• Where Excel formulas and PivotTables reach their limits  
• Why automation matters in modern analytics  
• When to use Excel vs Python  
• Overview of analytical workflows using Python  

Key Topics  

Visualisation & Dashboards  

• Applying insight-driven conditional formatting  
• Highlighting performance using indicators  
• Choosing the right charts for different analytical questions  
• Creating charts directly from PivotTables  
• Designing clean, interactive dashboards  
• Dashboard layout, alignment, and usability best practices  

Automation with Python in Excel  

• Overview of Python integration within Excel  
• Importing Excel data into Python  
• Data exploration and distribution analysis  
• Identifying outliers and exceptions  
• Ranking and summarising performance  
• Creating charts using Python  
• Comparing Excel vs Python approaches for analysis and automation 

Handson Practice (Supervised Lab)  

Participants will independently:  

• Build an interactive dashboard from their analysis   
• Apply conditional formatting to highlight insights   
• Create charts for executive reporting  
• Perform basic automated analysis using Python in Excel   
• Compare manual vs automated approaches  

Outcomes  

• Executive-level interactive dashboard  
• Strong data storytelling skills  
• Understanding when and how to use Python to extend Excel