Excel from Basics to Advanced — Macros and Real-World Automation

What Is a Macro?

A macro is Excel’s built-in feature for automating repetitive work. Tasks you do every day — sorting data, applying formatting, generating reports — can be handled with a single button click.

Two ways to build a macro:

• Recording: Excel records your actions as you perform them → fast and easy
• VBA coding: Write code directly in Visual Basic for Applications → flexible and powerful

---

Recording a Macro

Starting the recorder

1. Developer tab
2. Record Macro (If you don’t see the Developer tab: File
3. Options
4. Customize Ribbon
5. check Developer)

Recording settings

Field	Description
Macro name	Letters and numbers only, no spaces (e.g. FormatReport)
Shortcut key	Assign a Ctrl+key combo (e.g. Ctrl+Shift+F)
Store macro in	This Workbook / Personal Macro Workbook
Description	A note on what the macro does

Things to watch for while recording

Once recording starts:

• Clicks, keystrokes, and menu selections are all recorded
• Mouse movement alone is not recorded
• Cell selection is recorded too (watch out for absolute references)
• → To record with relative references: Developer → Use Relative References

Running a macro

• Method 1: The shortcut key you assigned (e.g. Ctrl+Shift+F)
• Method 2: Developer → Macros → select from the list → Run
• Method 3: Assign the macro to a shape/button → click to run (Insert → Shapes → right-click → Assign Macro)

---

The VBA Editor

The environment for editing recorded macros or writing new code directly.

Alt+F11 → opens the VBA editor
or: Developer → Visual Basic

VBA editor layout

• Project pane (left): ThisWorkbook, Sheet1, Module1, …
• Code editor pane (right): write your code here

Adding a module: right-click the project pane → Insert → Module

---

VBA Basics

Sub procedure structure

Sub MacroName()
’ A single quote starts a comment
’ write your code here
End Sub

Declaring variables and types

Dim variableName As Type

Dim name As String       ' text
Dim age As Integer       ' whole number (-32768 to 32767)
Dim amount As Long       ' larger whole number
Dim ratio As Double      ' decimal
Dim isDone As Boolean    ' True/False

' Multiple variables at once:
Dim i As Integer, j As Integer, result As Double

Referencing cells

' A single cell:
Range("A1").Value = "Hello"
Cells(1, 1).Value = "Hello"    ' Cells(row, column)

' A range:
Range("A1:D10").ClearContents  ' clear contents
Range("B2:B100").Interior.Color = RGB(255, 255, 0)  ' yellow fill

' The active cell:
ActiveCell.Value = "current position"

' Finding the last used row:
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

If statements

If condition Then
    ' when true
ElseIf otherCondition Then
    ' when the other condition is true
Else
    ' otherwise
End If

' Example:
If Cells(i, 3).Value >= 80 Then
    Cells(i, 4).Value = "Pass"
Else
    Cells(i, 4).Value = "Fail"
End If

For loops

' A fixed number of times:
For i = 1 To 10
    Cells(i, 1).Value = i
Next i

' Up to the last row:
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
    ' process
Next i

' In reverse:
For i = lastRow To 2 Step -1
    If Cells(i, 1).Value = "" Then Rows(i).Delete
Next i

Do While loops

Dim i As Integer
i = 1
Do While Cells(i, 1).Value <> ""
    ' process
    i = i + 1
Loop

---

5 Practical Automation Scripts

1. Auto-apply report formatting

Sub ApplyReportFormat()
    ' Header row formatting
    With Range("A1:F1")
        .Interior.Color = RGB(0, 112, 192)  ' blue fill
        .Font.Color = RGB(255, 255, 255)     ' white text
        .Font.Bold = True
        .Font.Size = 12
    End With
    
    ' Border the data rows
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1:F" & lastRow).Borders.LineStyle = xlContinuous
    
    MsgBox "Formatting applied!"
End Sub

2. Auto-sort data and clear filters

Sub SortData()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ' Clear any existing filter
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    ' Sort ascending by column A
    ws.UsedRange.Sort Key1:=ws.Range("A2"), _
        Order1:=xlAscending, Header:=xlYes
    
    MsgBox "Sort complete"
End Sub

3. Auto-delete blank rows

Sub DeleteBlankRows()
    Dim lastRow As Long
    Dim i As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Loop in reverse to avoid row-number shifting on delete
    For i = lastRow To 2 Step -1
        If Cells(i, 1).Value = "" Then
            Rows(i).Delete
        End If
    Next i
    
    MsgBox "Blank rows deleted"
End Sub

4. Auto-generate monthly sheets

Sub CreateMonthlySheets()
    Dim m As Integer
    Dim sheetName As String
    
    For m = 1 To 12
        sheetName = "Month " & m
        
        ' Skip if it already exists
        On Error Resume Next
        If Worksheets(sheetName) Is Nothing Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheetName
        End If
        On Error GoTo 0
    Next m
    
    MsgBox "12 monthly sheets created"
End Sub

5. Auto-summarize results

Sub GenerateMonthlySummary()
    Dim ws As Worksheet
    Dim summarySheet As Worksheet
    
    ' Reset the summary sheet
    Set summarySheet = Worksheets("Summary")
    summarySheet.Cells.ClearContents
    
    ' Write the header
    summarySheet.Range("A1").Value = "Department"
    summarySheet.Range("B1").Value = "Total Sales"
    summarySheet.Range("C1").Value = "Count"
    
    ' Aggregate data from each sheet
    Dim row As Integer
    row = 2
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            summarySheet.Cells(row, 1).Value = ws.Name
            summarySheet.Cells(row, 2).Value = Application.WorksheetFunction.Sum(ws.Range("D:D"))
            summarySheet.Cells(row, 3).Value = ws.Cells(Rows.Count, 1).End(xlUp).Row - 1
            row = row + 1
        End If
    Next ws
    
    MsgBox "Summary complete"
End Sub

---

Macro Security Settings

Excel shows a security warning when you open a file containing macros.

File → Options → Trust Center → Trust Center Settings → Macro Settings:

• Disable all macros with notification: recommended
• Disable all macros except digitally signed macros: for corporate environments
• Enable all macros: only when you trust the source

Saving a macro-enabled file:

A regular .xlsx file cannot store macros
→ File → Save As → Excel Macro-Enabled Workbook (.xlsm)

---

Real-World Template Examples

Budget tracker automation

Macro workflow:

1. Enter date, category, and amount into an input form
2. Click a button → automatically appends the entry to the data sheet
3. Auto-aggregates into the monthly tab
4. Annual spending chart updates automatically

Inventory management sheet

Automated elements:

• Highlight negative stock quantities in red
• Auto-list items at or below the reorder point
• Auto-calculate inventory from incoming/outgoing entries
• Auto-generate a month-end inventory report

Automatic business report generation

With one button click:

1. Pull this month’s data from the source sheet
2. Auto-generate tables and charts on the report sheet
3. Auto-calculate the month-over-month change
4. Save as PDF automatically (optional)

---

Wrapping Up the Excel Series — A Learning Roadmap

Stage	Goal	Key Features
Ch1 Basics	Getting started with Excel	Interface, SUM/IF/ROUND
Ch2 Data Management	Organizing data	Sorting, filtering, conditional formatting
Ch3 Advanced Functions	Connecting data	VLOOKUP, INDEX/MATCH, SUMIFS
Ch4 PivotTables & Charts	Visualizing data	PivotTables, slicers, charts
Ch5 Macros	Automating work	VBA, automating repetitive tasks

Where to go next:

• Power Query: automatically connect and transform external data
• Power Pivot: analyze large volumes of data
• Power BI: take Excel data visualization further

---

Practice Quiz — 5 Questions

Q1. What happens when you save a macro to the “Personal Macro Workbook”?

• ① It’s usable only in the current file
• ② It’s loaded automatically every time Excel opens, so it’s usable in any file
• ③ It’s auto-saved to OneDrive
• ④ It’s shared with other people

Answer: ② (the Personal Macro Workbook is a hidden file that’s always loaded)

---

Q2. What does Cells(Rows.Count, 1).End(xlUp).Row do in VBA?

• ① Returns the value of cell A1
• ② Finds the row number of the last used cell in column A
• ③ Returns the total number of rows on the sheet
• ④ Counts how many data entries are in column A

Answer: ② (it starts from the bottom and moves up until it hits the first cell with data, returning that row number)

---

Q3. Why does a loop that deletes blank rows need to run in reverse (from the last row down to row 2)?

• ① It runs faster that way
• ② Deleting a row shifts every row after it up by one, so going in reverse means rows you’ve already processed are unaffected
• ③ Excel doesn’t allow deleting rows in forward order
• ④ For Each only supports reverse iteration

Answer: ② (the core reason for reverse order — avoiding the row-shift problem when deleting)

---

Q4. What file format must you use to save a file that contains macros?

• ① .xlsx (Excel Workbook)
• ② .xlsm (Excel Macro-Enabled Workbook)
• ③ .xls (Excel 97-2003)
• ④ .csv (comma-separated values)

Answer: ② (.xlsx cannot store macros — .xlsm is required)

---

Q5. How do you skip over a line of VBA code that might throw an error?

• ① Skip Error Next
• ② On Error Resume Next
• ③ Try ... Catch
• ④ If Error Then Skip

Answer: ② (On Error Resume Next moves to the next line when an error occurs)