Productivity

Spreadsheets for everyday work: formulas, fixed references, IF, SUMIF and VLOOKUP

Formulas you can trust: cells and ranges, SUM and AVERAGE, copying formulas with fixed references, shares of a total, and IF, COUNTIF, SUMIF and VLOOKUP on small made-up tables.

3 lessons, 18 steps

Get it on Google Play

Formulas you can trust

A spreadsheet is only as good as its formulas. A total typed in by hand goes stale the moment a number changes, a formula copied one row down can quietly point at the wrong cell, and a lookup can return the wrong client without any warning. Spreadsheets for Everyday Work teaches the formulas behind household budgets, sales sheets and invoice lists, so you can trust what the cells show.

The course has three lessons. The first covers cell addresses and ranges, formulas that start with an equals sign, SUM and AVERAGE, and why =B4+B2*B3 does the multiplication first. The second is about copying formulas: how plain references move with the copy, how dollar signs keep a reference on one cell or fix only its row or column, and how one copied formula gives every item's share of a total. The third covers IF, COUNTIF, SUMIF and VLOOKUP with an exact match, and picking the right function for a question about a list.

Each lesson opens with a short explanation and then gives you six steps. Most of them show a small made-up table and ask what number a formula shows; others ask you to pick the right formula or function, or to explain a mistake in two or three sentences. The steps do not need a spreadsheet app: you work each result out yourself. When your answer is off, the AI tutor first asks a question about it, then gives a hint, then a partial step. Show solution is always available, and a four-question quiz before and after each lesson shows what changed.

This is one of the editorial courses in Ai Teacher. The problems and worked solutions were written in advance, and facts were checked against the public sources listed below, including the LibreOffice Calc Guide.

What you'll learn

  • Write formulas with cell references, SUM and AVERAGE and predict their results
  • Copy a formula correctly by fixing the references that must not move
  • Count, add and look up values in a list with IF, COUNTIF, SUMIF and VLOOKUP

Lessons

  1. Cells, ranges and formulas

    6 steps, 4 min intro

  2. Copying formulas and fixed references

    6 steps, 4 min intro

  3. Decisions and lookups

    6 steps, 4 min intro

A copied formula that goes wrong

Tutor: A sales sheet keeps a commission rate of 0.05 in B1. B2, B3 and B4 hold the sales 200, 400 and 300. C2 holds =B2*B1 and shows 10. C2 is copied to C3 with no dollar signs anywhere. What number does C3 show?

You: 20

Tutor: Without dollar signs, does the reference B1 stay where it is when the formula moves down a row?

You: No, it moves down to B2 as well. So C3 holds =B3*B2, and 400 × 200 = 80,000.

Tutor: Right, 80,000. The copy multiplies two sales instead of a sale by the rate. Writing the rate as $B$1 keeps every copy on B1.

Based on a real step from lesson 2. The tutor's wording varies, and AI can be wrong: the tutor answers from the course material and labels a reply it could not check.

Who this course is for

  • You keep a household budget, a shared expense list or a small sales sheet and want formulas you can trust.
  • You copy a formula down a column and sometimes get nonsense in the rows below.
  • You hear about SUMIF and VLOOKUP at work but are not sure how they decide what to return.
  • You still add up totals on a calculator and type them into the sheet.

Questions about the spreadsheet course

Does the course teach Excel or Google Sheets?

Not one app in particular. The lessons have no menus or screenshots: they teach formulas. SUM, AVERAGE, IF, COUNTIF, SUMIF and VLOOKUP have the same names in Excel, Google Sheets and LibreOffice Calc, and dollar signs fix a reference in all three. Depending on your language settings, an app may separate the parts of a formula with semicolons instead of commas.

How much math do I need?

Adding, multiplying and dividing small numbers, and reading a result as a percentage. If percentages or the order of operations feel rusty, Practical Math Refresher covers them.

Is the course free?

One lesson in Ai Teacher is free. After that, a subscription opens all lessons. You can pay monthly or yearly in Google Play, and usage limits apply.

Sources

Facts and definitions were checked against these public sources. Lesson text and examples were written independently; no text was adapted.

Start Spreadsheets for Everyday Work in Ai Teacher

One lesson in the app is free. Ai Teacher is available for Android on Google Play.

Get it on Google Play