Computer Basics: Office and Internet · lesson 4 of 5

A simple budget in a spreadsheet

About 35 minutes

Why this matters

Every household asks the same question at month end: where did the money go? A spreadsheet answers it in one screen and does the adding for you, so a wrong total is never your fault again. Shopkeepers use exactly the same skill for daily sales.

The idea

A spreadsheet is a program that shows a grid of boxes for numbers and text, such as Microsoft Excel or LibreOffice Calc. A cell is one box in the grid, and it is the place where you type a number, a word or a formula. Each column has a letter across the top and each row has a number down the side, so a cell address is the column letter followed by the row number, like B2.

A formula is an instruction that starts with the equals sign and tells the spreadsheet to calculate something. A function is a ready-made formula with a name, and SUM is the function that adds up a range. A range is a block of cells written as the first cell, a colon, and the last cell, so B2:B6 means cells B2 down to B6.

FormulaMeaning
=B2+B3adds the two cells
=SUM(B2:B6)adds every cell from B2 to B6
=B8-B9subtracts B9 from B8
=B2*12multiplies B2 by 12
=AVERAGE(B2:B6)finds the average

The best part of a formula is that it recalculates on its own. Change one number and every total that depends on it updates instantly. The fill handle is the small square at the bottom-right corner of a selected cell, and dragging it copies the formula down or across.

Worked examples

Example 1: Listing the monthly expenses

Task: enter a household budget for one month.

  1. Open the spreadsheet program and choose a blank sheet.
  2. Click cell A1 and type Item, press Tab, type Rupees, press Enter.
  3. In A2 type Rent and in B2 type 8000. Press Enter and go on with the next row.
  4. Fill rows 3 to 6: Groceries 6500, Electricity 1200, School fees 3000, Phone and internet 800.
  5. Press Ctrl and S, choose Documents, name it budget-september and click Save.

Result: a two-column table with five expense items and their amounts in rupees.

Example 2: Adding the total with SUM

Task: work out the total spent.

  1. Click cell A7 and type Total.
  2. Click cell B7 and type =SUM(B2:B6) exactly, then press Enter.
  3. Watch the cell show 19500.
  4. Change B3 from 6500 to 7000 and press Enter.

Result: B7 shows 19500 first and then changes to 20000 on its own when groceries go up.

Example 3: What is left from the salary

Task: compare the total with the month's income.

  1. Click A9 and type Income. Click B9 and type 25000.
  2. Click A10 and type Left over.
  3. Click B10 and type =B9-B7 and press Enter.

Result: B10 shows 5000, the money still in hand after all expenses.

Example 4: Copying a formula with the fill handle

Task: add a Yearly column that multiplies each item by twelve.

  1. Click C1 and type Yearly.
  2. Click C2 and type =B2*12 and press Enter.
  3. Click C2 again, point at the small square at its bottom-right corner, and drag down to C6.

Result: C2 to C6 each show the yearly cost of that row, for example 96000 for rent.

Common mistakes

  • Typing the formula without the equals sign, so the cell just shows the text SUM(B2:B6). Every formula must start with the equals sign.
  • Typing the total by hand instead of using SUM. A typed number never updates when an item changes.
  • Putting the rupee sign inside the cell, like Rs 8000. Then it is text and cannot be added. Type only the number and put the unit in the heading.
  • Leaving a blank row inside the range, which makes the total look wrong. Keep the items in one unbroken block.

Try it yourself

  1. Make a budget with six items of your own and a SUM total.
  2. Add an Income cell and a Left over formula that subtracts the total from the income.
  3. Change one expense and check that both the total and the left-over amount change.

Answers: the total cell should show a formula in the formula bar when you click it, not a plain number. After editing one expense, the total should move by exactly the same amount and the left-over amount by the same amount in the other direction.

Quiz

Five questions built from this lesson. Start at Core; the level adapts to your score.

Flashcards

Turn the key terms of this lesson into cards that come back just before you forget them.