Keith’s Training Hub

LESSON 10 OF 16 · ABOUT 10 MINUTES

Link cells instead of retyping

Use a cell reference and see how a result responds when its input changes.

Need the practice files?

Keep using your working copy if you have started the programme.

Practice workbook (.xlsx)

Learn the skill

A formula refers to a source value. Retyping a number creates a separate value that can become outdated.

Worked example

Summary!B5 is the fictional base amount 200.

In a spare cell D5, enter =B5. It displays 200.

Change B5 to 210: D5 follows. Restore B5 to 200.

Follow these steps

  1. Work on Summary. Check B5 is 200.
  2. Select D5 and enter =B5.
  3. Change B5 to 210 and check D5. Restore 200.
  4. Copy D5 down one row and inspect the formula: it becomes =B6.
  5. Try =$B$5 in another spare cell to see a reference that stays fixed when copied.

Try it yourself

Explain the difference between a typed 200, =B5 and =$B$5.

Check your practical result
  • The link responds to a source change.
  • You know which references shift when copied.
  • The base amount is restored to 200.

This is your own review, not an automatic inspection of Excel.

If it does not work

If the formula displays as text, check the cell is not Text format and has no leading apostrophe. Re-enter it after correcting the format.

Optional knowledge check

This check is for practice. It is not saved or required to continue.

What keeps B5 fixed when a formula is copied?

Continue whenever you are ready. No test or recorded score is required.

Course guide and Microsoft helpDownload the whole course guide (.txt)

Steps primarily use Microsoft 365 desktop Excel for Windows. Other versions can differ. Formulas use English names and commas; your region may use semicolons. Work only on the fictional practice copies.

Microsoft: Relative and absolute references (opens in a new tab). Guidance checked 3 November 2026.

Browser knowledge checks do not execute Excel. This programme does not change your saved progress in other courses.

All 16 lessons and the day programme

Welcome and setup: 09:30–09:40. Evaluation and close: 16:15–16:30.

The four session windows match the supplied programme. Lesson times below are approximate independent practice times. Facilitators can use the remaining time for demonstrations, questions and breaks, including lunch within Session 2.

SESSION 1 · 09:40–11:40

More functions

Count, total, classify and work with dates.

  1. Count matching orders with COUNTIF · 15 min
  2. Add matching costs with SUMIF · 15 min
  3. Use IF to label a result · 15 min
  4. Use TODAY without losing a reporting date · 15 min

SESSION 2 · 11:40–14:30

Managing datasets

Keep records together, find what matters and control new entries.

  1. Sort whole records safely · 15 min
  2. Sort by team, then amount · 15 min
  3. Put priorities in a custom order · 15 min
  4. Filter a list and restore every row · 15 min
  5. Make a safe dropdown with data validation · 20 min

SESSION 3 · 14:30–15:30

Linking

Reuse values across cells, sheets and files with clear checks.

  1. Link cells instead of retyping · 10 min
  2. Link sheets inside one workbook · 10 min
  3. Link a value from another file · 15 min
  4. Inspect, repair and safely remove a link · 15 min

SESSION 4 · 15:30–16:15

Charts

Choose a chart, label it and improve its design.

  1. Choose a chart for the question · 15 min
  2. Label the chart so someone else can read it · 15 min
  3. Improve the design without changing the message · 15 min

Final practical task and evaluation →