Data Analysis & Reporting with Microsoft Excel

---

Course Content

1. Preparing Data for Analysis
   • Understanding structured data and good spreadsheet design
   • Organising data into rows, columns and fields
   • Converting ranges into Excel Tables
   • Sorting and filtering data
   • Finding and removing duplicate records
   • Identifying missing, inconsistent and incorrect data
   • Working with text, numbers and dates
   • Preparing data for reliable analysis and reporting
2. Analysing Data with Excel Formulas
   • Using SUM, AVERAGE, MIN and MAX for analysis
   • Using COUNT, COUNTA and COUNTBLANK
   • Using SUMIF and SUMIFS
   • Using COUNTIF and COUNTIFS
   • Using AVERAGEIF and AVERAGEIFS
   • Using IF for business rules and classifications
   • Using XLOOKUP to retrieve related information
   • Using percentage calculations for growth, variance and performance
3. Cleaning and Transforming Data with Power Query
   • Introduction to Power Query
   • Importing data into Excel
   • Changing data types
   • Removing unnecessary rows and columns
   • Handling missing and inconsistent values
   • Splitting and combining columns
   • Filtering and transforming data
   • Combining data from multiple sources
   • Refreshing queries when source data changes
4. Analysing Data with PivotTables
   • Creating a PivotTable from business data
   • Adding fields to rows, columns, values and filters
   • Summarising data using Sum, Count and Average
   • Grouping dates and numerical values
   • Sorting and filtering PivotTable results
   • Displaying values as percentages
   • Using slicers for interactive filtering
   • Answering business questions using PivotTables
5. Charts, Visualisation and Reporting
   • Choosing appropriate charts for different types of data
   • Creating column, bar, line and pie charts
   • Creating PivotCharts
   • Displaying trends and comparisons clearly
   • Using conditional formatting to highlight important information
   • Creating Key Performance Indicators (KPIs)
   • Designing clear and professional reports
   • Avoiding misleading or unnecessary visualisations
6. Building an Interactive Excel Dashboard
   • Planning a simple business dashboard
   • Combining PivotTables, charts and KPIs
   • Adding slicers and interactive filters
   • Presenting important business metrics
   • Organising dashboard elements clearly
   • Creating reports that can be refreshed when data changes
7. Practical Data Analysis & Reporting Project
   • Work with a realistic business dataset
   • Clean and prepare the data
   • Analyse performance using formulas and PivotTables
   • Identify trends, patterns and exceptions
   • Create charts and visualisations
   • Build a simple interactive dashboard
   • Summarise key findings and business insights
   • Present the completed analysis and report

---

Course Dates, Prices & Enrolment

---

Price Calculator & Booking Request Form

Calculate prices for Corporate, 1-on-1 or group training and request a booking.

Do you have a special training requirement or unable to find any suitable training date? Please complete and submit the booking request form, if you want to:

The price person is less when you book a course for more people. You can find the price per person and the total cost by changing the values of the training hours and the number of people below: