MS Excel
🔒 Log in to trackFormula errors, charts and data tools
🔒 Log in to trackError messages:
| Error | Cause |
|---|---|
| #DIV/0! | Division by zero or by an empty cell |
| #NAME? | Text Excel cannot recognise (misspelt function/name) |
| #VALUE! | Wrong data type in the formula (text where a number is needed) |
| #REF! | Invalid cell reference (referenced cells deleted) |
| #N/A | Lookup value not found |
| #NUM! | Bad numeric argument (e.g. SQRT of a negative) |
| ###### | Column too narrow - a display issue, NOT an error |
| Circular Reference warning | Formula refers back to its own cell |
Charts: Column - compare categories; Line - trends over time; Pie - parts of one whole; Bar - horizontal comparison; Area - cumulative trend; Scatter - relationship between two variables. Alt+F1 inserts a default chart on the sheet; F11 puts it on a new chart sheet.
Data tools: Sort (multi-level), Filter/AutoFilter (Ctrl+Shift+L), Conditional Formatting (colour scales, icon sets), Data Validation (dropdowns/limits), Pivot Table (drag-and-drop summary of big data), What-If Analysis - Goal Seek (find the input that produces a wanted output), Scenario Manager, Data Table. Freeze Panes locks rows/columns on screen; Wrap Text shows long text on multiple lines; Merge & Center combines cells; Sheet protection locks cells against edits; Sparklines are tiny in-cell charts.
Detailed notes
Excel error messages — what each one means
| Error | Why it appears |
|---|---|
| #DIV/0! | division by zero or an empty cell (=A1/B1 with B1 empty) |
| #NAME? | Excel does not recognise the text — usually a misspelt function (=SUIM) |
| #VALUE! | wrong type of data (=A1+5 where A1 holds "exam") |
| #REF! | a formula points to cells that were deleted |
| #N/A | a lookup found nothing (VLOOKUP misses) |
| #NUM! | impossible number, like =SQRT(-4) |
| ##### | not an error — the column is just too narrow |
Memory hooks: DIVide by zero, NAME unknown, VALUE wrong type, REFerence lost.
Charts — pick by the question the picture must answer
| Chart | Use it for |
|---|---|
| Column / Bar | comparing values between categories (sales of 5 branches) |
| Line | a trend over time (monthly temperature) |
| Pie | shares of one whole (budget split) — one data series only |
| Scatter | relationship between two number variables (height vs weight) |
Chart furniture: chart title, legend (which colour is which series), axes with titles, data labels (numbers printed on bars), plot area. Insert charts from the Insert tab.
Data tools
- Sort reorders rows A-Z or Z-A (largest to smallest); Filter hides rows that do not match a condition — filtering never deletes data.
- Conditional formatting paints cells automatically when a rule is true (marks below 35 turn red).
- PivotTable summarises thousands of rows into a compact cross-tab in seconds.
- Goal Seek (What-If Analysis) works backwards: it changes an input cell until the formula gives the result you want — e.g. what marks are needed to average 80.
Choosing a chart in three seconds
Ask: what should the reader SEE?
- A rise or fall across time → line chart.
- Who is bigger among a few categories → column (vertical bars) or bar (horizontal bars) chart.
- How the total is divided → pie or doughnut chart (one series only).
- Do two measurements move together? → scatter chart. Wrong-chart questions reuse this list: a pie for a time trend, a line for a share split, a 3-D explosion of a simple comparison — all wrong picks. Charts live on the Insert tab; a selected range plus F11 makes an instant chart sheet.
Quick revision
- #DIV/0! zero, #NAME? spelling, #VALUE! wrong type, #REF! deleted cells, #N/A lookup miss, ##### narrow column.
- Column/Bar compare, Line trend, Pie share, Scatter relation.
- Legend tells which colour is which series.
- Sort orders, Filter hides, conditional formatting colours by rule.
- PivotTable summarises; Goal Seek answers "what input gives this result?".
Types of questions asked
Every way this subtopic shows up in exams — how to recognise it, the formula or logic to use, and a solved example.
Type 1: Error-message identificationvery common5 practice Q
A formula situation is described (divide by empty cell, misspelt function, deleted range, lookup miss) and the error shown is asked — or an error like #NAME? is given and its cause asked.
- #DIV/0! = divide by zero/blank; #NAME? = text Excel cannot recognise; #VALUE! = wrong data type; #REF! = deleted reference; #N/A = lookup found nothing.
-
is NOT an error — it is a narrow column.
- Match the story: spelling gives #NAME?, deletion gives #REF!, empty divisor gives #DIV/0!.
Example: Amit types =SUIM(A1:A5) and Excel shows #NAME?. Why?
The function name is misspelt — Excel cannot recognise SUIM, and unknown text produces #NAME?.
Type 2: Chart selection by purposevery common4 practice Q
'Which chart best shows a trend over time / share of a whole / comparison between categories?' — four chart types as options.
- Trend across time = Line. Share of one whole = Pie. Compare categories = Column/Bar. Relation of two number variables = Scatter.
- A pie handles ONE data series only.
- 'Over the years / month-wise growth' words point to a line chart.
Example: To display the monthly sales trend of a year, the best chart is —
Line chart — trends across time are its exact purpose; a pie only shows shares of one total.
Type 3: Data tools: sort, filter, pivot, goal seekcommon4 practice Q
A need is described (hide non-matching rows, summarise thousands of rows, find the input that achieves a target) and the tool is asked.
- Sort reorders; Filter hides non-matching rows (data is never deleted).
- Conditional formatting colours cells by a rule; PivotTable summarises big data into a compact table.
- Goal Seek works backwards from a wanted result to the needed input.
Example: Which Excel feature finds the marks needed in the last test so that the average becomes 80?
Goal Seek (What-If Analysis) — it adjusts the input cell until the formula reaches the target value.
Type 4: Chart parts and labelsoccasional3 practice Q
The stem asks the name of the chart key that explains colours (legend), the axis holding categories, or the numbers printed on bars (data labels).
- Legend = the key that says which colour stands for which series.
- Category labels usually sit on the horizontal (x) axis, values on the vertical (y) axis — bar charts swap them.
- Data labels print the value on each bar or slice.
Example: In an Excel chart, the small key that explains what each colour represents is called the —
Legend — without it the reader cannot tell the series apart.
Shortcut tricks
⚡ ###### is not an error
Widen the column - done. Real errors start with #: DIV/0 (zero), NAME (spelling), VALUE (type), REF (deleted), N/A (not found). Match the cause table above.
Example: A cell shows #####. The problem is?
Column width, not a formula error.
⚡ Chart chooser
Pie = Parts, Line = Line of time (trend), Column = Compare, Scatter = relationship. 'Trend over months' questions always end at Line.
Example: Best chart for monthly sales trend?
Line chart.
⚡ Goal Seek direction
Goal Seek: you give the answer, it finds the input. Pivot Table: you give the data, it gives summaries.
Example: Which tool finds the marks needed in the last test to average exactly 80?
Goal Seek.
Where students lose marks
Calling ##### an error - it is only a narrow column.
Using a pie chart for a time trend - pies show one whole's shares at one point.
Saying #REF! appears for misspelt functions - that is #NAME?.
Practice sets — 20 questions
Sets of 10, mixed across the question types above. Each answer comes with a step-by-step explanation.
Topic test · 10 questions
Suggested time 5 min · wrong answers go to your mistake notebook automatically.