Online Excel Data Analysis & Pivot Tables Self-Paced Course

Getting Started

Set yourself up for success before diving in.  

• Learn how to get the most out of your course  
• Download your resources and cheat sheets for quick reference  
• Tips for making the most of the videos  
• Join our Learning Community for extra support  
• Learn how to join our live group meetups with Sharyn  

Module 1 – Importing and Saving Text Files

Learn how to confidently import, clean, and save text files without losing data or formatting. 

• Understand what text files are and when to use them 
• Control how CSV files open in Excel to avoid formatting issues 
• Import fixed-width text files accurately 
• Save imported text files as Excel workbooks for easier editing 
• Export Excel data as text files for sharing or system uploads 

Module 2 – Use Lookup Functions to Cross-Reference Data

Master lookup functions to quickly find and match information across your spreadsheets. 

• Understand how lookup functions work and when to use them 
• Use VLOOKUP to find and return data from another list 
• Compare two lists of data using VLOOKUP 
• Control VLOOKUP results and avoid common errors like #N/A 
• Perform horizontal lookups with HLOOKUP 
• Create flexible two-way lookups using MATCH and INDEX 
• Combine MATCH and INDEX for dynamic row and column lookups 
• Use XLOOKUP for more powerful and flexible lookups 
• Replace older functions with XLOOKUP for easier, error-free formulas 
• Handle approximate matches and advanced lookup scenarios 
• Join text values to look up multiple criteria in one formula 

Module 3 – Preparing Data for Reporting

Fix common data issues so your reports and formulas work perfectly.

• Prepare data so formulas and lookups work correctly  
• Find and remove duplicate data  
• Highlight errors and duplicates using Conditional Formatting  
• Convert numbers stored as text into real numbers  
• Split text in one column into multiple columns  
• Combine content from multiple cells using text joining functions  
• Remove unnecessary spaces—even the tricky ones TRIM can’t catch  
• Clean text further using SUBSTITUTE for unwanted characters  
• Check text length with LEN to validate data  
• Change text case to proper, upper, or lower  
• Add leading zeros without breaking your data  
• Extract specific text using functions like LEFT, RIGHT, MID, TEXTBEFORE, and TEXTAFTER  
• Clean and transform messy data instantly using smart tools like Flash Fill and Copilot for Excel  

Module 4 – Creating Dynamic Tables

Turn your data ranges into dynamic tables for easier analysis, filtering, and reporting. 

• Insert and format tables to make your data easier to manage 
• Resize tables as your data grows or shrinks 
• Use built-in filters and slicers for quick, interactive analysis 
• Apply filters to view subtotals and focus on key data segments 
• Create calculated columns for automatic, consistent formulas 
• Add a Total Row to summarise your data instantly 
• Use structured references and table components for dynamic formula updates and seamless PivotTable refreshes 
• Practise using table references in multiple formula scenarios 
• Convert a table back to a normal range when needed 

Module 5 – Pivot Tables

Summarise, analyse, and present your data with powerful PivotTable tools. 

• Create PivotTables to summarise large datasets quickly 
• Understand PivotTable components and customise the layout 
• Manage Grand Totals and Subtotals for transparent reporting 
• Create custom names for Value fields for better readability 
• Control how data is summarised and formatted 
• Apply number formatting for professional reports 
• Place multiple fields in the Values area for deeper analysis 
• Display data as percentages to show comparisons 
• Filter data using filters, slicers, and timelines 
• Generate individual PivotTables by field with one click 
• Drill down to display underlying detail 
• Group data by categories, dates, or time periods 
• Sort data and highlight the Top 10 values 
• Create your own formulas using PivotTable data 
• Add Calculated Fields for custom calculations 
• Apply styles for a polished, professional look 
• Refresh PivotTables to update with new data 
• Reset the data source when your dataset changes 

Module 6 – Pivot Charts

Visualise your PivotTable data with clear, dynamic charts. 

• Create PivotCharts to bring your data to life 
• Change chart design and layout for better storytelling 
• Add chart elements for clarity and impact 
• Hide field buttons for a cleaner look 
• Format PivotCharts for a professional finish 
• Create combination charts for comparing different data series 

Module 7 – Data Validation

Control data entry and create user-friendly spreadsheets.  

• Create drop-down lists to simplify data entry 
• Use data validation to prevent incorrect entries 
• Edit, copy, and delete validation settings easily 
• Create dynamic drop-down lists that update automatically 

Module 8 – Power Query

Combine and transform data from multiple sources in minutes. 

• Build one report from several CSV files in a folder 
• Combine and report on data from multiple Excel files 
• Append and consolidate data from multiple worksheets 

Passing the Finish Line 

Celebrate your achievement and wrap up your learning journey.  

• Claim your Certificate of Completion to showcase your new skills 
• Enjoy a final message from Sharyn with tips for your next steps