GUIDINEE.COM › Microsoft Excel › Level 6 › 6.3

6.3 · Best practices & pitfalls

Level 6 · In practice

6.3Best practices & pitfalls

Goal: adopt the right Excel reflexes and avoid the most frequent design mistakes.
Estimated time: 10 min · If you only have 5 min: read “The essentials in 30s” + the diagram.

The essentials in 30 seconds

  • Structure: named Ctrl+T tables, 3 layers (data, calculations, presentation), shared constants as absolute ($).
  • Make reliable: calculations as formulas (never hard-coded), XLOOKUP and SUMIFS, checked types, IFERROR and documented assumptions.
  • Industrialize: cleaning in Power Query, no merged cells or volatiles in bulk, refreshed pivot — data entered once, referenced everywhere.

Wassim thread — the rule taped to the wall

After a quarter, Wassim taped his rule to the wall in Algiers: structured and proven first, fast next. What mattered most wasn't a function, but three habits — named table, calculations as formulas, checked types — and his biggest pitfall narrowly avoided: a total typed by hand the day before a closing, caught before sending.

Choose a single habit to anchor in your routine, then move to the next one.

The do's and don'ts of a reliable workbook

The difference between a solid workbook and a bug factory comes down to a few design habits. Here are the ones that come up most.

✓ Do

  • Convert the data into structured tables (Ctrl+T) and name them.
  • Separate entered data, calculations and presentation (3 layers).
  • Formulate the calculations (never a « hard-coded » result).
  • Lock shared constants as absolute ($); think through the copy before writing.
  • Prefer XLOOKUP and SUMIFS to fragile workarounds.
  • Clean/consolidate in Power Query, not by hand each month.
  • Check the types (numbers right) before analyzing.
  • Handle errors cleanly (IFERROR) and document the assumptions.

✗ Avoid

  • Typing results hard-coded instead of formulas.
  • Formulas over frozen ranges (D2:D200) that forget the new rows.
  • Merged cells in the data areas (broken sort/formulas).
  • Numbers stored as text → wrong sums.
  • Sorting a single column of a table (rows come apart).
  • Filling a large file with volatile functions (OFFSET/INDIRECT).
  • Forgetting to Refresh a pivot after a source change.
  • A single « v_final » file edited by everyone by email.
Common misinterpretation: « tick all the boxes every day ». No. These are habits to install one by one: choose one (e.g. named tables everywhere), anchor it in your routine, then move to the next.
Text version of the diagram (SEO / screen readers / translation): Balance to hold: 1) On the data side — named Ctrl+T table, unique headers, checked types, zero merged cells, sort on the whole row. 2) On the calculation side — formulas everywhere, constants in $, XLOOKUP and SUMIFS, clean IFERROR, documented assumptions. 3) On the output side — 3 layers, refreshed pivot, Power Query for the recurring, no v_final by email. Lesson: a healthy workbook is first structured and proven — speed comes after.

Ready-to-say script — check your workbook before sending (30 s)

« Named tables? 3 layers? Zero hard-coded? Constants in $? Types right? XLOOKUP and SUMIFS? Pivot refreshed? If any no, I fix it before sending. »

Copy-paste template — healthy formulas

=IFERROR(A2/B2,"To check")
=SUMIFS(Sales[Amount],Sales[Region],$A$1)
=XLOOKUP(A2,Sales[Product],Sales[Amount],"Not found")

SME / small budget

A single habit per quarter is enough: named tables + zero hard-coded first, the rest next.

SME Algiers

Adapt the routines to the closings: a slot for checked types + refreshed pivot held every month.

Remote / hybrid

Discipline #1 at a distance: named source, assumptions and refresh date written down, even for small files.

Intercultural

Check local usages rather than assume: an unusual date or number format must raise a flag everywhere.

Caution: Generic reminder: golden rule — a healthy workbook is first structured and proven; never distribute a v_final by email without a copy, never leave a result hard-coded, and check types and totals before sending.

48h challenge — apply tomorrow

Put your workbook under the microscope: zero hard-coded results, zero frozen ranges, zero text disguised as a number. Fix the first discrepancy found.

Section exit checklist
  • I know the 8 reflexes to adopt and the 8 pitfalls to avoid.
  • I have checked my 3 critical points (named tables, zero hard-coded, types).
  • I did the 48h challenge or planned its date.
Self-assessment 1-5: consistency of the right reflexes?pitfalls avoided?
← Previous : Recurring problems (playbook) (V3)Next : Toolbox (V3) →
Check your understanding

Before his closing, which reflex protects Wassim best?

💡

Golden rule: data entered once, referenced everywhere — never copied by hand.