Microsoft Office · Intermediate level

Intermediate level · Module 2 of 6 · 20 guided hours

Excel for Everyday Office Data

Manage the lists and records offices depend on — staff lists, stock, attendance, expenses — with functions, tables and charts.

Learning objectives

  • Use logical and conditional functions such as IF, COUNTIF and SUMIF
  • Organise data as Excel tables and sort and filter it
  • Present summaries with charts and conditional formatting

Topics

  • absolute and relative references
  • IF, COUNTIF, SUMIF and date functions
  • Excel tables, sort and filter
  • conditional formatting
  • charts for reports

Lessons

  1. Lesson 1 References and Conditional Functions

    Write formulas that copy correctly and calculate based on conditions.

    absolute referenceIFCOUNTIFSUMIF

  2. Lesson 2 Tables, Sorting and Filtering

    Turn ranges into Excel tables and find information quickly with sorting and filtering.

    Excel tablestructured referencesortfilter

  3. Lesson 3 Data Validation and Drop-down Lists

    Stop errors at the point of entry with drop-down lists, input rules and helpful messages.

    data validationdrop-down listinput messageerror alert

  4. Lesson 4 Conditional Formatting and Report Charts

    Highlight what matters and present data clearly for managers.

    conditional formattingdata barschart elementscopy chart to Word/PowerPoint

Practical activity

Build an office records workbook (attendance or stock) with tables, IF/SUMIF summaries, conditional formatting and a dashboard-style summary sheet.

Expected outcomes

  • Reliable everyday office spreadsheets
  • Quick answers to common data questions

Assessment

Practical test with a checklist; instructor changes data to confirm formulas and formats respond correctly.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.