Automating Excel: macros, scripts and safe habits
Move from clicking to automation: templates, recorded macros, scripts and Python, with safety rules and a real report-building example.
In this lesson you will
- Choose the right level of automation
- Record and read a simple macro
- Follow macro safety rules
- Verify what automation produces
About 17 minutes
Let the computer do the clicking
Latoya prepares the same weekly report every Friday: paste the new numbers, make the header bold, fit the columns, add the totals, save a copy with the date. It takes twenty minutes and it is boring. Boring and repeated is exactly what a computer is good at. In this lesson you will meet the ways to automate Excel work, from the simple (templates and Power Query) to the programmable (macros, scripts and Python), and learn how to do it safely.
A ladder of automation
| Level | Tool | Use it when |
|---|---|---|
| 1 | Templates and Tables | The layout repeats; formulas fill themselves |
| 2 | Power Query and PivotTable refresh | The data changes; the steps stay the same |
| 3 | Recorded macros (VBA) | The clicks repeat on the desktop app |
| 4 | Office Scripts or Power Automate | You work online or need scheduled flows |
| 5 | Python or other code | You need heavy processing or many files |
Move up the ladder only when the step below is not enough. Most problems are solved at levels 1 and 2.
Recording a macro
- Turn on the Developer tab (File, Options, Customize Ribbon).
- Click Record Macro, give it a name with no spaces and choose where to store it.
- Do the clicks once: make the header bold, fit the columns, set a number format.
- Click Stop Recording.
- Run it later with Macros or a button you attach to it.
Excel writes the clicks as VBA, a programming language. The code below is the kind of thing a recording produces after a little tidying. It is shown as a pattern and was not run, because the sandbox has no Excel:
Sub FormatReport()
' Recorded, then tidied: make the header bold and fit the columns
Range("A1:B1").Font.Bold = True
Columns("A:B").AutoFit
Range("B2:B10").NumberFormat = "#,##0"
End Sub
Workbooks that contain macros must be saved as .xlsm. Recorded macros repeat exactly what you did, including accidents, so test on a copy and use relative references (the Use Relative References button) if the data moves.
Safety first
- Macros can carry viruses. Never enable macros in a file from an unknown sender. Excel’s yellow security bar asks; the safe answer is no unless you trust the source and expected the file.
- Keep your own macros in a trusted folder or a signed workbook, as your workplace advises.
- Back up before running a macro that changes or deletes data. Macros cannot be undone with Ctrl+Z.
- Do not store passwords or private data inside macro code.
Automating with code outside Excel
Python can read and write Excel files without opening Excel, which is useful for producing the same report from many files. The program below reads a small sales text, totals by region, writes a report workbook with a total formula, then reloads the file and checks what it saved. The data is invented. The output is real:
import csv, io, openpyxl
raw = """region,product,units,price
North,Pen,10,20
South,Notebook,5,60
North,Notebook,8,60
East,Pen,20,20
South,Pen,15,20
"""
rows = list(csv.DictReader(io.StringIO(raw)))
wb = openpyxl.Workbook(); ws = wb.active; ws.title = "Report"
ws.append(["Region", "Revenue"])
totals = {}
for r in rows:
totals[r["region"]] = totals.get(r["region"], 0) + int(r["units"]) * int(r["price"])
for region in sorted(totals):
ws.append([region, totals[region]])
ws.append(["Total", "=SUM(B2:B4)"])
ws.column_dimensions["A"].width = 16
wb.save("monthly_report.xlsx")
check = openpyxl.load_workbook("monthly_report.xlsx")["Report"]
print("rows written:", check.max_row)
for row in check.iter_rows(min_row=2, values_only=True):
print(row)
print("sum of regions:", sum(totals.values()))
rows written: 5
('East', 400)
('North', 680)
('South', 600)
('Total', '=SUM(B2:B4)')
sum of regions: 1680
The check at the end is the important habit: after any automation, read back what it produced and compare it with an independent total, as the last line does. Automation makes mistakes fast and at scale, so verification matters even more.
Online options
Excel for the web cannot run VBA, but it has Office Scripts on some plans, and Microsoft Power Automate can run flows such as ‘when a file arrives, refresh and email a report’. Availability depends on your plan and your organisation’s settings, so check with your administrator. Google Sheets has its own scripting called Apps Script.
Before you automate
- Do the job by hand a few times so you understand every step.
- Write the steps down, including what could go wrong.
- Automate one step at a time and test each.
- Add checks: row counts, totals, empty-file detection.
- Document what the automation does, where it reads from and who maintains it.
When not to automate
If a job happens twice a year or changes every time, the time to automate may exceed the time saved. If the process is unclear or politically contested, fix the process first. And if nobody will be around to maintain the macro when Excel updates or the layout changes, a simple documented manual process may serve better.
Practice
Choose a five-minute formatting routine you repeat. Record it as a macro on a copy of the file, read the code, and run it on a second file to see where it fails. Then list three checks you would add to make it safe, such as confirming that the header is in row 1 and that the total matches the sum of the data.
Common failures are a hard-coded range (A1:B10 when next week’s data has fifteen rows) and a sheet name that changed. A better macro finds the last row with the data itself and refuses to run if the header is missing.
Enabling macros in an unexpected file. If you were not told to expect a macro, do not enable it. Ask the sender through another channel first.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What file type must hold a workbook with macros?
- .csv
- .txt
- .xlsm
Normal .xlsx files cannot store VBA.
What should you do with macros from an unknown sender?
- Do not enable them
- Enable them quickly
- Forward them
- Rename them
Macros can carry malware.
Why back up before running a macro?
- Macros are slow
- Excel requires it
- Macro changes cannot be undone with Ctrl+Z
- It changes colours
Test on a copy first.
What is a good check after automation?
- Compare totals with an independent calculation
- Hide the sheet
- Delete the source
- Skip testing
Automation can make mistakes at scale.
When is automation not worth it?
- A weekly routine
- A rare job that changes every time
- A daily clean-up
- A repeated format
Save the effort for repeated, stable jobs.
Summary
- Use the simplest tool that works: templates, Power Query, then macros or code.
- Never enable macros from unknown senders.
- Test on copies; macros cannot be undone.
- Always check automated output against an independent total.
Questions
Do I need to learn VBA?
Not to be good at Excel. Learn it if you do the same desktop tasks daily. Power Query and formulas cover most needs.
Can Excel for the web run macros?
Not VBA. Some plans offer Office Scripts instead, depending on your organisation's settings.
Why does my recorded macro break on new data?
It recorded fixed cell addresses. Rewrite it to find the last row, or record with relative references.
