How to Use Spreadsheet Formulas for Everyday Office Work
Almost every office job ends up in a spreadsheet, and in almost every one of those files the same handful of functions does the useful work — SUM, AVERAGE, IF, COUNTIF, SUMIF, VLOOKUP and IFERROR. They are not difficult, just easy to get slightly wrong, and a total that is slightly wrong is worse than no total at all.
This is a hands-on lesson, not a comparison of software. It explains how a formula reads your data, where each function fails in real office files, and then builds one monthly expense tracker using all seven functions. Everything behaves the same way in Excel, Google Sheets, LibreOffice Calc and WPS Office.
Disclosure: this article contains affiliate links. If you buy a service through them, Tân IT365 may earn a small commission. This does not raise the price you pay and does not affect the guidance below.
1. How formulas work: the = sign, cell addresses and ranges
A formula starts with an equals sign. Type =2+3 into a cell and the cell shows 5 while the formula bar still shows =2+3: the cell shows a result, the formula bar shows the truth. When a number looks strange, read the formula bar before you touch anything.
A cell address is a column letter plus a row number: A1, B7, AA3. A range is two addresses joined by a colon, so B2:B10 means B2 down to B10 — that is why formulas stay short, because you write the rule once and it applies to every cell inside. You can use arithmetic, comparisons (<, >, <>) and text joined with &, as in =A2&" - "&B2.
The part that trips people up is what happens when you copy a formula down. By default every address is relative: copy =C2*D2 from row 2 into row 3 and it quietly becomes =C3*D3. That is usually what you want. But when a formula must keep pointing at one fixed cell — a tax rate, a target — the shift breaks it, and the result looks plausible instead of obviously wrong.
A dollar sign locks a coordinate. $D$1 is frozen on both axes.
| Reference | What stays fixed when you copy | Typical use |
|---|---|---|
A1 | Nothing | Row-by-row maths, such as quantity times unit price |
$A$1 | Column and row | A VAT rate or one target figure in one cell |
$A1 | Column only | Filling a formula to the right while always reading column A |
A$1 | Row only | Filling down while always reading a label from row 1 |
You do not have to type the dollar signs: select the address in the formula bar and press F4 to cycle through the four forms. And if a formula shows in the cell exactly as typed instead of calculating, the cell is formatted as text: set it to General and re-enter with F2 then Enter.
2. SUM and AVERAGE that actually add up
SUM adds the numbers in a range. AVERAGE adds them and divides by how many numbers it actually found. The second half of that sentence is where the mistakes live.
- SUM ignores empty cells and text, so
=SUM(B2:B10)never fails just because one row is blank. It quietly skips it. - AVERAGE also ignores blanks and text, and it counts only numeric cells. Ten rows with three blanks give you the average of seven values, not ten.
If you meant "the average across all ten rows, treating empty rows as zero", write =SUM(B2:B11)/10 instead. Check what the function really saw with =COUNT(B2:B10), which counts numbers only: if COUNT is smaller than the number of rows you expect to hold numbers, some of your numbers are not numbers.
Numbers stored as text is the most common cause of a total that is short: a value pasted from a report with a space inside it, or 1,200 typed into a text-formatted column, looks like a number but behaves like a word. Real numbers align to the right edge of the cell, text to the left. Fix a column with =VALUE(TRIM(B2)), or run Find and Replace to remove stray spaces.
Hidden rows and filters are the second trap. SUM and AVERAGE include hidden rows and ignore filters, so a report filtered to one month still shows a total for the whole year. Use =SUBTOTAL(9,B2:B200) or =SUBTOTAL(1,B2:B200), which follow the filter; =SUBTOTAL(109,B2:B200) also skips manually hidden rows.
Wrong ranges are the third. Starting on the header row is harmless, because text is ignored. Ending a row short loses money. Including a subtotal row from the block above counts the same money twice. And a SUM inside its own range is a circular reference, which your spreadsheet refuses to calculate.
| Symptom | Usual cause | What to do |
|---|---|---|
| Total is slightly too low | A few numbers are stored as text | Compare COUNT with the row count, then convert those cells |
| Total does not change when you filter | SUM ignores filters | Switch to SUBTOTAL(9,range) |
| Total is roughly double | The range includes a subtotal row | Sum detail rows only, first to last |
One habit makes this visible in two seconds: select a range and read the status bar, which shows Sum, Average and Count using the same rules as the functions.
3. IF and nested IF for status columns
IF asks one question and gives you one of two answers:
=IF(condition, value if true, value if false). Three arguments, two commas, no exceptions. In
an office file it is usually a status column — pass or fail, paid or unpaid, on time or late.
- Text answers go inside double quotes; numbers stay bare:
=IF(C2>=5,"Pass","Fail"). - Comparisons are the usual ones:
=,<>,>,<,>=,<=. - Combine conditions with AND and OR:
=IF(AND(C2>=5,D2="Paid"),"Ready","Hold"). AND needs every condition true, OR needs only one.
Nesting gives you more than two possible answers: put a second IF where an answer would normally go, as in =IF(C2>=8,"Good",IF(C2>=6.5,"Fair",IF(C2>=5,"Pass","Fail"))). Read it from the left: the first true condition wins and the rest are never tested, so the order matters. Writing the milder condition first swallows every good result before the strict one is checked. Keep nesting to three or four levels; past that nobody can maintain the formula, which is where IFS helps.
Deadline flags are the other everyday use. For a due date in column D: =IF(D2<TODAY(),"Overdue",IF(D2-TODAY()<=3,"Due soon","On track")). This only works if D2 holds a real date, because a date pasted in as text compares like a word and gives nonsense. Check by widening the column, since a real date aligns to the right, and fix text dates with Data, Text to Columns.
| Goal | Formula |
|---|---|
| Pass or fail at 5 points | =IF(C2>=5,"Pass","Fail") |
| Bonus only when target and payment condition are both met | =IF(AND(C2>=100,D2="Yes"),C2*0.05,0) |
| Leave the cell blank instead of writing a zero | =IF(C2="","",C2*D2) |
| Overdue flag from a due date | =IF(D2<TODAY(),"Overdue","On time") |
| Three-level status without nesting | =IFS(C2>=8,"Good",C2>=6.5,"Fair",C2>=5,"Pass") |
The mistakes are boring and repeatable: forgetting the quotes around text, so the formula returns #NAME?; typing "5" in quotes; leaving a stray space in "Paid " so nothing matches; and using < where you meant <=. Check the boundary values first — that is where errors always are.
4. COUNTIF and SUMIF: counting and totalling by category
These two are the workhorses of every category report. COUNTIF counts how many rows match something, and SUMIF adds up those rows: =COUNTIF(D2:D200,"Hanoi") counts, while =SUMIF(D2:D200,"Hanoi",E2:E200) totals column E for them.
Read the argument order carefully: criteria range first, criterion second, sum range third. Both ranges must be the same size and shape, and they are matched by position rather than by row number — so =SUMIF(D2:D200,"Hanoi",E5:E203) still calculates something, which is why this error survives unnoticed for months.
A criterion can be text, a number or a comparison. Comparisons go inside quotes, and when the value lives in another cell you join them with an ampersand: =COUNTIF(E2:E200,">"&F2). Note the two styles — in COUNTIF the operator is part of the text (">1000"), while in IF it is part of the formula (C2>1000). Mixing them up returns a count of zero, which looks like "no matching rows" rather than an error.
Wildcards help when the data is written by humans: * stands for any number
of characters and ? for exactly one, so "May*" matches May, May invoice and May
rent, while "*invoice*" matches the word anywhere in the cell. For more than one condition
use COUNTIFS and SUMIFS — and note that they are not symmetrical: SUMIF takes the criteria range first,
SUMIFS takes the sum range first.
| What you want | Formula |
|---|---|
| Number of invoices from one city | =COUNTIF(D2:D200,"Hanoi") |
| Total value from one city | =SUMIF(D2:D200,"Hanoi",E2:E200) |
| Payments above a threshold | =COUNTIF(E2:E200,">10000000") |
| Total for one city, only rows marked Paid | =SUMIFS(E2:E200,D2:D200,"Hanoi",F2:F200,"Paid") |
| Rows that are not blank | =COUNTIF(D2:D200,"<>") |
Two practical notes. Criteria match the whole cell, so "Hanoi" will not count Hanoi branch — that needs "Hanoi*". And sanity-check the counts: with 40 rows in the category column, all your COUNTIF results plus =COUNTBLANK(D2:D200) should add up to 40. If they add up to 37, three rows carry a spelling difference to fix before you send the report.
5. VLOOKUP: looking up across two tables
VLOOKUP fetches a value from another table using a key. One rule matters: the key you search for must be in the first column of the table you search in. The shape is =VLOOKUP(lookup value, table range, column number, FALSE).
An example: a sheet named Staff has codes in column A and salaries in column D, rows 2 to 100. Typing a code into A2 on another sheet, =VLOOKUP(A2,Staff!$A$2:$D$100,4,FALSE) returns the matching salary.
- A2 is what you are looking for: a code, an invoice number, an employee ID.
- Staff!$A$2:$D$100 is the whole block. The dollar signs keep it fixed when you copy the formula down, which prevents the classic "it worked for row 1 and broke after that".
- 4 is the column you want, counted inside the block: D is the fourth column of A to D. Counting from the sheet edge instead is the second classic mistake.
- FALSE means exact match, which you want almost always. TRUE is approximate, needs a sorted first column, and will happily return a wrong but plausible neighbour.
The left-column rule is what people hit most. If the key sits in column D and the value you want is in column A, VLOOKUP cannot do it — it only looks to the right. Move the key to the front of the block, or use INDEX and MATCH (=INDEX(A2:A100,MATCH(A2,D2:D100,0))), which can look any direction.
When VLOOKUP shows #N/A — "not found" — the key really is different in the two tables:
| Cause | How to confirm | Fix |
|---|---|---|
| Trailing or double spaces in the key | =LEN(A2) gives a different length than the same code in the other table | Wrap both sides in TRIM, or clean the source column |
| A number stored as text, or the reverse | The cell aligns left although it looks like a number | Make both sides the same type with VALUE or TEXT |
| Approximate match used by accident | The fourth argument is TRUE or missing | Always write FALSE |
Two habits prevent most VLOOKUP pain: type the fourth argument every time, because a missing argument defaults to TRUE, the most dangerous default in any spreadsheet; and keep the lookup table on its own sheet, because merged cells and stray blank rows turn a working file into a mystery six months later.
6. IFERROR (and IFNA) for clean reports that show no error codes
A report you send to a manager should not contain #N/A, #DIV/0! or
#VALUE!. IFERROR wraps a formula and replaces the error with something you choose:
=IFERROR(original formula, what to show instead).
=IFERROR(VLOOKUP(A2,Staff!$A$2:$D$100,4,FALSE),"")— leave the cell empty when the code is not in the staff table.=IFERROR(E2/F2,0)— return zero instead of#DIV/0!when the denominator is still blank, which is normal on the first days of a month.=IFNA(VLOOKUP(A2,Staff!$A$2:$D$100,4,FALSE),"Code not found")— the same idea, but it only handles "not found".
That last point matters. IFERROR hides every error, including the ones you need to see. If a reference points at the wrong sheet, or somebody deletes a column the formula used, plain IFERROR turns the problem into a blank cell or a zero. IFNA is safer for lookups because structural errors such as #REF! still appear. IFNA exists in Excel 2013 and later, current Google Sheets and recent LibreOffice; if the file must open in something older, IFERROR is portable.
| Error code | What it means | Best first step |
|---|---|---|
#N/A | A lookup value was not found | Compare the key in both tables: spaces, text versus number, hidden characters |
#DIV/0! | Dividing by zero or by an empty cell | Decide whether a blank result is acceptable, then use IF or IFERROR |
#VALUE! | Text where a number was expected | Find the text cell and convert or clean it |
#REF! | The formula points at a column or sheet that no longer exists | Repair the reference — do not hide it |
Pick the replacement value deliberately too. Empty text "" looks clean and prints clean, which is normally right for a report. A zero is easier to sum but drags your totals down. A short word such as "Check" is the most honest option in a working file, because a human will notice it.
7. A worked example: a monthly expense tracker built from all seven functions
Put the seven functions together in one small file. Row 1 is the header row:
| A | B | C | D | E |
|---|---|---|---|---|
| Date | Category | Note | Amount | Method |
| 02/09/2026 | Travel | Taxi to client | 185,000 | Cash |
| 03/09/2026 | Office | Printer paper | 240,000 | Transfer |
Enter data from row 2 down to row 201 — a fixed range with room for the rest of the year. Then build a summary block in columns H to K, one row per category, with the budget beside it. That block is where all seven functions appear:
| Cell | What it does | Formula |
|---|---|---|
| D202 | SUM — total spent this month | =SUM(D2:D201) |
| D203 | AVERAGE — typical expense size | =IFERROR(AVERAGE(D2:D201),0) |
| D204 | COUNTIF — rows paid in cash | =COUNTIF(E2:E201,"Cash") |
| I2 | SUMIF — total for the category named in H2 | =SUMIF($B$2:$B$201,$H2,$D$2:$D$201) |
| J2 | IF — over budget, close, or fine | =IF(I2>K2,"Over budget",IF(K2-I2<=200000,"Close","OK")) |
| L2 | VLOOKUP — budget for that category | =IFERROR(VLOOKUP($H2,Budget!$A$2:$B$20,2,FALSE),0) |
Build it in this order and every step is testable on its own:
- Check the data. Enter ten rows, then confirm with
=COUNT(D2:D201)that ten numbers were counted; if it says eight, two amounts are text and the totals will be short. - Add the two totals. SUM for the month and AVERAGE wrapped in IFERROR, so the sheet still looks tidy on a day with nothing entered.
- Add the category block. List your categories in H2:H8, spelled exactly as in column B, then paste
=SUMIF($B$2:$B$201,$H2,$D$2:$D$201)into I2 and copy it down. A category total of zero means the spelling does not match the data. - Add the budget lookup and the status column. Keep a Budget sheet with categories in A and limits in B, bring the limit across with VLOOKUP, and let the nested IF write over budget, close or OK.
- Reconcile. The category totals must add up to the month total. Any row where
=COUNTIF($H$2:$H$8,B2)returns 0 is a typo or an empty category.
Two finishing touches: format column D with thousand separators, so a text amount stands out because it ignores the format, and keep the dollar signs in the summary formulas so copying them sideways does not move the ranges. Saved once, this file runs all year; the companion article in this series compares purpose-built expense tools if you would rather not maintain it by hand.
8. Common mistakes that break a working sheet
Every one of these faults is easy to spot once you know where to look, and each has a fix you can apply in the same minute:
- Numbers stored as text. A total that comes out too low is usually skipping values that were typed as text. Compare
=COUNT(range)with the number of rows you entered; if the count is smaller, some cells are text. Real numbers align right, text aligns left. - A range that stops one row short. A new entry never appears in the total because the sum range ends above it. Check the last row of the range against the last row of data before you send the file.
- Values hard-coded inside a formula. A limit typed straight into the formula cannot be found or updated later. Keep it in its own cell and point the formula at that cell.
- Merged cells over data. Sorting and filtering see only the first cell of a merged block, so rows disappear from the result. Keep the header row unmerged.
- Relative references copied sideways. A copied formula drifts by one column and starts reading the wrong data. Lock the part that must not move with dollar signs.
- Deleting rows that other sheets point at. A lookup then returns the not-found error. Check what references the row before you remove it, and clear the contents instead.
- Filters left on when the file is shared. The reader sees a subset and assumes rows are missing. Clear the filters and freeze the header row before sending.
Frequently Asked Questions
What is the difference between SUM and AVERAGE in a spreadsheet?
SUM adds every number in a range and ignores text and empty cells. AVERAGE adds the same numbers but divides by the number of numeric cells it found, so blank rows are left out of the count. If you want blanks counted as zero, use SUM divided by the full number of rows.
Why does my SUM total come out too low?
The usual cause is numbers stored as text, which SUM skips silently. Compare COUNT of the range with the number of rows: if COUNT is smaller, some cells are text. Real numbers align right and text aligns left. Clean the column with VALUE and TRIM, or remove stray spaces with Find and Replace.
What is the rule for VLOOKUP to work?
The value you look up must be in the first column of the table range you give the formula, because VLOOKUP only searches to the right. The third argument counts columns inside that range from one, and the fourth should be FALSE for an exact match. Lock the range with dollar signs before copying the formula down.
Should I use IFERROR or IFNA to hide error codes?
Use IFNA for lookups when it is available, because it only replaces the not-found error and lets real problems such as broken references still appear. IFERROR hides every error, including genuine mistakes. Whichever you pick, choose the replacement on purpose: empty text prints cleanly, zero is easier to sum, and a short word such as Check is the most honest option.
How do I count or total rows by category with COUNTIF and SUMIF?
COUNTIF counts rows that match a criterion and SUMIF adds a matching range. SUMIF takes the criteria range first, the criterion second and the sum range third, and both ranges must be the same size and aligned row by row. For several conditions use COUNTIFS and SUMIFS, noting that SUMIFS takes the sum range first.
Can two people edit the same spreadsheet at the same time?
In a shared online sheet, yes, but the last edit to a cell wins, so two people should not type into the same block. Protect the formula cells and leave the input columns open, and keep a short change log at the top so everyone can see what was entered and when. A file sent by email has no live sharing at all: each copy stands alone and the newest file wins.
What is the fastest way to find a broken formula in a long sheet?
Test one function at a time on a small range and compare the result with a total you can count by hand. Select the cells that feed a formula to see what it actually reads, and check the range against the data before you suspect the function itself. Most faults are a wrong range, a value stored as text, or a reference that moved when the formula was copied.
Related reading
- Best expense tracking software for a small business (2026) — the tool comparison
- Remote work tools for Vietnamese teams (2026) — the wider office toolkit
- Ecommerce payment solutions in Vietnam (2026) — for online sales fees
- Vietnamese version: Hàm Excel cơ bản cho dân văn phòng: 7 hàm dùng hằng ngày
- Laptop Specs for Office Work in 2026: What You Actually Need to Buy
- Best bookkeeping software for a small business (2026) — when the sheet should become a proper ledger
- Best payroll software for a small business (2026) — for wages, tax and payslips
Comments
Share your experience or ask a question about this article — we usually reply within one business day.
No comments yet. Be the first!