Sign inStart practising
← All subjects
NewFree preview available
Digital Skills · Any age, any level

Excel from Beginner to Pro: Digital Skills for Learners

Learn Excel from your very first formula to PivotTables, lookups and dashboards, with tasks, worked examples and three real projects.

★ New0 reviews1 learners
Created by Prince Bopape
Modules
7
Lessons
29
Study time
14h 45m
OUTCOMES

What you’ll learn

Write formulas and functions with confidence, including absolute references
Make decisions in a sheet with IF, AND, OR and lookups such as XLOOKUP and INDEX/MATCH
Sort, filter, validate, format and clean data using Tables and conditional formatting
Summarise data with PivotTables and show it in clear, professional charts
Calculate loans and savings, and answer what-if questions with Goal Seek
Build a marks tracker, a budget planner and a small business dashboard
CURRICULUM

Workshop content

7 modules · 29 lessons · 14h 45m total

Module 1 · Getting started4 lessons
Meet Excel25 min · Preview

Meet Excel

Excel3 min readTry it live: Excel Lab

Practise this in the Excel Lab

open the exercise for this lesson. It is a real spreadsheet in your browser, with tasks that tick themselves when your sheet is right. No Excel needed.

Excel is a program for working with numbers. You type numbers into a grid, tell Excel how to calculate with them, and it does the sums instantly and correctly every time. If a number changes, every answer that depends on it changes too.

No Microsoft Excel? No problem. Everything in this course also works in Google Sheets (free, in a browser or on your phone with a Google account) and in LibreOffice Calc (free). The buttons sit in slightly different places, but the ideas and the formulas are the same. Where it matters, we tell you.

The parts you need to know

PartWhat it is
WorkbookThe whole file. Like a book.
Worksheet (sheet)One page of the workbook. The tabs at the bottom are the sheets.
CellOne box in the grid. This is where you type.
ColumnA vertical line of cells, named with letters: A, B, C…
RowA horizontal line of cells, named with numbers: 1, 2, 3…
Cell addressThe column letter and the row number together. The cell in column B, row 3 is B3.
Formula barThe long bar above the grid. It shows what is really in the selected cell.
RangeA block of cells, written with a colon. B2:B6 means B2, B3, B4, B5 and B6.

Tip

What you see in a cell and what is in the cell can be different. A cell can show 69,4 while it really holds a formula. Always click a cell and look at the formula bar.

Watch it work. Press Next to go one step at a time, or Play to let it run.

Loading the worked example…

Try it

  1. Open a new blank workbook.
  2. Click the cell A1 and type Subject, then press Tab. You move to B1.
  3. Type Test 1, press Tab, then type Test 2 and press Enter.
  4. Fill in this table, starting in A2. Press Tab to move right and Enter to finish a row.
SubjectTest 1Test 2
Maths6872
English7470
Life Sciences5948
Accounting8185
Geography6566
  1. Click on B3. What does the formula bar show? What is the cell address of the mark for Accounting, Test 2?

We will keep using this table for the next lessons, so save your work now (Ctrl + S, or File → Save) with the name My marks.

Check yourself

What is the cell address of the Geography mark in Test 1?

B6. Test 1 is column B and Geography is the fifth subject, which sits in row 6 (row 1 is the headings).

What does the range B2:B6 mean?

All five Test 1 marks: B2, B3, B4, B5 and B6.

Where do you look to see what is really inside a cell?

In the formula bar.

Practise this lesson in the Excel Lab →

Entering and formatting data25 min
Your first formulas25 min
Rows, columns, sheets and printing30 min
Module 2 · Essential functions5 lessons
Your first functions: SUM, AVERAGE, MIN, MAX and COUNT30 min · Preview

Your first functions: SUM, AVERAGE, MIN, MAX and COUNT

Excel5 min readTry it live: Excel Lab

In this lesson

  1. How to write a function
  2. The five everyday functions
  3. A summary block
  4. Common mistakes

Practise this in the Excel Lab

open the exercise for this lesson. It is a real spreadsheet in your browser, with tasks that tick themselves when your sheet is right. No Excel needed.

Adding with =B2+B3+B4+B5+B6 works, but imagine 500 marks. A function is a ready-made calculation with a name. You give it some cells, it gives you an answer.

How to write a function

Formula
=SUM(B2:B6)
  • SUM is the function name.
  • The brackets hold the arguments: the cells or numbers it works on.
  • B2:B6 is a range: B2 up to B6.

Local settings tip

On computers with a comma as the decimal mark (common in South Africa), Excel separates arguments with a semicolon: =IF(A1>50;"Pass";"Fail"). On computers with a dot as the decimal mark it uses a comma. If your formula in this course gives an error, try swapping , for ;. In this course we write commas.

The five everyday functions

Use the Test 1 marks: 68, 74, 59, 81, 65 in B2:B6.

FunctionWhat it doesFormulaAnswer
SUMAdds=SUM(B2:B6)347
AVERAGEMean (sum ÷ count)=AVERAGE(B2:B6)69,4
MAXBiggest=MAX(B2:B6)81
MINSmallest=MIN(B2:B6)59
COUNTHow many numbers=COUNT(B2:B6)5

Others you will meet: COUNTA counts cells that are not empty (numbers and text), MEDIAN gives the middle value (here 68), and LARGE(B2:B6,2) gives the 2nd biggest (here 74).

The AutoSum button

Click the cell below a column of numbers, then press Alt + = (or the AutoSum Σ button on the Home tab). Excel guesses the range. Press Enter to accept. Always glance at the range it chose: it can be wrong.

Using more than one range

=SUM(B2:B6,C2:C6) adds both columns together: 347 + 341 = 688.

A summary block

Under your marks table, build a small summary. In A9 type Average, A10 Highest, A11 Lowest, then:

CellFormulaTest 1Test 2
B9=AVERAGE(B2:B6)69,468,2
B10=MAX(B2:B6)8185
B11=MIN(B2:B6)5948

Write the formula in B, then drag the fill handle one column right to get the Test 2 column (C) for free.

Watch it work. Press Next to go one step at a time, or Play to let it run.

Loading the worked example…

Common mistakes

  • Numbers stored as text. If a number is left-aligned, SUM ignores it. Fix it by retyping or using Data → Text to Columns → Finish.
  • Blank cells. AVERAGE skips blank cells (good), but a cell with 0 is counted as a mark.
  • A range that stops too early. If you add a row at the bottom, a formula like =SUM(B2:B6) will not include it. Insert rows inside the range, or use a Table (Lesson 11).

Try it

Task 1. Add Afrikaans, 62 and 64 in row 7 (insert it inside the range so formulas update). What are the new Test 1 average and Test 2 average?

Task 2. In column F, use AVERAGE across the row (=AVERAGE(B2:C2)) to give each subject's average for the two tests, and fill it down.

Task 3. Which function tells you the middle mark for Test 1? Write the formula and the answer.

Task 4. Use COUNTA on column A to count your subjects.

Task 5 (challenge). Use LARGE and SMALL to find the second best and the second worst Test 2 mark.

Check yourself

Task 1 answer

Test 1 average (6 subjects): 409 ÷ 6 = 68,17. Test 2 average: 405 ÷ 6 = 67,5.

Task 2 answer

F2 =AVERAGE(B2:C2) = 70, English 72, Life Sciences 53,5, Accounting 83, Geography 65,5, Afrikaans 63.

Task 3 answer

=MEDIAN(B2:B7). With 6 marks (59, 62, 65, 68, 74, 81) the median is the average of the two middle marks: (65 + 68) ÷ 2 = 66,5.

Task 4 answer

=COUNTA(A2:A7) gives 6 (the heading in A1 is outside the range).

Task 5 answer

Test 2 marks: 72, 70, 48, 85, 66, 64. =LARGE(C2:C7,2) = 72; =SMALL(C2:C7,2) = 64.

Practise this lesson in the Excel Lab →

Cell references: relative, absolute and mixed30 min
Percentages, rounding and everyday maths30 min
Making decisions with IF, AND and OR30 min
Counting and adding with conditions: COUNTIF, SUMIF and AVERAGEIF30 min
Module 3 · Organise and clean your data6 lessons
Sorting and filtering30 min
Excel Tables: the professional way to store data30 min
Conditional formatting: make the important things stand out30 min
Data validation: stop mistakes before they happen30 min
Working with text30 min
Dates and times35 min
Module 4 · Looking things up2 lessons
Looking things up: VLOOKUP and HLOOKUP30 min
Better lookups: XLOOKUP, INDEX and MATCH35 min
Module 5 · Charts, PivotTables and what-if3 lessons
Charts: turn numbers into pictures35 min
PivotTables: summarise thousands of rows in seconds30 min
Money functions and what-if tools35 min
Module 6 · Pro skills4 lessons
Named ranges, checking formulas and fixing errors35 min
Dynamic arrays and modern functions30 min
Work faster: shortcuts and habits30 min
Protect, share and save your work30 min
Module 7 · Projects, quiz and next steps5 lessons
Project 1: Your personal marks tracker30 min
Project 2: A monthly budget planner30 min
Project 3: A tuckshop shop and dashboard30 min
Final quiz and challenges40 min
Where next? From pro to expert25 min
BEFORE YOU START

Requirements

  • A computer with Microsoft Excel, or any device with free Google Sheets
  • No experience needed: we start with the very first cell
  • Open to everyone: no age or grade requirement
QUESTIONS

Frequently asked questions

Do I need Microsoft Excel?

No. Everything in the course works in Excel, and almost all of it works in free Google Sheets in your browser or phone. We point out the few places where they differ.

How long does it take?

There are about 29 lessons. Working an hour a day, most learners finish in three to four weeks of the holidays, but you can go at your own speed.

Who is this course for?

Anyone. School learners, university students and adults at work all use Excel, and the course starts from the very first cell. Nothing in it depends on your age or school grade.

Will I get a certificate?

Yes. Finish the lessons, tasks and the final checklist to complete the course.

LEARNER REVIEWS

No reviews yet

KEEP LEARNING

More from Prince Bopape

Physical Sciences · Grade 12Physics Grade 12Free