MS Excel
🔒 Log in to trackFormula errors, charts and data tools
🔒 Log in to trackExcel shows an error value that starts with # when a formula cannot work. Each error has one cause. Charts turn numbers into pictures: column, line, pie and scatter. Tools such as filter, PivotTable, Goal Seek and conditional formatting analyse data.
Error values
When Excel cannot finish a formula, it shows an error that starts with #.
| Error | Cause |
|---|---|
| #DIV/0! | Division by zero, or by an empty cell |
| #NAME? | Excel does not know the name. Usually a misspelt function |
| #VALUE! | Wrong type of value, such as adding text to a number |
| #REF! | The formula points to a cell that was deleted |
| #N/A | A value is not available. Common when a lookup finds no match |
| #NUM! | A number problem, such as the square root of a negative number |
Watch: Five hash signs (#####) do not mean an error. The column is too narrow to show the number. Widen the column.
Handling errors
=IFERROR(value, value if error) catches any error and shows a replacement. For example, =IFERROR(A1/B1,0) shows 0 when B1 is empty or zero.
Chart types
| Chart | Best for |
|---|---|
| Column | Comparing values between items |
| Bar | The same, with horizontal bars |
| Line | A trend over time |
| Pie | Parts of one whole. Uses a single data series |
| Scatter (XY) | The link between two numbers |
| Area | Change in total over time |
Create a chart from the Insert tab. Select the data first. The shortcut F11 makes a chart on a new sheet. Alt+F1 puts a chart on the current sheet.
A chart has a title, axes, a legend, data labels and gridlines. The X axis is the category axis. The Y axis is the value axis.
Tip: If a question mentions percentages of a total, choose a pie chart.
Data tools
- Sort puts rows in order, from A to Z or from small to large.
- Filter hides rows that do not match. The shortcut is Ctrl+Shift+L.
- Conditional formatting colours cells that meet a rule, such as marks below 40.
- PivotTable summarises a large table in a few clicks. It is on the Insert tab.
- Goal Seek finds the input that gives a result you want. It is under What-If Analysis on the Data tab.
- Data Validation limits what a cell accepts, such as a drop-down list.
- Freeze Panes keeps headings in view while you scroll.
Example: Goal Seek answers "What mark do I need in the last test to average 75?"
Formatting and printing
- Format Cells (Ctrl+1) sets number, date, currency and percentage looks.
- Merge & Center joins selected cells into one and centres the text.
- Wrap Text shows long text on several lines inside one cell.
- Print Area and Page Break Preview control what prints.
- Comments and Notes attach remarks to a cell.
- Sparklines are tiny charts drawn inside a single cell.
Question types you will see
Each type: how to recognise it, the method step by step, and one question to try.
Match the error to its cause
A situation is described and the error name is asked.
Spot the cause: division, spelling, type, deleted cell, no match, number.
Match it to the error name.
Remember that ##### is a width problem.
Every error has one cause, and the name hints at it.
Which error appears when a formula divides a number by zero?
Show solutionHide solution
Division by zero is the cause.
The error name has DIV in it.
#DIV/0!
#NAME?, #VALUE! and #REF! compared
Three look-alike errors are listed and one must be chosen for a cause.
Misspelt function gives #NAME?.
Text used in arithmetic gives #VALUE!.
A deleted cell gives #REF!.
The three are easy to swap, so tie each to its cause.
=SUMM(A1:A5) is typed by mistake. Which error appears?
Show solutionHide solution
SUMM is not a real function.
Excel cannot recognise the name.
#NAME?
Choose the right chart
A data story is given, and the chart type is asked.
Comparing items points to a column chart.
A trend over time points to a line chart.
Parts of a whole point to a pie chart.
Two-number links point to a scatter chart.
Each chart is built for one kind of story.
Monthly sales of a shop for 12 months should be shown as a:
Show solutionHide solution
The data runs over time.
A trend is needed.
A line chart shows a trend.
Line chart
Data analysis tools
The question describes a task and asks for the tool.
Summary of a big table means PivotTable.
Finding a needed input means Goal Seek.
Colour by rule means conditional formatting.
Hiding rows that do not match means Filter.
Each tool answers one type of question.
Which tool finds the input needed to reach a target result?
Show solutionHide solution
The target result is fixed.
The input is unknown.
Goal Seek works backwards.
Goal Seek
Chart creation keys and parts
The question asks the key for a chart or the name of a chart part.
F11 makes a chart on a new sheet.
Alt+F1 makes a chart on the same sheet.
X axis holds categories. Y axis holds values.
The shortcuts and axes are short facts asked directly.
Which key creates a chart on a new chart sheet?
Show solutionHide solution
A separate chart sheet is wanted.
The key is F11.
F11
Shortcuts that save time
DIV is division. NAME is a spelling problem. VALUE is a wrong type. REF is a reference that is gone. N/A is not available. NUM is a number problem.
A pie chart shows parts of one whole. It uses a single data series. Use line for trends and scatter for two-number links.
Which chart best shows each state's share of total sales?
Show solutionHide solution
Shares of one whole are needed.
That is the pie chart's job.
Pie chart
Mistakes to avoid
Where most students lose marks on this subtopic.
Calling ##### a formula error.
It only means the column is too narrow. Widen it.
Blaming #NAME? on a bad number.
It usually comes from a misspelt function name.
Using a line chart for parts of a whole.
Use a pie chart for parts of a whole.
Confusing Filter with Sort.
Sort re-orders rows. Filter hides rows.
Thinking #REF! comes from division by zero.
Division by zero gives #DIV/0!. #REF! comes from a deleted cell.
Quick revision
Read this the night before the exam.
#DIV/0! divide by zero. #NAME? unknown name. #VALUE! wrong type. #REF! deleted cell. #N/A not available. #NUM! number problem.
means the column is too narrow. It is not an error.
IFERROR(value, replacement) replaces any error.
Column compares, line shows trend, pie shows parts of a whole, scatter shows a link between two numbers.
F11 makes a chart on a new sheet. Alt+F1 makes an embedded chart.
Filter Ctrl+Shift+L. PivotTable summarises. Goal Seek finds a needed input. Conditional formatting colours cells.
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.