10 common Excel mistakes
and how to fix them

1. Not starting a formula with =

If you type SUM(A1:A10) without the equals sign, Excel stores it as text. The cell shows the formula text, not the result. Always start with =.

2. Using absolute references when you need relative (or vice versa)

When you copy a formula, relative references (A1) adjust automatically — which is usually what you want. Absolute references ($A$1) stay fixed. If your formula gives wrong results when copied, this is almost always the cause. Use F4 to toggle between reference types.

3. Storing numbers as text

A number stored as text looks like a number but doesn't behave like one. SUM ignores it, VLOOKUP misses it, sorting puts it in the wrong order. Signs: numbers left-aligned, a green triangle in the corner. Fix: select the cells → Data → Text to Columns → Finish.

4. Leaving blank rows in data

A single blank row in a dataset silently breaks everything — pivot tables stop there, VLOOKUP stops there, tables stop there. Keep data contiguous with no blank rows.

5. Hardcoding values in formulas

Writing =A1*0.2 is risky — if the VAT rate changes, you have to find every formula that uses 0.2 and update it. Instead, put 0.2 in a cell (say B1), label it "VAT Rate", and write =A1*$B$1. Change the rate once and every formula updates.

6. Using VLOOKUP when XLOOKUP is available

VLOOKUP has real limitations — it can't look left, it breaks when you insert columns, and it requires a column index number that's easy to get wrong. If you have Excel 365, use XLOOKUP instead.

7. Not using Excel Tables

Plain ranges don't expand. Formulas that reference them miss new rows. Pivot tables built from them don't update. Convert data to a Table (Ctrl+T) and these problems disappear.

8. Merging cells in data tables

Merged cells break sorting, filtering, VLOOKUP, and pivot tables. They look tidy but cause constant problems. Use "Centre Across Selection" instead — it looks identical but doesn't merge.

9. Circular references

A circular reference is when a formula refers to its own cell, directly or indirectly. Excel can't calculate it and shows a warning. Check Formulas → Error Checking → Circular References to find the problem cell.

10. Not saving with Ctrl+S frequently enough

Excel doesn't autosave unless you're on OneDrive or SharePoint. Press Ctrl+S regularly. Or better: File → Options → Save → set AutoRecover to every 5 minutes.