Back to Course

Excel & Data Capturing: Get Hired for Admin and Data Jobs

0% Complete
0/0 Steps
  1. Chapter 1: Data Jobs and the Spreadsheet Basics You Need
    5 Topics
    |
    1 Quiz
  2. Chapter 2: Data Capturing — Fast and Accurate
    5 Topics
    |
    1 Quiz
  3. Chapter 3: Cleaning Messy Data
    5 Topics
    |
    1 Quiz
  4. Chapter 4: Counting and Adding Up With Conditions
    5 Topics
    |
    1 Quiz
  5. Chapter 5: Lookups — VLOOKUP and XLOOKUP
    5 Topics
    |
    1 Quiz
  6. Chapter 6: Pivot Tables, Charts and Reports
    5 Topics
    |
    1 Quiz
  7. Chapter 7: Protect the Data, Get Hired and Earn
    5 Topics
    |
    1 Quiz

Quizzes

Participants 1

Lesson Progress
0% Complete

In this lesson: You will refresh the basics every Excel user needs.

Cells, rows and columns

  • Columns have letters (A, B, C…), rows have numbers (1, 2, 3…)

  • A cell is where a column and row meet, for example B3

  • A range is a group of cells, for example B2:B10 (B2 to B10)

Formulas start with =

EXAMPLE

=B2+B3 adds two cells

=SUM(B2:B10) adds a range

=AVERAGE(B2:B10) gives the average

=MIN(B2:B10) and =MAX(B2:B10) give the smallest and largest values

=COUNT(B2:B10) counts cells with numbers

Relative and absolute references

When you copy a formula down, the cell references change: =B2+C2 becomes =B3+C3. That’s a relative reference — usually what you want.

Sometimes one cell must stay fixed, like a VAT rate in cell F1. Add dollar signs to lock it:

EXAMPLE

Price in B2, VAT rate (15%) in F1

VAT amount: =B2 × $F$1 typed as =B2*$F$1

When you copy it down, B2 becomes B3, B4… but $F$1 stays the same

Tip: click on the reference in the formula bar and press F4 in Excel to add the dollar signs (in Google Sheets, F4 also works on most computers).

Fill down quickly

Type the formula once, then double-click the small square at the bottom-right corner of the cell (the fill handle) to copy it down the whole column.

Key takeaways

  • Formulas start with =; SUM, AVERAGE, MIN, MAX and COUNT cover the basics.

  • Relative references change when copied; absolute references ($F$1) stay fixed.

  • Use the fill handle to copy a formula down a whole column.

Enjoyed Chapter 1?

Unlock every chapter of Excel & Data Capturing: Get Hired for Admin and Data Jobs and download your certificate. R199 once-off, no monthly fees.

Get the full course – R199

Responses