Today's Goals

  • Self-check six core Excel skills
  • Finish one mixed mini-exercise end to end
  • Know what to learn next and which course to open

Key Concepts

1. What comes after the basics

Five lessons built five skills: formulas and addresses, common functions, VLOOKUP, pivot tables, conditional formatting. The sixth is the habit that binds them inside one workbook — clean data. Messy sources break reports no matter how many functions you know; with clean sources, all six skills click together. The check counts these six, not how many functions you can recite.

2. The six-skill checklist

  1. Clean data: one record per row, no merged header cells
  2. Formulas: arithmetic plus how addresses drift when dragged
  3. Functions: SUM/AVERAGE/MAX/MIN/COUNT from memory
  4. Lookup: four VLOOKUP arguments on demand; check formats first when #N/A appears
  5. Aggregation: 3000 rows summarized in five minutes
  6. Alarms: whole-row conditional formatting, $E2 locked correctly

Stuck on one? Go back to that lesson only.

3. The one insight that matters

Beginner formulas live in plain sight — visible and traceable. Real projects are the same six skills rearranged by business logic: a stock ledger is clean flow records + VLOOKUP on the item file + summary functions + conditional-format alerts. Skills constant, arrangement new. That is the honest relationship between basics and projects.

Today's Code

Mixed exercise: a 20-row sales detail (date / rep / region / product / price / qty), six tasks:

Task Skill Reference
Amount per row Formula =E2*F2, drag down
Total and average Functions =SUM / =AVERAGE
Category per product Lookup =VLOOKUP(D2,CatTable,2,FALSE)
Sales per region Aggregation Pivot: Rows=Region, Values=Amount
Highlight top five Alarms Conditional format: greater than the 5th highest
Audit the source Clean data No merges, no blank rows, real dates

Every task verifies on its own: spot-check two rows of task 3; task 4's grand total must equal task 2's — two different tools agreeing on one number is the hard self-check.

Exercises

  1. Finish all six tasks, timed; target: 30 minutes.
  2. Turn one price into text on purpose; watch tasks 1, 2 and 4, then repair the damage.
  3. Add Rep as a pivot column; verify the region totals still match task 2.

Exercise Solutions

  1. Suggested order: clean first (task 6), then amounts, functions, lookup, pivot, colors — reversing the order means rework.
  2. The sum shrinks or returns 0; the pivot counts instead of sums. Fix: select the column → Data → Text to Columns → Finish, or use the green-triangle converter.
  3. Column totals should add up to the grand total; if not, text numbers or duplicate rows remain in the source.

Real-World Scenario

The question after lesson six is always "which project next?" The paths are ready-made: stock control in the Inventory course (18 lessons, from one sheet to a traceable system); trading in the Purchase-Sales course (24 lessons: files, flows, pivots, dashboards); customers in the CRM course (20 lessons, leads to payments, with VBA automation late on). All three assume exactly these six skills and open with business modeling. Basics teach skills; projects teach combinations. When a project lesson gets hard, one of the six is usually shaky — come back and patch just that one.

Common Errors and Troubleshooting

The errors scattered across lessons 1–5, gathered into one master checklist:

Symptom Likely cause Back to
#NAME? Misspelled function / full-width punctuation Lesson 2
#N/A Format mismatch / hidden spaces Lesson 3
#REF! VLOOKUP column index out of range Lesson 3
#DIV/0! No numbers in the AVERAGE range Lesson 2
Invalid field name (pivot) Blank headers / merged cells Lesson 4
Whole block colors $ locked the row too Lesson 5
Sum stays 0 Numbers stored as text Lesson 2

Extension Exercises

  1. Redo all six tasks on real data from your own job — none skipped.
  2. Add a "Notes" column full of stray text, re-run the pivot, and feel how a dirty field interferes; practice cleaning it.

Summary

The path closes here: formulas, functions, lookup, pivots, alarms, clean data. Pass the six-skill check and the three project courses are open to you. The bar is simple — six for six within 30 minutes, and you are no longer a beginner.

由在线工具箱(www.vba.net)整理制作