Automation & Macros

An accountant at Deloitte automated her entire Monday morning in 2019

Every Monday, she received a CSV export from the billing system. She would open it, delete three header rows the system added, reformat the date column, add a currency format to the amount column, sort by client name, insert a subtotal row for each client, and save it as a formatted Excel file. The process took 45 minutes.

One day, she recorded a macro while doing the entire process. The next Monday, she opened the CSV, pressed Ctrl+Shift+M, and the macro replayed all 23 steps in under 4 seconds. She estimated that macro saved her 35 hours per year — nearly a full work week — on a single repetitive task.

Automation is not about replacing people. It is about freeing people from the tasks that do not require human judgment, so they can spend that time on work that does.

What is a macro?

A macro is a recorded sequence of actions that Excel can replay on command. Think of it like a recipe card: you write down the steps once, and anyone can follow them perfectly every time.

Behind the scenes, macros are written in VBA (Visual Basic for Applications) — a programming language built into Excel. When you "record" a macro, Excel watches what you do and writes VBA code that reproduces those exact steps. You do not need to know VBA to record a macro. You can learn VBA to write more powerful macros later.

Recording your first macro

There Are No Dumb Questions

"Is there a macro recorder in Google Sheets?"

Google Sheets has a macro recorder (Extensions → Macros → Record macro), but it generates Google Apps Script (JavaScript-based) instead of VBA. The concept is identical — record actions, replay them. The language is different. If you learn the principles in Excel, they transfer directly.

"Will my macros work on someone else's computer?"

Only if the file is saved as .xlsm (macro-enabled workbook) instead of .xlsx. Regular .xlsx files strip out all macros when saved. Also, the recipient's Excel must allow macros to run — many corporate IT departments block macros by default for security reasons (macros can be used to deliver malware).

Peeking under the hood: basic VBA

When you record a macro, Excel writes VBA code. You can view it: Developer → Visual Basic (or Alt+F11). Here is what a simple formatting macro looks like:

Sub FormatReport()
    ' Select the data range
    Range("A1:E100").Select

    ' Bold the header row
    Rows("1:1").Font.Bold = True

    ' Format column D as currency
    Columns("D:D").NumberFormat = "$#,##0.00"

    ' Sort by column A
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("A2"), Order:=xlAscending
    With ActiveSheet.Sort
        .SetRange Range("A1:E100")
        .Header = xlYes
        .Apply
    End With
End Sub

You do not need to memorize this. But understanding the structure helps you edit recorded macros:

Custom VBA functions — when Excel's built-in functions are not enough

Sometimes you need a calculation that does not exist in Excel's 500+ functions. VBA lets you create your own.

Example: A function that extracts the domain from an email address:

Function ExtractDomain(email As String) As String
    ExtractDomain = Mid(email, InStr(email, "@") + 1)
End Function

Now in any cell, you can type =ExtractDomain(A2) and it pulls "gmail.com" from "[email protected]". This function works like any built-in Excel function — you can copy it down an entire column.

Common custom function ideas:

• Calculate business days between two dates (excluding holidays specific to your company)
• Convert between unit systems your industry uses
• Parse complex text patterns from imported data
• Apply industry-specific scoring formulas

Macro security — why IT departments are scared of macros

Macros are powerful — which is exactly why they are dangerous. A VBA macro has full access to your file system, can download files from the internet, and can execute system commands. This is why macro-based malware has been one of the top attack vectors for decades.

There Are No Dumb Questions

"Should I learn VBA in 2025, or is it outdated?"

VBA is 30+ years old and Microsoft is investing in newer technologies (Power Automate, Office Scripts, Python in Excel). But VBA is far from dead — it is embedded in billions of existing Excel files across every industry. Learning basic VBA is still practical for editing recorded macros, building custom functions, and maintaining legacy systems. Think of it like knowing how to drive a manual car — not always necessary, but useful when you encounter one.

"What is Power Query and how is it different from macros?"

Power Query is a data transformation tool built into Excel (Data → Get Data). It connects to external sources (databases, CSVs, web pages), cleans and reshapes data, and loads it into your spreadsheet. Unlike macros, Power Query uses a visual interface — no coding required. It is better than macros for data import and transformation tasks. Macros are better for formatting, multi-step workflows, and actions that involve multiple sheets or user interaction.

<classifychallenge xp="25" title="Which Automation Tool?" items={["Calculate 5% commission on deals over $10K, 3% otherwise","Every Friday, copy this weeks data to an Archive sheet and clear the Raw sheet","Import a CSV, remove system headers, unpivot monthly columns into rows","Create a custom shipping cost function based on weight, distance, and a proprietary rate table","Format all negative numbers as red, bold headers, add borders to all cells","Pull daily sales data from 3 regional CSVs, merge them, clean duplicates, and load into one summary table"]} options={["Formula (IF/IFS)","Recorded macro","Custom VBA function","Power Query"]} hint="Formulas are for calculations in cells. Recorded macros replay formatting and processing actions. Custom VBA functions create new calculation capabilities. Power Query handles data import, transformation, and merging from external sources.">

Back to the Deloitte accountant

Remember the accountant who turned 45 minutes of Monday drudgery into 4 seconds? She did not learn VBA programming. She did not hire a developer. She clicked Record, did the work one last time, and clicked Stop. That single recorded macro saved her 35 hours per year — nearly a full work week she reinvested in analysis that actually required her judgment.

The formulas you learned in Formulas & Functions handle calculations. The data analysis tools from the previous modules handle sorting, filtering, and summarizing. Macros handle the actions — the formatting, processing, and multi-step workflows that no formula can do. And for data import and transformation, Power Query handles what macros do clumsily.

Next up: Macros automate actions. But what about the formulas themselves — can they get more powerful? In the next module, you will learn dynamic arrays (FILTER, SORT, UNIQUE), LAMBDA functions, IFS, advanced text and date functions, and error handling — the formulas that separate competent Excel users from the ones everyone calls for help.

Key takeaways

• Macros record sequences of actions and replay them on command — perfect for repetitive formatting, sorting, and processing tasks
• Recording a macro requires zero coding knowledge — just click Record, perform the steps, and Stop
• VBA is the programming language behind macros — learning the basics lets you edit recorded macros and add logic (loops, conditions)
• Custom VBA functions create new formulas that work like built-in functions — useful when Excel's 500+ functions are not enough
• Power Query is superior to macros for data import and transformation tasks — use the visual interface, no code needed
• Macro security is critical — never enable macros from unknown sources, save macro files as .xlsm, and understand your organization's security policy
• The automation mindset: if you do the same sequence of steps more than three times, automate it