By the end of this lesson you will be able to
- Recognise a repetitive task that is worth automating and one that is not.
- Choose between a shortcut, a spreadsheet feature and a script for a given task.
- Use fill down to copy a spreadsheet formula instead of retyping it.
- Explain why a copy and a preview must come before any bulk change.
- Follow a five-step routine for automating a task safely.
Computers are good at doing the same small thing a thousand times without getting bored. People are not. If you find yourself renaming forty files by hand, retyping the same formula in every row or sending the same message over and over, the computer should be doing that work. The danger is that a computer also repeats mistakes a thousand times, very fast. This lesson shows how to gain the saving and avoid the damage.
Spotting work worth automating
Automation means setting things up so that the computer carries out a task for you with little or no manual effort. You do not need programming to begin. Start by noticing. Over a week, keep a note whenever you catch yourself doing the same steps again. Good candidates share three features: you do them often, the steps are always the same, and you can tell quickly whether the result is right.
A task done once a year, or one that needs judgement each time, is usually not worth automating. Setting up an automation takes time, so the saving must be real. A rough test: if it takes ten minutes by hand, you do it weekly, and setting up the automation takes an hour, you break even after six weeks.
- Do you repeat this task often?
- YesAre the steps always the same?
- YesCan you easily check the result?
- YesDo thisStart with a practice copy.
- NoDo thisAn unchecked automation can fail silently.
- NoDo thisIt needs your judgement each time.
- YesCan you easily check the result?
- NoDo thisSetting up automation would cost more than it saves.
- YesAre the steps always the same?
Read this figure as text
A decision tree asking whether a task is repeated, always the same and easy to check.
Start: do you repeat this task often? If no, do it by hand. If yes, ask: are the steps always the same? If no, do it by hand, because it needs judgement. If yes, ask: can you easily check the result? If no, be careful and keep it manual until you can check. If yes, it is a good candidate for automation, starting with a practice copy.
A ladder of tools, from simple to powerful
Always use the simplest tool that works. Each step up the ladder saves more time but needs more care.
| Tool | Good for | Example | Risk level |
|---|---|---|---|
| Keyboard shortcuts | Repeating small actions faster | Ctrl+C, Ctrl+V, Ctrl+Z; Ctrl+F to find | Very low |
| Built-in features | Common jobs the program already knows | Spell check, sort, filter, find and replace, templates | Low to medium |
| Spreadsheet formulas and fill down | Calculating many rows the same way | One total formula copied down 200 rows | Low |
| Recorded macros | Repeating a long series of clicks in one program | Format a weekly report the same way each time | Medium: macros run code, so only use ones you made or trust |
| Simple scripts | Working on many files or running commands in order | Add a date to the name of every file in a folder | Higher: a script can change many files at once |
Spreadsheet automation: write the formula once
The easiest automation is already on your computer. In a Spreadsheet, you type a Formula once, in the first row, and copy it down. The program adjusts the cell references for each row, so every row calculates its own answer. This is called fill down.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Total |
| 2 | Pencils | 50 | 10 | 500 |
| 3 | Notebooks | 120 | 5 | 600 |
| 4 | Rulers | 80 | 4 | 320 |
| 5 | Sum | 1420 |
- Marker 1: Type the formula once, then fill down
- Marker 2: =SUM(D2:D4) adds all totals
Read this figure as text
A simulated spreadsheet with items, prices, quantities and a Total column filled by one formula.
A small spreadsheet. Row 1 has the headings Item, Price, Qty and Total. Row 2: Pencils, 50, 10, total 500. Row 3: Notebooks, 120, 5, total 600. Row 4: Rulers, 80, 4, total 320. Row 5 has the label Sum in column C and 1420 in D5. Cell D2 is selected and the formula bar shows =B2*C2. Marker 1 points to D2, where the formula is typed once and copied down. Marker 2 points to D5, which holds =SUM(D2:D4).
Try fill down in LibreOffice Calc or Excel
Step 1: Type the data
Enter the headings and the three rows of Item, Price and Qty from the picture. Leave the Total column empty. All names and numbers are made up.
You should see: You have three rows with columns A to C filled.
Step 2: Type one formula
Click cell D2 and type
=B2*C2, then press Enter.You should see: D2 shows 500.
Step 3: Select the range
Click D2, then hold Shift and click D4, so D2 to D4 is selected.
You should see: Three cells in column D are highlighted.
Step 4: Fill down
Press
Ctrl+D. (This shortcut works in both LibreOffice Calc and Excel.) Alternatively drag the small square at the bottom-right corner of D2 down to D4.You should see: D3 shows 600 and D4 shows 320. Click D3: its formula is
=B3*C3.Step 5: Add the sum
In D5 type
=SUM(D2:D4)and press Enter.You should see: D5 shows 1420.
Doing a task to many files: batch processing
Doing the same action to many items in one go is called batch processing. Examples are renaming a hundred photos, converting many documents to PDF, or moving all files of one type into a folder. File managers, office programs and the command line all offer ways to do it. The principle is the same everywhere: you describe the rule once, and the computer applies it to every matching item.
The power is also the danger. A rule that is slightly wrong, such as a pattern that matches more files than you meant, will be applied to all of them. In a spreadsheet, clicking Replace All in Find and Replace can change every matching cell in the sheet, including ones you never looked at.
The safe automation routine
- Step 1: Do it by hand onceWrite the steps down
- Step 2: Work on a copyPractice folder
- Step 3: Preview the planDry run or small sample
- Step 4: Run on the copy
- Step 5: Check the resultOpen a few files
- Step 6: Run on the real filesWith a backup in place
Read this figure as text
A six-step flow from doing it by hand to checking the result and keeping a backup.
Six steps. 1: do the task by hand once and write down each step. 2: make a copy of the files, or a practice folder, and work only on the copy. 3: preview what will change, using a preview, dry run or a small sample. 4: run it on the copy. 5: check the result against what you expected, for example by opening three files. 6: only then run it on the real files, with a backup in place.
The key habit is the dry run: asking the tool to show what it would do without doing it. Many tools have a preview or a test mode. When yours does not, you can make one by starting with a sample of only two or three files. A good automation also tells you what it did, so that you can check afterwards.
- Slow and tiring
- Small slips here and there
- A mistake affects one item
Safe but costly in time.
- Fast and consistent
- Easy to repeat next week
- A wrong rule affects every item
Test on copies first.
Read this figure as text
A comparison of doing a task by hand and by automation, with benefits and risks.
Two columns. By hand: slow, tiring, small mistakes are possible in each item, but a mistake affects only one item and is easy to notice. Automated: fast, consistent, and repeatable, but a wrong rule affects every item at once and the damage may be large before anyone notices. The remedy is to test on copies and to preview first.
Common mistakes
✖ Instead of: Automating a task before understanding it, and before doing it by hand once.
✔ Do this: Do it by hand first and write down the steps. If you cannot describe the task, you cannot automate it safely.
✖ Instead of: Testing a new bulk rename on the real folder.
✔ Do this: Copy a few files to a practice folder and test there. Check the results before touching the real files.
✖ Instead of: Typing totals into cells by hand when a formula could do it.
✔ Do this: Type a formula once and fill down, so the totals update when the data changes.
If something goes wrong
- Fill down copied the same number in every row instead of calculating it.
- You probably typed a number instead of a formula, or used a fixed reference. Check that D2 starts with
=and look at the formula in D3. Use plain references like B2 and C2 for this exercise. - Ctrl+D does nothing.
- Make sure you selected the range starting with the cell that holds the formula, for example D2 to D4. In a browser-based spreadsheet the key may be taken by the browser; drag the fill handle instead.
- I changed things I did not mean to change.
- Press Ctrl+Z straight away, as many times as needed. If the file was saved, reopen the earlier copy or restore from a backup. Then use a narrower range next time.
Key terms
- Automation
- Setting things up so the computer does a repeated task for you with little or no manual effort.
- Batch processing
- Doing the same action to many items in one go, following one rule.
- Dry run
- Running a task in a way that only shows what it would do, without changing anything.
- macro
- A macro is a small program stored inside a document or spreadsheet that can run automatically.
- 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.
Keep a repetition diary
For three days, write down every task you notice yourself repeating on a computer or phone (for example, renaming downloaded files, typing the same greeting, copying numbers between documents). At the end, choose the two most frequent and write for each: how long it takes, how often, and which tool on the ladder might help.
Challenge: a price list that updates itself
In a spreadsheet, build a made-up price list for a small shop with at least six items, a price column and a quantity column. Without retyping the formula for each row, make a Total column and a grand total. Then change two prices and check that the totals update. Finally, write two sentences about what could go wrong if you had typed each total by hand.
Knowledge check
6 questions. You need 80% to pass. Mistakes here are part of learning, and you can retry as often as you like.
Summary
- Automate tasks that are frequent, identical each time and easy to check.
- Use the simplest tool on the ladder: shortcuts, built-in features, formulas, macros, then scripts.
- In a spreadsheet, type a formula once and fill it down instead of retyping.
- Bulk changes are fast and can be hard to undo.
- Work on a copy, preview with a dry run, check the result, and only then run it for real.
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)
- Fill data automatically in worksheet cells (Microsoft Support)