Beginner guide · Everyday calculations

Spreadsheet formulas you might use at work

You do not need dozens of functions to handle a basic office spreadsheet. Most tasks begin with a clear question: “How much in total?”, “What is the typical score?”, “Does this row meet the rule?”, or “How many orders are still open?” The question points you toward a formula.

Add a column with SUM

Suppose a small shop recorded sales of 125, 75, and 200 in cells A1 through A3. Put =SUM(A1:A3) in B1. It shows 400. The range includes every cell between the two endpoints. If new sales reach A4, update the range to A1:A4 so the extra row is counted.

Find a typical value with AVERAGE

If scores 80, 90, and 100 are in A1:A3, use =AVERAGE(A1:A3) to get 90. AVERAGE adds the numeric values and divides by how many there are. If the task asks for the total, use SUM instead; if it asks for the typical or mean value, use AVERAGE. Write the result in the answer cell named by the task.

Calculate an invoice line

An invoice often has a quantity in column B and a price per item in column C. If row 2 has 3 items at 12 each, =B2*C2 in D2 gives 36. The asterisk means multiply. A later =SUM(D2:D6) can add the line totals. Check whether the price is per item or already a total before multiplying.

Make a simple decision with IF

If B2 holds a test score, =IF(B2>=60,"Pass","Fail") displays Pass when the score is at least 60 and Fail otherwise. The first part is the test. The second part is what to display when the test is true; the third part is what to display when it is false. Words such as Pass need quotation marks inside a formula.

Count matching records with COUNTIF

Suppose C2:C5 contains Pending, Shipped, Pending, Cancelled. The formula =COUNTIF(C2:C5,"Pending") returns 2. The first argument is the range to inspect; the second is the label to match. This helps someone answer “How many orders still need attention?” without counting each row by hand.

Choose the formula from the question: “How much?” often means SUM; “what is the average?” means AVERAGE; “does it meet a rule?” means IF; “how many rows match?” means COUNTIF. Read the range and output cell just as carefully as the function name.

What to check when a formula is wrong

It appears as text: look for the opening = and finish editing with Enter.
The answer is too small: the range may stop before the last data row.
You see #VALUE!: a calculation may be using words where it needs numbers. The plus sign does not join names; use a text-joining formula for that job.
The wrong label appears: check the IF comparison, quotation marks, and the order of the true and false results.

More worked examples

The five formulas above handle most beginner office work. The next four come up as soon as you work with customer lists, product sales, or a budget.

Clean up text with UPPER, TRIM, and LEN

Customer and product lists are often typed by different people, so the same thing appears as “p100”, “P100”, and “ P100”. If A2 holds jane smith, then =UPPER(A2) shows JANE SMITH. =TRIM(A2) removes extra spaces at the start and end. =LEN(A2) counts the characters, including spaces, so =LEN("JANE SMITH") returns 10. LEN is a quick way to spot bad entries. If every product code should be four characters, any cell where LEN shows 5 probably has a stray space.

Join first and last names

If A2 holds Maria and B2 holds Lopez, then =CONCATENATE(A2, " ", B2) shows Maria Lopez. The " " in the middle is a space inside quotation marks. Leave it out and you get “MariaLopez”. The ampersand does the same job in a shorter way: =A2&" "&B2. Note that =A2+B2 does not join text. The plus sign is for numbers only.

Profit and margin

Row	A: Product	B: Revenue	C: Cost	D: Profit	E: Margin
2	Desk lamp	250	175	`=B2-C2` → 75	`=D2/B2` → 30%

Profit is what is left after costs: 250 − 175 = 75. Margin shows that profit as a share of the revenue: 75 ÷ 250 = 0.3. Format E2 as a percentage and it displays 30%. A common mix-up is dividing by cost instead (75 ÷ 175 ≈ 43%). That number is called markup, and it answers a different question. If a manager asks for margin, divide by revenue.

Largest and smallest expense with MAX and MIN

A monthly budget lists rent 1200, groceries 340, phone 95, and internet 60 in B2:B5. =SUM(B2:B5) gives the total, 1695. =MAX(B2:B5) finds the largest expense, 1200, and =MIN(B2:B5) finds the smallest, 60. These are useful for spotting where most of the money goes without sorting the list.

Questions first-time users ask

What is the difference between a formula and a function?

A formula is anything you type that starts with =, such as =B2-C2. A function is a named, ready-made calculation used inside a formula, such as SUM, AVERAGE, or IF. Everything inside a function’s parentheses is called its arguments, and commas separate them.

Do function names have to be in capitals?

No. You can type =sum(a1:a3) and the spreadsheet will change it to capitals for you. Capitals are just the convention in guides so the function names stand out.

Why does my cell show ####?

The number is too wide for the column. Nothing is wrong with the formula. Widen the column by double-clicking the border to the right of its letter at the top of the sheet.

Which formula should I learn first?

SUM, then AVERAGE, then IF. Together they cover totals, typical values, and simple decisions, which is most of what beginners are asked to do.

The FreeJobTools trainer lets you make these mistakes safely. It starts with a guided version, then gives you different data so you can confirm that you understand the formula rather than copying a number.

Practice these formulas in the trainer: Text Cleaning & Length · Customer Name Concatenator · Product Profit & Margin · Budget Expense Analyzer · Smart Pass/Fail Evaluator.