Course Code: exdata
Duration: 14 hours
Prerequisites:

Target Audience: PDEA regional and national office staff responsible for data consolidation and reporting using Excel

Overview:

Learning Objectives

By the end of this course, participants will be able to:

  • Analyze and interpret Excel data to uncover trends and relationships across regions
  • Systematize their data handling process to improve accuracy and efficiency
  • Create dynamic, meaningful reports for internal decision-making and national consolidation
  • Understand how to identify outliers, patterns, and actionable insights within the Barangay Drug Clearing Program data
Course Outline:

Day 1: Data Preparation and Foundational Analytics in Excel

1. Introduction to Structured Data Handling

  • Common pitfalls in raw data handling
  • Best practices in regional data consolidation
  • Organizing worksheets and using data templates

2. Data Cleaning and Preparation

  • Removing duplicates, blank cells, and inconsistencies
  • Text-to-columns and Flash Fill
  • Using filters and sorting effectively
  • Using named ranges and structured references

3. Formulas for Quick Data Analysis

  • Logical functions: IF, IFS, AND, OR
  • Lookup functions: VLOOKUP, XLOOKUP, INDEX/MATCH
  • Statistical functions: AVERAGEIF(S), COUNTIF(S), MEDIAN, MODE, STDEV

4. Using Tables and Structured Data Models

  • Creating and managing Excel Tables
  • Using slicers for filtering regional data
  • Understanding the power of structured referencing in reports

Day 2: Trend Analysis, Visualization, and Reporting

5. Interpreting Trends Over Time

  • Analyzing month-to-month or quarter-to-quarter changes
  • Creating moving averages
  • Using conditional formatting to highlight trend changes
  • Identifying seasonality or regional variation

6. Understanding Relationships Between Variables

  • Creating and interpreting scatter plots
  • Correlation analysis using Excel
  • Introducing basic regression (conceptual overview with Excel implementation)

7. Visualization and Dashboard Creation

  • Best practices in data storytelling through charts
  • Dynamic charts with drop-down lists
  • Introduction to PivotTables and PivotCharts
  • Creating summary dashboards for regional vs. national comparison

8. Putting It All Together

  • Simulating a national report using sample regional data
  • Group breakout: Each group interprets data from one region and presents key findings
  • Q&A and Wrap-Up