Mastering Data Analysis with Excel Online: From Importing Datasets to Advanced Functions

Introduction

The session began with a warm welcome, a quick weather check, and an invitation to download the dataset attached to the video description. Participants were reminded to ensure they had access to Microsoft 365 (Excel Online) and to switch their file permissions from view to edit.

Setting Up Excel Online

1. Open the Excel icon on the left side of the Microsoft 365 home screen.
2. Choose Upload a file and locate the downloaded dataset (usually in the Downloads folder).
3. After the file opens, click the green Edit button to gain editing rights.

Recap of Previous Lessons

• Created an Excel standard table from raw data.
• Built a pivot table and a simple dashboard.
• Discussed the importance of data cleaning (removing duplicates, handling blanks) before analysis.

Pivot Tables & Charts

• Insert → PivotTable → New Worksheet (or Existing Worksheet for additional tables).
• Drag fields (e.g., Gender, Writing Score) into Rows, Columns, and Values to explore relationships.
• Use the pivot chart feature to visualise trends.

Functions vs. Formulas

• Function: A built‑in mathematical expression supplied by Excel (e.g., SUM, AVERAGE).
• Formula: Any expression you write, beginning with =; it may contain one or more functions.
• Example: =SUM(A2:A5) is a formula that uses the SUM function.

Core Functions Demonstrated

Practical Walk‑throughs

• Total grade per student: Use SUM across the four grade columns, then drag the fill handle to copy the formula down.
• Average grade for the class: =AVERAGE(J2:J20) where column J holds total grades.
• Identify top and bottom performers: =MAX(J2:J20) and =MIN(J2:J20).
• Count participants: =COUNT(A2:A20) gives the number of students.
• Create full names: =CONCAT(A2, " ", B2) merges first and last names.
• Extract distinct groups: =UNIQUE(C2:C20) lists each unique group once.

Data Cleaning & Preparation

The instructor emphasized that clean data is essential for trustworthy insights. Steps include: 1. Remove duplicate rows. 2. Fill or delete empty cells. 3. Convert raw ranges to Excel tables for easier referencing. 4. Validate data types (numbers vs. text).

Building a Dashboard

• Combine pivot tables, key metrics (average, max, min), and charts on a single sheet.
• Use conditional formatting (e.g., colour‑code low scores) to highlight students needing attention.
• Keep the dashboard dynamic by referencing table names instead of static cell ranges.

Motivational & Community Elements

Throughout the class the trainer repeated a mantra: "No man down", encouraging participants to help each other and share progress on social media. Frequent reminders to download the dataset, ask questions in the chat, and stay engaged were interwoven with the technical content.

Closing Remarks

The session concluded with a call to complete upcoming quizzes and projects, a reminder that certification depends on finishing the assignments, and an invitation to revisit the recorded video for any missed details.

By mastering Excel Online—importing data, cleaning it, using pivot tables, and applying core functions—you can turn raw spreadsheets into actionable insights and professional‑grade dashboards without leaving your browser.