Management & Soft Skills
Excel for Data Analysis and Reporting
A practical programme in using Excel to organise and analyse operational data and prepare periodic reports: functions, data validation, PivotTables, charts and dashboards, on data representing maintenance records, analysis results and production.
Overview
About the programme
Duration
3 days
Training hours
15 hours
Delivery
Training hall · Virtual · At your premises
Language
Arabic or English
Objectives
Training outcomes
- Organise data in formatted Excel tables, and control data entry with data validation and drop-down lists.
- Use basic, conditional, lookup, date and text functions.
- Summarise data with PivotTables and filter it with slicers.
- Create charts suited to the type of data and the intended message.
- Build a simple dashboard that updates when new data is added.
Content
What the programme covers
Day 1: organising data and functions
- Formatting tables and cells, AutoFill, and relative and absolute references.
- Data validation and drop-down lists, sorting and filtering, and conditional formatting.
- Basic and conditional functions: SUM, AVERAGE, COUNT, IF, SUMIFS, COUNTIFS and IFERROR.
- Date, text and lookup functions: TODAY, NETWORKDAYS, TEXT, VLOOKUP and XLOOKUP.
Day 2: analysis with PivotTables
- Converting a range to an Excel table and its benefits for updating and formulas.
- PivotTables: grouping, filtering, calculated fields and date grouping.
- Slicers and timelines, and subtotals.
- Introduction to Power Query for cleaning imported data and combining files.
Day 3: charts and dashboards
- Choosing the right chart: column, line, scatter and combination charts, and avoiding misleading charts.
- Designing a dashboard: key indicators, charts linked to PivotTables, and slicers.
- Preparing, printing and exporting the periodic report to PDF, and protecting sheets.
- Applied project: a dashboard built from operational data, followed by the assessment.
Who it is for
Target audience
Staff, technicians, engineers and section coordinators who collect operational data and prepare periodic reports; a working knowledge of basic Excel is required.
Method
Delivery method
Hands-on computer work throughout, in which every participant works on data files representing maintenance records, analysis results and production data, ending with an applied project prepared by each participant.
Questions
FAQ
Which version of Excel is needed?
Microsoft 365 or Excel 2021 is preferred, as some functions such as XLOOKUP are not available in older versions; alternatives are shown where needed. Each participant needs a computer with Excel installed.
Is the programme suitable for beginners?
A working knowledge of basic Excel is required. A beginners’ version can be delivered, focusing on the interface, formatting, basic functions, sorting, filtering and printing.
Can we work on our own data?
Yes. The organisation can send the centre a sample of its data before the programme, with personal or confidential data removed, and the exercises and applied project are built on it.
A programme tailored to your organisation
The duration and topics of the programme are adjusted to the organisation's needs, and it is delivered at its site, in a training hall arranged by the Center, or virtually.