AdvancedLesson 27 of 305 min read

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

  1. Turn on the Developer tab (File, Options, Customize Ribbon).
  2. Click Record Macro, give it a name with no spaces and choose where to store it.
  3. Do the clicks once: make the header bold, fit the columns, set a number format.
  4. Click Stop Recording.
  5. 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

  1. Do the job by hand a few times so you understand every step.
  2. Write the steps down, including what could go wrong.
  3. Automate one step at a time and test each.
  4. Add checks: row counts, totals, empty-file detection.
  5. 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.

Practice answer

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.

Common mistake

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?

  1. .csv
  2. .txt
  3. .xlsm
  4. .pdf

Normal .xlsx files cannot store VBA.

What should you do with macros from an unknown sender?

  1. Do not enable them
  2. Enable them quickly
  3. Forward them
  4. Rename them

Macros can carry malware.

Why back up before running a macro?

  1. Macros are slow
  2. Excel requires it
  3. Macro changes cannot be undone with Ctrl+Z
  4. It changes colours

Test on a copy first.

What is a good check after automation?

  1. Compare totals with an independent calculation
  2. Hide the sheet
  3. Delete the source
  4. Skip testing

Automation can make mistakes at scale.

When is automation not worth it?

  1. A weekly routine
  2. A rare job that changes every time
  3. A daily clean-up
  4. 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.

Next lesson

Next: using Excel in professional life.

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.

Progress is saved in this browser.