Spreadsheets (Microsoft Excel or similar)

TL;DR

Spreadsheets like Excel help you organise, calculate, and analyse data using cells, rows, and columns. You use formulas and functions to perform calculations automatically, saving time and reducing errors. Mastering basic features lets you create powerful tools for managing information efficiently.

1. The Mental Model

Think of a spreadsheet as a giant grid, like a digital ledger. Each box (cell) can hold different types of information, and you can tell these boxes to perform calculations based on what's in other boxes. It's like having a super-smart calculator and organiser rolled into one.

2. The Core Material

Spreadsheets are powerful tools for managing and analysing data. They're made up of worksheets, which are grids of rows (numbered horizontally) and columns (lettered vertically). The intersection of a row and a column is called a cell, identified by its column letter and row number (e.g., A1, B5).

You can enter different types of data into cells:
* Text: Words, names, descriptions.
* Numbers: Values you want to calculate with.
* Dates/Times: Specific date and time entries.
* Formulas: Instructions that perform calculations.

Formulas and Functions

Photo by Shubham Dhage on Pexels

The real power of a spreadsheet comes from formulas. All formulas start with an equals sign (=).
* Basic Formulas: You can use arithmetic operators like + (add), - (subtract), * (multiply), / (divide), and ^ (exponent).
* Example: =A1+B1 (adds the values in cells A1 and B1)
* Example: =C3*D3 (multiplies the values in cells C3 and D3)
* Functions: These are pre-defined formulas that perform specific calculations. They save you time by handling complex operations with a single keyword.
* SUM(range): Adds all numbers in a specified range.
* Example: =SUM(A1:A10) (adds numbers from A1 to A10)
* AVERAGE(range): Calculates the average of numbers in a range.
* Example: =AVERAGE(B2:B5)
* COUNT(range): Counts how many numbers are in a range.
* Example: =COUNT(C1:C20)
* MAX(range): Finds the largest number in a range.
* Example: =MAX(D1:D10)
* MIN(range): Finds the smallest number in a range.
* Example: =MIN(E1:E10)
* IF(logical_test, value_if_true, value_if_false): Performs a conditional check.
* Example: =IF(A1>10, "Pass", "Fail") (If A1 is greater than 10, show "Pass", otherwise show "Fail")

Cell References

Photo by Torsten Dettlaff on Pexels

When you write a formula, you can refer to cells in different ways:
* Relative References (default): When you copy a formula, the cell references change relative to their new position.
* If cell C1 contains =A1+B1 and you copy it to C2, it becomes =A2+B2.
* Absolute References: Use a dollar sign $ before the column letter and/or row number to keep a reference fixed when copied.
* $A$1: Both column and row are fixed.
* A$1: Only the row is fixed.
* $A1: Only the column is fixed.
* Example: If you want to multiply a range of numbers by a fixed tax rate in cell D1, you'd use =A1*$D$1.

Data Organisation and Formatting

Photo by Jakub Zerdzicki on Pexels

You can format cells to make your data more readable:
* Number Formats: Currency, percentage, date, etc.
* Text Formats: Bold, italics, font size, colours.
* Conditional Formatting: Automatically format cells based on their values (e.g., highlight all numbers above 100 in red).
* Sorting and Filtering: Organise data alphabetically, numerically, or hide data that doesn't meet specific criteria.

Here's a diagram showing the basic process of using a spreadsheet for a simple calculation:

graph TD
    A["Open Spreadsheet Software"] --> B["Create New Workbook/Sheet"]
    B --> C1["Enter Data (Numbers, Text)"]
    B --> C2["Enter Formulas (e.g., =SUM, =AVERAGE)"]
    C1 --> D["View/Calculate Results"]
    C2 --> D
    D --> E["Format Data/Cells"]
    E --> F["Save Workbook"]

3. Worked Example

Let's calculate the total cost of some items, including VAT (Value Added Tax) at 15%.

Steps:
1. Enter Data: Type the data above into cells A1:C4.
2. Calculate Line Item Cost (Column D): In cell D2, enter the formula =B2*C2. This multiplies Quantity by Unit Price for Apples.
3. Copy Formula: Drag the fill handle (the small square at the bottom-right of cell D2) down to D4 to apply the formula to Milk and Bread.
* D3 will automatically become =B3*C3.
* D4 will automatically become =B4*C4.
4. Calculate Subtotal: In cell D5, enter =SUM(D2:D4) to add up the individual item costs.
5. Calculate VAT: In cell D6, enter =D5*0.15. This calculates 15% of the Subtotal.
6. Calculate Total: In cell D7, enter =D5+D6 to add the Subtotal and VAT.

Your spreadsheet would now look something like this:

4. Key Takeaways

• Formulas always begin with an equals sign (=).
• Cell references like A1 allow formulas to use values from other cells.
• Functions like SUM, AVERAGE, and IF simplify complex calculations.
• Relative references adjust when copied, while absolute references ($A$1) stay fixed.

• Formatting data (currency, percentages) makes spreadsheets easier to read and understand.

• Common Mistakes to Avoid:

  • Forgetting the = sign at the start of a formula.
  • Using incorrect cell references in formulas.
  • Not using absolute references ($) when a cell reference needs to stay fixed.
  • Entering data manually that could be calculated with a formula.

5. Now Try It

Create a simple budget spreadsheet.
1. Set up: Create a new sheet. In column A, list 5-7 monthly expenses (e.g., Rent, Groceries, Transport, Entertainment, Internet).
2. Enter Data: In column B, enter a realistic amount for each expense.
3. Calculate Total: In a cell below your last expense amount, use the SUM function to calculate your total monthly expenses.
4. Add Income: In a separate cell, enter your monthly income.
5. Calculate Savings/Deficit: In another cell, calculate your income minus your total expenses.
6. Format: Format your amounts as currency (e.g., R 123.45).

Success looks like: A clear budget showing individual expenses, a total expense, your income, and whether you have a surplus (savings) or a deficit (spending more than you earn).