A spreadsheet looks scary at first. Rows. Columns. Hundreds of boxes.
But most people use the same few formulas, again and again.
Learn these 7 and you can handle most weekly tasks. Budgets. Sales. Attendance. Stock lists.
They all work in Excel and in Google Sheets.
Three rules before you start
- Every formula starts with an equals sign: =
- A range like B2:B6 means “from cell B2 down to cell B6”.
- Press Enter to see the answer. Click the cell to see the formula again.
Our sample data: weekly sales
| Row | A (Day) | B (Sales in dollars) |
|---|---|---|
| 2 | Monday | 120 |
| 3 | Tuesday | 80 |
| 4 | Wednesday | 150 |
| 5 | Thursday | 100 |
| 6 | Friday | 200 |
1. SUM adds numbers
=SUM(B2:B6) gives 650.
Use it for totals: sales, costs, hours.
2. AVERAGE finds the usual value
=AVERAGE(B2:B6) gives 130.
One very big number can pull an average up. When that happens, try MEDIAN. It finds the middle value.
=MEDIAN(B2:B6) gives 120.
3. COUNT and COUNTA count items
=COUNT(B2:B6) gives 5. It counts cells that hold numbers.
=COUNTA(A2:A6) gives 5. It counts every cell that is not empty, including text.
4. MIN and MAX find the smallest and biggest
=MIN(B2:B6) gives 80.
=MAX(B2:B6) gives 200.
5. IF makes a decision
=IF(B2>=100,”Good day”,”Slow day”)
Monday (120) shows “Good day”. Tuesday (80) shows “Slow day”.
The pattern is always: IF(test, answer if true, answer if false).
6. SUMIF adds only what matches
Here is a small expense list:
| Row | A (Category) | B (Amount) |
|---|---|---|
| 2 | Food | 20 |
| 3 | Transport | 15 |
| 4 | Food | 30 |
| 5 | Rent | 400 |
| 6 | Transport | 10 |
=SUMIF(A2:A6,”Food”,B2:B6) gives 50.
It looks in column A for “Food” and adds the matching amounts from column B.
7. VLOOKUP finds information in a table
Here is a small price list:
| Row | A (ID) | B (Item) | C (Price) |
|---|---|---|---|
| 2 | P101 | Notebook | 3.50 |
| 3 | P102 | Pen | 1.20 |
| 4 | P103 | Folder | 2.00 |
| 5 | P104 | Marker | 2.80 |
=VLOOKUP(“P102”,A2:C5,3,FALSE) gives 1.20.
It means: find P102 in the first column, return the value from column 3, and match exactly (FALSE).
A newer option is XLOOKUP. It works in Google Sheets and in Excel 2021 and Microsoft 365.
=XLOOKUP(“P102”,A2:A5,C2:C5) gives 1.20.
Five mistakes to avoid
- Typing numbers into a formula by hand. Point to the cells instead, so the answer updates when the numbers change.
- Forgetting the equals sign at the start.
- Leaving out a closing bracket.
- Mixing text and numbers in the same column.
- Using AVERAGE when one huge number is bending the result. Check MEDIAN too.
A 15 minute practice task
Build a one week budget.
- List your income and five expenses.
- Use SUM for total spending.
- Use SUMIF to total one category, such as food.
- Use IF to show “Over” when your spending is more than your income.
Your next step
Choose one:
- Get the Starter Skills Training Manual and practice with more examples.
- Take a free practice quiz and test what you know.
- Join our community and share your first spreadsheet for feedback.
- Explore our Google Productivity Tools course. It takes 3 months.
Quick answers
Excel or Google Sheets?
The formulas here work in both. Start with the one you can use for free every day.
Do I need to memorize formulas?
No. Learn what each one does. Your spreadsheet tool will help you with the exact spelling.