Test Data for Excel

1. Select the table on this web page (click and drag over the table) and press Ctrl+C (or right‑click > Copy).

2. Open Excel and click cell A1 in a new worksheet.

3. Press Ctrl+V to paste. If formatting looks wrong, use the Paste dropdown and choose Paste → Keep Text Only or Paste Special → Text.

4. Immediately check the pasted result for extra header rows, merged cells, or stray line breaks.

Excel basics — structure & formatting ( 10 Minutes)

Goal: Make the dataset readable and prepare for analysis.

Steps:

1. Ensure the first row contains clear column headers. Rename if needed. Example headers: ID, Name, Age, Gender, Community, Issue Reported, Sessions Attended, Outcome, Case Opened, Case Worker.

2. Select the entire data range and set proper column widths: double‑click the column boundary to AutoFit.

3. Basic formatting: Home → Font to bold the header row; apply borders to the data area (Home → Borders → All Borders).

4. Convert age and sessions to numeric if they are text: select column → Data → Text to Columns → Finish, or change cell format to Number.

5. Apply cell formatting for dates: select Case Opened column → Right‑click → Format Cells → Date → choose format DD‑MMM‑YYYY or DD/MM/YYYY.

Live exercise: Students to highlight beneficiaries from Slum A using Filter (Data → Filter) and to change the fill color for the filtered rows

Convert range into a Table and why it helps (10 minutes)

Steps to convert:

1. Click inside your data range.

2. Insert → Table (or press Ctrl+T).

3. Ensure the checkbox My table has headers is checked. Click OK.

Benefits to emphasise:

• Automatic filter dropdowns appear on each header.

• Table expands automatically when you add new rows; references update.

• Easier formulas using Structured References (e.g., =SUM(Table1[Sessions Attended])).

• Styles & Total Row: Table Design → Table Styles / Total Row (turn on to get automatic SUM, AVERAGE, etc.).

Exercise: Turn on the Total Row and set the Sessions Attended column to show Average.

Ranges & basic functions (5 minutes)

What is a range?

• A range is a set of cells (e.g., E2:E6). Tables have structured column names (Table1[Age]).

Common functions to practice

=SUM([@[Sessions Attended]])   // inside a table row — returns the sessions for that row (structured example)

=SUM(E2:E6)                     // sum of Sessions Attended if they are in E2:E6

=AVERAGE(C2:C6)                 // average age

=MAX(C2:C6)                     // maximum age

=MIN(C2:C6)                     // minimum age

=COUNT(A2:A6)                   // count nonblank IDs

=COUNTA(B2:B6)                  // count nonblank Names

=COUNTIF(D2:D6,"F")           // count females

=COUNTIFS(F2:F6,">=5", G2:G6,"Re‑enrolled")  // sessions >=5 and outcome Re-enrolled

Charts & visualisation (10 minutes)

Create these charts during class (step by step):

A. Gender distribution — Pie Chart

1. Create a small summary table: Male | Female counts (use COUNTIF).

2. Select the two cells (labels + counts) → Insert → Pie → 2‑D Pie.

3. Add Data Labels (Chart Elements → Data Labels) and a chart title like Gender distribution.

B. Sessions Attended per participant — Bar/Column Chart

1. Select Name and Sessions Attended columns.

2. Insert → Insert Column or Bar Chart → Clustered Column.

3. Add axis titles (Chart Elements → Axis Titles) and rotate X‑axis labels if they overlap.

C. Trend of case openings over time — Line Chart

1. If you have Case Opened dates, create a simple pivot or summary by month (or use the raw data sorted by date).

2. Select Date and a count of IDs by date → Insert → Line Chart.

PivotTables — create meaningful summaries (15 minutes)

PivotTables are essential for fast summarization.

Create a PivotTable (step by step):

1. Click anywhere in the Table you created.

2. Insert → PivotTable → choose New Worksheet → OK.

3. In the PivotTable Fields pane, drag fields to the areas:

   • Rows: Drag Community or Issue Reported.

   • Columns: (Optional) Drag Outcome.

   • Values: Drag ID (default will be Count of ID) or Sessions Attended.

   • Filters: Drag Case Worker if you want to filter results by worker.

Example pivot tasks to demo:

• Count of beneficiaries by community: Rows = Community; Values = Count of ID.

• Average sessions by issue: Rows = Issue Reported; Values = Average of Sessions Attended (click value field settings → Average).

• Outcomes by community: Rows = Community; Columns = Outcome; Values = Count of ID.

Grouping ages into ranges in a Pivot:

1. Drag Age to Rows.

2. Right‑click any Age in the Pivot → Group → set Start at 10, End at 40, By = 10 (creates 10–19, 20–29 groups).

✅ Dos in Excel

• Keep data clean and organized – each column should have one clear heading.
  
  

• Use consistent formats – e.g., same date format (DD/MM/YYYY) or consistent spelling (“Male/Female,” not “M/F”).
  
  

• Save work regularly – keep a backup copy to avoid losing data.
  
  

• Use filters and sorting instead of manually scanning data.
  
  

• Name your sheets meaningfully (e.g., “Beneficiaries Data” instead of “Sheet1”).
  
  

• Apply formulas smartly – like SUM, AVERAGE, COUNT, instead of manual calculations.
  
  

• Use tables or pivot tables when datasets grow larger.
  
  

• Highlight important data using conditional formatting (e.g., ages below 18).

❌ Don’ts in Excel

• Don’t merge cells unnecessarily – it makes sorting and pivot tables difficult.
  
  

• Don’t leave blank rows or columns – it breaks the structure of your dataset.
  
  

• Don’t type different kinds of data in one column (e.g., mixing names and ages in the same column).
  
  

• Don’t hard-code results – use formulas so changes update automatically.
  
  

• Don’t use inconsistent spelling/abbreviations – it creates errors in analysis.
  
  

• Don’t overload one sheet with too much data – split into logical sheets if needed.
  
  

• Don’t forget data privacy – avoid sharing sensitive information without anonymizing.
  
  

• Don’t rely only on visuals – always keep the raw data for verification.