By the end of this lesson you will be able to
- Create a chart from a table in Calc or Excel and give it a title, axis labels and a legend where needed.
- Choose between column, bar, line and pie charts for a given question.
- Spot the common tricks that make a chart misleading, such as a cut-off axis.
- Check a spreadsheet's formulas, keep backups and share files without leaking private data.
A table of numbers is accurate but tiring to read. A chart turns the same numbers into a picture so that a pattern is visible at a glance: which pupil scored highest, whether sales are rising, how a budget is divided. A chart is also persuasive, which is a risk: the wrong chart type, a cut-off axis or a missing label can lead people to the wrong conclusion. This lesson teaches you to make honest charts. It ends with the habits that keep a spreadsheet trustworthy and safe: checking formulas, taking backups and sharing wisely. The marksheet used here is fictional.
The data: a small class marksheet
A chart is only as good as the table behind it. We will use a marksheet for five fictional pupils in three subjects, with the average calculated by formula. Marks are out of 100.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Name | Maths | English | Science | Average |
| 2 | Amina | 78 | 85 | 72 | 78 |
| 3 | Joseph | 65 | 58 | 70 | 64 |
| 4 | Wanjiru | 92 | 88 | 90 | 90 |
| 5 | Kamau | 54 | 61 | 49 | 55 |
| 6 | Fatuma | 70 | 75 | 68 | 71 |
- Marker 1: Headings name the data
- Marker 2: Numbers to plot
- Marker 3: Names become chart labels
Read this figure as text
A simulated spreadsheet with pupils' marks in maths, English and science and an average column.
A simulated spreadsheet with a marksheet. Row 1 holds the headings Name, Maths, English, Science and Average in columns A to E. Row 2 is Amina 78, 85, 72, 78. Row 3 is Joseph 65, 58, 70, 64. Row 4 is Wanjiru 92, 88, 90, 90. Row 5 is Kamau 54, 61, 49, 55. Row 6 is Fatuma 70, 75, 68, 71. Cell E2 is selected and the formula bar shows =AVERAGE(B2:D2). The range A1:B6 is highlighted to show the cells that would be selected for a simple chart of Maths marks. Marker 1 points to the heading row, which gives the chart its labels. Marker 2 points to the Maths column, the numbers to plot. Marker 3 points to the pupil names, which become the labels along the bottom of the chart.
Making a chart
Build a column chart of Maths marks
Step 1: Select the data
Select the names and the Maths marks together, A1:B6, including the headings.
You should see: The block is highlighted and the Name Box shows A1:B6.
Step 2: Insert the chart
Choose Insert, then Chart... (Calc). In Excel, choose Insert and then a column chart from the chart group, or Recommended Charts.
You should see: In Calc a Chart Wizard opens with a preview. In Excel a chart appears on the sheet.
Step 3: Set type and title
In Calc, choose the type Column in the first step of the wizard. Then click Next through the steps for the data range and series, and in the last step, Chart Elements, type the title
Maths marks, Class 5B, and tick the display of the legend only if there is more than one series. Click Finish.You should see: A column chart appears: one column per pupil, with Wanjiru's the tallest and Kamau's the shortest.
Step 4: Add axis titles
Add axis titles. In Calc, double-click the chart to enter edit mode, then choose Insert, then Titles.... In Excel, click the chart, then the + button (Chart Elements) and tick Axis Titles. Type
Pupilfor the horizontal axis andMark (out of 100)for the vertical axis.You should see: Both axes carry labels, so a reader knows what the numbers mean without asking.
Step 5: Leave edit mode
Click outside the chart to leave edit mode (in Calc, press Esc or click a cell).
You should see: The chart is part of the sheet. If you change a mark in the table, the chart updates.
Choose the data and a chart type, add labels and see how some choices mislead. This is a simulation that runs in the lesson; your own files are not touched.
Loading lab…
Choosing the right chart type
Pick the chart by the question it answers, not by what looks attractive. The table below covers the four types that cover nearly every beginner need.
| Chart | Best for | Example | Avoid when |
|---|---|---|---|
| Column (vertical bars) | Comparing separate things | Maths mark of each pupil | There are dozens of categories |
| Bar (horizontal bars) | Comparing things with long names | Average mark by subject name | The categories are in time order |
| Line | Change over time | A pupil's average each term | The points are not in order, or have no time link |
| Pie | Parts of one whole that add up to 100% | How a monthly budget is divided into five parts | More than about five slices, or the parts do not form a whole |
- ColumnCompare separate things
- BarCompare long names
- LineChange over time
- PieParts of one whole
Read this figure as text
Four cards naming column, bar, line and pie charts with the question each one answers.
Four cards. Column chart: compare separate things, such as the Maths mark of each pupil. Bar chart: compare things with long names, laid out horizontally so the names are easy to read. Line chart: show change over time, such as a pupil's average each term. Pie chart: show parts of one whole, such as how a budget is split, and use it only for a few slices that add up to 100 per cent.
Making charts honest
A chart can be accurate in every number and still mislead. The most common tricks are easy to spot once you know them. The first is a cut-off axis: a column chart's vertical axis should start at zero because the height of a column stands for its size. If the axis starts at 60, a pupil with 70 looks twice as good as one with 65, though the real gap is small. The second is a 3D effect, which makes front columns look bigger than the same value behind them. The third is a pie chart with too many slices, or slices that do not add up to a whole. The fourth is missing labels or units: a reader cannot tell whether the numbers are marks, shillings or kilograms.
- Class A: 65
- Class B: 70
- Columns almost the same height
Honest: a small gap looks small
- Class A: 65 (tiny column)
- Class B: 70 (twice as tall)
- Same numbers, different story
Misleading: a small gap looks huge
Read this figure as text
A comparison of an honest chart axis that starts at zero and a misleading one that starts at sixty.
Two columns compare the same data: Class A average 65 and Class B average 70. The left column, Axis starts at zero, shows Class A at 65 and Class B at 70, with columns of nearly the same height, and the verdict is honest: a small difference looks small. The right column, Axis starts at 60, shows the same numbers but Class B's column is twice as tall as Class A's, because only the part from 60 upwards is drawn. Its verdict is misleading: a small gap looks huge. The advice is to start column and bar chart axes at zero, label the axes and state the units.
- Give it a title that says what the chart shows, for example
Maths marks, Class 5B. - Label the axes and include units such as
Mark (out of 100). - Start bars and columns at zero. A line chart may start higher, but say so.
- Keep it plain. Avoid 3D effects, heavy backgrounds and rainbow colours with no meaning.
- Use a legend only when needed, such as when several series share a chart. Place labels directly on the chart when you can.
- Do not rely on colour alone. Some readers cannot tell certain colours apart, and the chart may be printed in black and white.
Keeping spreadsheets correct
A spreadsheet can look professional and be wrong. Studies and news stories have shown many costly spreadsheet mistakes, often a wrong range, a number stored as text or a formula typed over with a fixed number. Build the habit of checking.
- Show the formulas. In Calc choose View, then Show Formula (the Ctrl key with the backtick key, which sits at the top left of most keyboards). In Excel use Formulas, then Show Formulas. Every cell shows its formula, so you can scan a column for one that is different from the rest.
- Estimate. Check totals against a rough sum in your head.
- Test with simple numbers. Replace a few values with 1, 2 and 3 and see if the answers are what you expect.
- Look for typed-over formulas. A number in the middle of a column of formulas is suspicious.
- Keep inputs separate. Put rates and settings in labelled cells (using absolute references) rather than buried inside formulas.
- Ask someone else to look. A fresh pair of eyes finds mistakes that you can no longer see.
Backups, privacy and risky files
A marksheet, a household budget or a member list may be hard to rebuild. Keep a copy somewhere else, following the 3-2-1 rule from the backup lessons: three copies, on two kinds of storage, with one away from home. Save versions with dates in the file name, such as marksheet-2026-03-14.ods, before major changes. Sorting, deleting rows and pasting over cells are all things that are much easier to recover from when you have an earlier copy.
- BuildData and formulas
- CheckShow formulas, estimate
- Save a versionDate in the name
- Back upSecond place
- Share carefullyOnly what is needed
Read this figure as text
A cycle of five steps: build, check, save a version, back up and share carefully.
A cycle of five steps that repeats whenever you change an important spreadsheet. Step 1, Build: enter data and formulas. Step 2, Check: show formulas, compare with an estimate. Step 3, Save a version: add the date to the file name. Step 4, Back up: copy to a second place, such as an external drive or cloud storage. Step 5, Share carefully: send only what is needed, and prefer a PDF or a copy with private columns removed. Then return to Build for the next change.
When you share a spreadsheet, remember that it often holds more than you can see. Hidden rows, hidden columns and extra sheets are still in the file, and so is the data behind a chart. If a marksheet holds pupils' names, phone numbers or marks, send only what the person needs. A safer way is to create a copy with the private columns deleted, or to export just the part you want as a PDF (File, then Export as PDF in Calc, or File, then Save As, PDF in Excel). Do not email personal data to the wrong address; check the recipient before pressing Send.
Common mistakes
✖ Instead of: Choosing a chart because it looks impressive, such as a 3D pie with ten slices.
✔ Do this: Choose by question. Use a column, bar or line chart and keep it plain.
✖ Instead of: Leaving out the title, axis labels and units.
✔ Do this: Say what the chart shows and what the numbers measure.
✖ Instead of: Hiding a column and thinking the data is gone.
✔ Do this: Hidden data is still in the file. Delete it from the copy you share, or share a PDF.
✖ Instead of: Having only one copy of an important spreadsheet.
✔ Do this: Keep dated versions and a second copy in another place.
If something goes wrong
- The chart is missing the pupils' names, or shows 1, 2, 3 along the bottom.
- The names were not part of the selected range, or the first column was treated as data. Select the names and numbers together with their headings and make the chart again, or edit the data range.
- The chart does not update when I change a number.
- Check that the chart uses the cells you changed, and that automatic calculation is on. Also check that the number is not stored as text (left-aligned).
- The pie chart is unreadable.
- It has too many slices or tiny ones. Switch to a bar chart, or group small items into one called Other.
- The chart seems to have disappeared.
- It may be on another sheet or behind other cells. Check the sheet tabs, and use Ctrl+Z if you pressed Delete while it was selected.
Key terms
- chart
- A chart is a picture of numbers, such as columns, bars, lines or slices, that makes patterns easy to see.
- macro
- A macro is a small program stored inside a document or spreadsheet that can run automatically.
- Backup
- A separate copy of your files kept so you can get them back if the originals are lost or damaged.
- Formula
- An instruction in a spreadsheet cell that calculates a value from other cells.
Practise it for real
Reading is not the same as being able to do it. Complete these on your own computer, then tick only what you have actually done. These ticks are your own honest record: nothing checks them for you.
Chart the marksheet
Type the marksheet from this lesson, or one of your own with five fictional pupils and three subjects. Add an Average column using AVERAGE and fill it down. Make a column chart of one subject and a second chart of the averages. Give each a title and axis titles. Then change Kamau's Maths mark and check that the chart and average update. Show the formulas with Ctrl and the backtick key and check that every Average is a formula, then switch back. Save the file with today's date in its name, and copy it to a second place.
Challenge: fix a misleading chart
Make a column chart of two values, 65 and 70. Then change the axis so that it starts at 60 and note how the picture changes. Write two sentences explaining why the second chart misleads, then change it back to zero and add a title and axis labels. Finally, make a pie chart of your five budget items and decide whether it is easier to read than a bar chart.
Knowledge check
7 questions. You need 80% to pass. Mistakes here are part of learning, and you can retry as often as you like.
Summary
- Choose a chart by the question: columns and bars compare, lines show change over time and pies show parts of a whole.
- Give every chart a title, axis labels and units, and start column and bar axes at zero.
- Show formulas and compare totals with an estimate to catch mistakes.
- Keep dated versions and a second copy of important files.
- Hidden data is still in the file. Never enable macros in unexpected files.
Checklist: can you do these?
Marking a lesson as read does not prove you can do it. Skills count as demonstrated when you pass the quiz (not yet) and complete the practical tasks (not yet).
Related lessons
References and further reading
Official sources for details that can change between versions.
- LibreOffice Calc Guide (The Document Foundation)
- Create a chart in Excel (Microsoft)
- LibreOffice Help (The Document Foundation)