Advanced Excel for Data Analysis

Programme Summary

Reliable spreadsheet analysis begins with structured data, transparent calculations and checks that expose errors. This programme develops advanced Excel skills for cleaning data, applying modern formulas, summarising results and building refreshable reports. Participants work with realistic operational datasets, combine information using Power Query and create a concise analytical workbook that answers business questions without hiding assumptions or unexplained discrepancies.

Training Objectives

1 Strengthen disciplined preparation of data for spreadsheet analysis; 2 Develop efficient use of advanced formulas and lookups; 3 Improve repeatable data transformation and reporting workflows; 4 Build clear summaries charts and business analysis outputs; 5 Promote validation and transparent explanation of spreadsheet results.

Learning Outcomes

1 Clean and structure a supplied operational data table; 2 Apply lookup conditional and dynamic array formulas correctly; 3 Combine source files using a refreshable Power Query; 4 Build PivotTable summaries and purposeful analytical charts; 5 Validate totals and document workbook assumptions and limitations.

Target Audience

Business Analysts; Finance Officers; Inventory Analysts; Quality Analysts; HR Analysts; Operations Planners.

Target Industries

Manufacturing; Retail; Financial Services; Healthcare Administration; Logistics; Professional Services.

Course Contents

• Module 1 Analytical Workbook Design: Business questions and suitable data structure; Excel tables and structured cell references; Separating source data calculations and outputs.
• Module 2 Data Cleaning with Formulas: Text dates and numeric data types; Removing inconsistent spacing and duplicate records; Detecting missing values and data exceptions.
• Module 3 Conditional Calculations: IF logic and nested decision conditions; SUMIFS COUNTIFS and AVERAGEIFS applications; Avoiding inconsistent ranges and misleading totals.
• Module 4 Lookup and Matching Techniques: XLOOKUP exact matches and missing values; INDEX MATCH and legacy workbook compatibility; Validating keys before combining related information.
• Module 5 Dynamic Array Analysis: FILTER SORT and UNIQUE applications; Spill ranges and dynamic output behaviour; Resolving spill errors and dependency limitations.
• Module 6 Validation and Formula Checking: Data validation and controlled input choices; Error handling without concealing underlying problems; Formula tracing reconciliation and reasonableness checks.
• Module 7 PivotTable Analysis: Building summaries from structured source data; Grouping filtering and appropriate aggregation choices; Refreshing sources and checking PivotTable totals.
• Module 8 Charts and Analytical Presentation: Choosing visuals for specific business questions; Clear labels scales and comparison baselines; Avoiding misleading chart choices and clutter.
• Module 9 Power Query Fundamentals: Connecting to approved files and tables; Data profiling and repeatable transformation steps; Loading query results and refreshing data.
• Module 10 Combining and Reshaping Data: Appending compatible datasets and source files; Merging tables with validated relationship keys; Unpivoting cross tab data for analysis.
• Module 11 Scenario and Sensitivity Analysis: Input assumptions and alternative business scenarios; Goal Seek and simple sensitivity comparisons; Explaining model limitations and decision uncertainty.
• Module 12 Integrated Reporting Workbook: Combining formulas queries summaries and charts; Validating final outputs against source information; Documenting refresh steps and workbook handover instructions.

Assessment

• 60 single best answer questions, including applied scenarios; one mark per question.
• 90 minute invigilated digital examination with identity verification. Approved accessibility adjustments preserve the assessed competence.
• Written exam closed book; practical permits Excel and supplied function references. Internet and AI assistance prohibited.
• Individual applied assessment (45 minutes): Complete a supplied workbook by cleaning data, correcting two formulas, merging a reference table and producing a PivotTable with one chart. Reconcile the result to control totals and document a repeatable refresh procedure.
• Assessment criteria: Accuracy and robustness of formulas and transformations; Completeness of reconciliation and error checks; Clarity usability and repeatability of the analytical workbook.
• Pass each component separately: 45/60 written marks (75%) and at least 70% on the applied task.
• Individual results and feedback are provided. One reassessment may follow coaching under the published enrolment terms.

Certification

• Successful learners receive a TQCSI Arabia digital Certificate of Successful Completion.
• The certificate records the learner name, exact programme title, teaching hours, completion date and unique verification reference.
• Verified identity, full attendance and separate written and applied passes are required; all mandatory critical criteria must be met.
• Proposed issue target: within 30 calendar days of approved results and settlement of applicable fees.
• Records assessed advanced Excel analysis skills; it does not confer software vendor certification.
• Attendance confirmation may record completed hours for unsuccessful learners; it does not certify successful completion.

Programme Profile

Number of daysNumber of daysNumber of days

333

Total teaching hoursTotal teaching hoursTotal teaching hours

18 hours; breaks, pre course work and formal final assessments excluded.18 hours; breaks, pre course work and formal final assessments excluded.18 hours; breaks, pre course work and formal final assessments excluded.

Delegates allowedDelegates allowedDelegates allowed

Maximum 16 delegates.Maximum 16 delegates.Maximum 16 delegates.

Training prerequisitesTraining prerequisitesTraining prerequisites

Working knowledge of Excel formulas, cell references, sorting and filtering is required. Participants should complete the diagnostic; beginners should first undertake an introductory Excel programme.Working knowledge of Excel formulas, cell references, sorting and filtering is required. Participants should complete the diagnostic; beginners should first undertake an introductory Excel programme.Working knowledge of Excel formulas, cell references, sorting and filtering is required. Participants should complete the diagnostic; beginners should first undertake an introductory Excel programme.

Pre course assignment and assessmentPre course assignment and assessmentPre course assignment and assessment

Complete a 30 minute Excel diagnostic covering references, formulas and tables; confirm that the required desktop functions and Power Query are available.Complete a 30 minute Excel diagnostic covering references, formulas and tables; confirm that the required desktop functions and Power Query are available.Complete a 30 minute Excel diagnostic covering references, formulas and tables; confirm that the required desktop functions and Power Query are available.

End of course assessmentEnd of course assessmentEnd of course assessment

60 question written exam (90 minutes) and individual applied task (45 minutes).60 question written exam (90 minutes) and individual applied task (45 minutes).60 question written exam (90 minutes) and individual applied task (45 minutes).

Passing criteriaPassing criteriaPassing criteria

75% written and 70% applied, passed separately; full attendance.75% written and 70% applied, passed separately; full attendance.75% written and 70% applied, passed separately; full attendance.