BLC
Year 7
Excel Reference
Everything you need for spreadsheets this year — formulas, charts and how to fix common mistakes.
Formula reference
| Formula | What it does | Example |
|---|---|---|
=SUM(A1:A10) | Adds up a range of cells. | =SUM(B2:B7) |
=AVERAGE(A1:A10) | Finds the mean (average) of a range. | =AVERAGE(H2:H7) |
=MIN(A1:A10) | Finds the smallest value in a range. | =MIN(F2:F7) |
=MAX(A1:A10) | Finds the largest value in a range. | =MAX(F2:F7) |
=B2*C2 | Multiplies two cells together. | Cost × quantity |
=D9-D8 | Subtracts one cell from another. | Budget remaining |
Tip: every formula starts with an = sign. If it's missing, Excel just treats it as text.
Chart type guide
| Chart | Best for |
|---|---|
| Bar chart | Comparing amounts between categories, e.g. votes per lunch option. |
| Pie chart | Showing parts of a whole, e.g. percentage of the class who chose each option. |
| Line chart | Showing change over time, e.g. temperature across the months. |
| Scatter chart | Showing a relationship between two numbers, e.g. hours revised vs test score. |
Step-by-step: making a bar chart
- Select your data, including the column headings.
- Go to Insert → Chart.
- Choose Bar Chart (sometimes called Column Chart).
- Click the chart, then add a title using the "+" or Chart Design menu.
- Label your axes so anyone can understand the chart.
Step-by-step: making a pie chart
- Select just two columns: the category names and the numbers.
- Go to Insert → Chart → Pie Chart.
- Turn on Data Labels so each slice shows its value or percentage.
- Add a title that explains what the pie chart shows.
Common errors and fixes
| Problem | Fix |
|---|---|
| Formula shows as text, not a number | Check it starts with =. |
#REF! error | A cell your formula pointed to has been deleted. Rewrite the formula. |
#DIV/0! error | You're dividing by an empty or zero cell. |
| Chart shows the wrong data | Click the chart → Select Data → check the highlighted range. |
| Autofill copied the wrong thing | Drag the small square (fill handle) at the bottom-right of the cell, don't copy-paste. |
Practice datasets
Use these to practise formulas and charts. See the Data & Datasets page for previews of each one.