Last updated: 2026-08-19
Interactive Excel Lab: open the bilingual lab — a real formula engine with 64 rows of live data, where every formula you type runs and the cells you reference light up.
Đọc bản tiếng Việt: Excel từ gốc — công thức, hàm, và những cái bẫy không ai cảnh báo. This guide pairs with four auto-graded practice courses: Excel basics, Formulas and references, Functions and intro statistics, and Data analysis.
Most people learn Excel as a pile of disconnected tricks. Somebody shows them VLOOKUP, they write it down, and six months later they still cannot say why it sometimes returns the wrong row. This guide takes the other route. There are perhaps six ideas underneath all of Excel, and once you hold them the several hundred functions stop being a list to memorise and become a vocabulary you can reason about.
The one idea: point, do not type
A spreadsheet is not a calculator with a nicer skin. The difference is the whole point of the tool.
Type =25012 into a cell and you get 3000, and you have gained nothing over a phone. Type =B312 and you have built a small machine: when the number in B3 changes, your answer changes with it. You did not compute a result, you described a relationship. Everything else in Excel is built on top of that single move.
This gives you the first working rule, and it is worth more than any function: if a number appears anywhere else on the sheet, point at its cell rather than retyping it. The exception is a universal constant — twelve months in a year, a hundred cents in a dollar — because those will never change, so nothing is gained by giving them a cell.
The rule sounds obvious and is broken constantly. Hard-coded numbers hidden inside formulas are the single most common reason a spreadsheet is wrong and nobody notices. There is a two-second test: press Ctrl and the backtick key. Every cell flips from showing its result to showing its formula, and typed-in numbers stand out immediately. Press it again to flip back.
References, and the dollar sign that pins them
Every cell has an address, written column-then-row: E2 is column E, row 2. Point at another sheet by prefixing its name: Data!E2.
When you copy a formula, Excel does not copy the text. It copies the relationship. Write =B312 in D3, drag it down to D6, and Excel writes =B612 for you, because what you actually said was "the cell on my row, in column B". This is why you never retype a formula five hundred times, and it is the second core idea.
It is also the source of the most common breakage in beginner spreadsheets. Suppose you want each row as a percentage of a total sitting in B12. You write =B3/B12, fill it down, and it collapses — because the second reference moved too, and now points at B13, B14, B15, which are empty. The fix is a dollar sign, which pins a reference so filling cannot move it:
=B3/$B$12pins the total. The numerator still travels down, the denominator holds still. This is an absolute reference.$B3pins the column but lets the row move.B$3pins the row but lets the column move. These are mixed references, and they are what makes a grid work — one formula that fills both across and down.- Pressing
F4while editing a reference cycles through all four lock modes, which is faster than typing dollar signs.
The question to ask before every fill is simply: when this formula moves, what is allowed to move with it? The answer tells you exactly where the dollars go.
Operators and the order they run in
Excel uses +, -, *, / and ^. There is no x for multiplication and no superscript for powers, and typing either produces an error rather than a calculation.
Operations run in the standard mathematical order: parentheses first, then exponents, then multiplication and division left to right, then addition and subtraction left to right. Parentheses are not decoration; they are the only way to override that order.
The classic demonstration is percent change. Between last year in E3 and this year in D3, the correct formula is =(D3-E3)/E3. Drop the parentheses and write =D3-E3/E3 and Excel obediently divides E3 by itself first, giving 1, then subtracts — producing a number in the hundreds of thousands of percent. It does not error. It just answers a different question than the one you asked, and it will keep doing that quietly for as long as you let it.
Which brings up the principle that matters more than any syntax: a formula that runs is not a formula that is right. Every result deserves a two-second sanity check against what you know about the data. If last year and this year are nearly identical and the sheet reports a 299,900% change, the sheet is not telling you about your business.
Ranges, and the colon that makes them
B3:B11 means "B3 through B11". That colon turns nine separate cells into a single thing you can hand to a function.
=B3+B4+B5+B6+B7+B8+B9+B10+B11 and =SUM(B3:B11) return the same number today. They are not equally good. The range version is shorter, far harder to get wrong, and survives you inserting a row in the middle — which the chain of plus signs silently does not.
One habit worth building: never let a range swallow the header row. =SUM(B2:B11) where B2 holds the word "Revenue" happens to work, because SUM ignores text. =COUNT(B2:B11) on the same range does not, and the difference will not announce itself.
The functions that actually earn their place
There are hundreds. In practice, an analyst uses roughly twenty, and the ones below cover the vast majority of real work.
Describing a column
Before you draw a single chart, describe your data. SUM, AVERAGE, MEDIAN, COUNT, COUNTA, MAX, MIN and STDEV take about a minute between them and will catch most of the errors hiding in a fresh dataset.
Three distinctions inside that list matter more than they look:
AVERAGEskips blank cells but does not skip cells containing zero. One stray zero drags your mean down and nothing warns you.COUNTcounts only numbers.COUNTAcounts everything that is not empty, text included. Point COUNT at a column of names and it returns zero, and you conclude your data is missing when it is sitting right there.AVERAGEandMEDIANagree on symmetric data and disagree on skewed data. When they disagree substantially, that gap is itself the finding — report both and say so.
MAX and MIN deserve a special mention because they are the cheapest data-quality check that exists. A negative where negatives are impossible, an absurd high from an extra typed zero, a date that wandered into a money column — all of it surfaces in two seconds.
Making the sheet decide
IF takes three parts, always in this order: the test, what to return when it is true, and what to return when it is false. =IF(D3>1000,"High","Low"). Text you return must be quoted; numbers must not be. The missing third part is the classic beginner bug — leave it off and the false branch returns the word FALSE into your data.
AND and OR slot into the test position when one condition is not enough. =IF(AND(C2="Beach",A2=2025),"Yes","No") fires only when both hold. Note the asymmetry that trips everyone: the text comparison needs quotes, the number comparison must not have them, and swapping that is how you write a condition that can never be true.
Totalling only what matters
This is the step from knowing Excel to being able to analyse with it.
=SUMIF(criteria_range, criteria, sum_range) totals only the rows that match. =COUNTIF(criteria_range, criteria) counts them. The criteria can be a value, "Beach", or a comparison written as text, ">50000".
The argument order is the trap. In SUMIF the range you test comes first and the range you add comes last. In SUMIFS — the plural version that takes several conditions — the order flips, and the range you add comes first, followed by pairs of range-and-criteria. Convert a working SUMIF into a SUMIFS without flipping the arguments and Excel still runs it. It just returns a different number, with no error and no warning.
Joining two tables
A lookup answers "given this key, what is the matching value over there".
=XLOOKUP(value, lookup_range, return_range) is the modern one. It matches exactly by default, it can look leftwards as well as rightwards, and it takes an optional fourth argument for what to return when nothing matches.
VLOOKUP is the older one and carries a genuinely dangerous default. Its last argument controls match mode, and if you leave it off, VLOOKUP performs an approximate match: it walks down the column and returns the last value that is less than or equal to your key. On unsorted data that is effectively a random row. It does not error. Always end a VLOOKUP with FALSE (or 0), every single time.
Surviving contact with real data
Two functions do most of the cleaning work. TRIM strips stray spaces, and LEN counts characters so you can find the stray spaces in the first place. This matters because "Beach " with a trailing space is, to Excel, a completely different value from "Beach". Your COUNTIF undercounts, your pivot grows a duplicate category, and no error ever appears. UPPER and LOWER do the same job for inconsistent capitalisation.
IFERROR(calculation, fallback) is the other survival tool. =E2/F2 is fine until F2 is blank or zero, at which point the column fills with #DIV/0! and one error cell poisons every SUM downstream of it. =IFERROR(E2/F2,"") computes normally and returns a blank when it cannot.
Tables are not formatting
Pressing Ctrl+T on a block of data does something structural, not cosmetic. The range becomes a named object that grows when you add rows, that every formula and chart pointing at it follows automatically, and that lets you write =[@Revenue]/[@NumBikes] instead of =E2/F2.
That last part is worth more than it appears. A structured reference reads like a sentence. Six months later, =[@Revenue]/[@NumBikes] still tells you what it does, while =E2/F2 requires you to go and look.
Two settings when you create one: turn on Header Row and Banded Rows, and leave Total Row off unless something specifically asks for it, because a total row wedges an extra row into the data range and quietly breaks counting formulas.
Sorting, and the click that destroys data
Sorting is the most dangerous ordinary operation in Excel.
Select a single column, press Sort, and Excel asks whether to expand the selection. Choose "Continue with the current selection" and that one column reshuffles while every other column stays put. Every row in your dataset is now severed — the revenue on row 40 belongs to a different location than the one sitting beside it — and there is no way to reconstruct which went with which. Undo is the only remedy, and only if you notice immediately.
Always expand the selection. Better, sort inside an Excel Table, which cannot make this mistake by construction.
For multi-level sorting — by bike type, then by year within each type, then by quarter within each year — do it in one pass. Open Data, then Sort, set the first level, and use Add Level for the second and third. Three separate sorts do not stack; each one discards the ordering of the one before it, so you keep only the last.
Filters, by contrast, are safe: a filter hides rows rather than deleting them. Two cautions all the same. Hidden rows still count in SUM, so use SUBTOTAL when you want to total only what is visible. And never send a file with a filter left on, because the person opening it will read your slice as the whole dataset.
PivotTables in four drop zones
A PivotTable summarises thousands of rows in seconds, and all of its power is in one decision: which field goes in which zone.
- Rows — what you want listed down the side
- Columns — what you want across the top
- Values — the number being aggregated
- Filters — a slice applied to the whole table
Writing those four out before you build is a good discipline, because that list is your question, stated precisely.
One trap. When you drop a field into Values, Excel guesses the aggregation, and if the column contains even a single blank or text cell, it guesses Count rather than Sum. You then read "64" believing you are looking at revenue. The label in the corner of the pivot tells you which one you got. Read it before you read the number.
Charts, and where they lie
Chart type is chosen by the question, not by taste. Comparing categories calls for bars. Change over time calls for a line. The relationship between two numeric variables calls for a scatter. Parts of a whole may call for a pie, but only with very few slices — past four or five the human eye cannot compare angles, and bars serve better.
Then there is the honesty question, which is really the heart of data literacy. Take a bar chart and start its value axis at 48 instead of 0, and a two percent difference occupies half the chart. Nothing you plotted is false. The picture is a lie anyway, because the eye reads bar height as quantity, so bars must start at zero. Line charts may truncate, because the eye reads slope rather than height and no such implicit comparison is being made.
Two more rules that cost nothing: put units on the axis label, and cite the source under the chart. A chart without units is a decoration, and a chart without a source is an assertion.
The error codes are diagnoses
Excel's errors are not failures, they are a small diagnostic vocabulary. Learn these five and you will fix most breakages without looking anything up.
#DIV/0!— divided by zero or by an empty cell. Wrap it in IFERROR, or test the denominator first.#N/A— a lookup found nothing. Check spelling, check for stray spaces with TRIM, check that both sides are the same data type.#NAME?— Excel does not recognise something you typed. A misspelled function, or text without quotes around it.#REF!— the formula points at a cell that has been deleted. Undo immediately; this one does not heal itself once saved.#VALUE!— wrong data type, usually text sitting where a number belongs. Look for cells that are left-aligned when their neighbours are right-aligned, which is Excel quietly telling you it sees text.
Where to go next
Read this once and you will forget most of it, which is fine and normal. The parts that stick are the parts you type.
Open the Excel Lab, which runs a real formula engine over sixty-four rows of live data — type a formula, watch it evaluate, and watch the cells it touches light up in the grid. Then work through the four practice courses, where each lesson is a test suite that tells you precisely what your formula is still missing: basics, formulas and references, functions and statistics, and data analysis.
Forty-six lessons, all auto-graded, all in the browser. Nothing to install.