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.
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.
Question types you will see
Each type: how to recognise it, the method step by step, and one question to try.
Error-message identification
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!.
Amit types =SUIM(A1:A5) and Excel shows #NAME?. Why?
Show solutionHide solution
The function name is misspelt — Excel cannot recognise SUIM, and unknown text produces #NAME?.
Chart selection by purpose
'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.
To display the monthly sales trend of a year, the best chart is —
Show solutionHide solution
Line chart — trends across time are its exact purpose; a pie only shows shares of one total.
Data tools: sort, filter, pivot, goal seek
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.
Which Excel feature finds the marks needed in the last test so that the average becomes 80?
Show solutionHide solution
Goal Seek (What-If Analysis) — it adjusts the input cell until the formula reaches the target value.
Chart parts and labels
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.
In an Excel chart, the small key that explains what each colour represents is called the —
Show solutionHide solution
Legend — without it the reader cannot tell the series apart.
Shortcuts that save time
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.
A cell shows #####. The problem is?
Show solutionHide solution
Column width, not a formula error.
Pie = Parts, Line = Line of time (trend), Column = Compare, Scatter = relationship. 'Trend over months' questions always end at Line.
Best chart for monthly sales trend?
Show solutionHide solution
Line chart.
Goal Seek: you give the answer, it finds the input. Pivot Table: you give the data, it gives summaries.
Which tool finds the marks needed in the last test to average exactly 80?
Show solutionHide solution
Goal Seek.
Mistakes to avoid
Where most students lose marks on this subtopic.
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?.
Quick revision
Read this the night before the exam.
#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?".
Practice: 20 questions
Sets of 10, mixed across the question types above. Every answer has a step-by-step explanation.
Topic test · 10 questions
Suggested time 5 min · wrong answers go to your mistake notebook automatically.