Use this guide if you can type into a spreadsheet but freeze when you need a formula. Open Excel or Google Sheets with a simple practice list, such as a column of dates, a description, a category and an amount.
Step by step
Understand how formulas work
Every formula starts with an equals sign, such as =A2+B2, and refers to cells by column letter and row number. Click into a cell showing a result to see its formula in the bar above the grid. Press Enter to confirm and Escape to cancel an edit.
Total and average a column
Type =SUM(D2:D50) to add a range, and =AVERAGE(D2:D50) to find the mean. Use =MIN and =MAX to find the smallest and largest values. Select the range with the mouse instead of typing it to avoid mistakes.
Copy formulas with fixed references
Drag the small square at the corner of a cell to copy a formula down, and the cell references move with it. Add dollar signs, such as $F$1, when a reference must stay fixed, for example a single rate cell. Press F4 in Excel while editing a reference to cycle through the options.
Make decisions with IF
=IF(D2>100,"Check","OK") shows one result when a condition is true and another when it is false. You can compare with =, >, <, >= and <>. Keep conditions simple and use helper columns rather than long nested formulas.
Count and total by category
=COUNTIF(C2:C50,"Food") counts how many rows match, and =SUMIF(C2:C50,"Food",D2:D50) totals the amounts for that category. Point the criteria at a cell instead, such as =SUMIF(C2:C50,G2,D2:D50), to build a small summary table that updates automatically.
Look up values with XLOOKUP
=XLOOKUP(G2,A2:A50,D2:D50) finds G2 in column A and returns the matching value from column D. It works in current versions of Excel and in Google Sheets, and older files may use VLOOKUP for the same job. Add a fourth argument such as "Not found" to show a friendly message when nothing matches.
Ready-to-use checklist
- Practice sheet with headings set up
- SUM and AVERAGE working
- Formula copied down correctly
- Fixed reference used with dollar signs
- IF formula tested with both outcomes
- COUNTIF and SUMIF summary table built
- XLOOKUP returning the right values
- Results checked against a manual total
Practical tips
- Check a new formula against a quick total worked out by hand or with a calculator before relying on it.
- Turn your data into a table in Excel or use named ranges so formulas stay readable and grow with new rows.
- Keep raw data on one sheet and formulas or summaries on another so you do not overwrite them by accident.
Common problems
The formula shows as text instead of a result.
The cell is probably formatted as Text or the formula starts with a space or apostrophe. Change the cell format to General or Automatic, remove anything before the equals sign and press Enter again.
A total ignores some numbers.
Some values may be stored as text, often after importing from a bank or website, and they usually sit on the left of the cell. Convert them with the warning icon's Convert to Number option in Excel or Format, Number in Google Sheets, then check the total again.
XLOOKUP returns #N/A even though the value is there.
There may be extra spaces or a mismatch such as a number stored as text in one column. Use =TRIM() to clean the lookup column or make both columns the same format, then try again.
This guide gives general information, not personal legal, financial or medical advice. Rules and prices change, so check the current position with the official service before acting.
This guide is free. Unlock all ABCDayZ guides from £3.99 – one payment, no automatic renewal.