Everyday spreadsheet checks

Who this is for

For readers who can enter values in cells and want to understand a few useful formulas through small examples.

What you will practise

• Clean ordinary extra spaces while keeping words separate.
• Understand when nonbreaking spaces need explicit replacement in Excel.
• Use IF to label a threshold and distinguish a missing count from zero.
• Calculate increases and decreases relative to the original value.
• Explain why a zero starting value has no ordinary percentage-change result.
• Explain why rounding each row can differ from rounding the final total.
• Distinguish numeric readings, nonempty cells and blank results with COUNT, COUNTA and COUNTBLANK.
• Explain how missing observations and recorded zeros affect an arithmetic mean.
• Find negative and positive extremes and distinguish a statistical range from an average.
• Flag repeated alphanumeric IDs while separating missing IDs and legitimate repeated records.

Your learning path

1. TRIM in Excel: clean extra spaces without joining words

   Clean inconsistent customer labels while preserving the space between words. Then handle a nonbreaking space copied from a web page and check what the cleanup changes.

2. COUNTIF in Excel: flag repeated IDs before changing the data

   Count repeated booking identifiers, keep blank IDs out of the flags, and distinguish repeated rows from extra copies.

3. IF in Excel: flag low stock and keep missing counts visible

   Compare a stock count with a minimum level. Decide what happens exactly at the threshold, then distinguish a missing count from a real zero.

4. COUNT, COUNTA and COUNTBLANK: what counts as an empty cell?

   Audit a small measurement log. Distinguish numeric readings, entered notes, truly empty cells and formulas that look blank.

5. AVERAGE in Excel: why a blank and a zero give different results

   Calculate a mean from a small equipment log, show how many readings support it, and avoid replacing missing observations with zero.

6. MIN and MAX in Excel: find the extremes and calculate the range

   Use a short temperature log to find the lowest and highest readings, calculate their difference, and avoid reporting missing data as zero.

7. Percentage change in Excel: increases, decreases and a zero starting value

   Compare monthly workshop bookings using the original month as the base. Distinguish a percentage change from an absolute difference and show an explicit result when the starting value is zero.

8. ROUND in Excel: why rounded rows can disagree with the total

   Compare rounding each amount with rounding the final sum. Use a three-row example, check negative midpoint values, and separate number formatting from changing a value.