Learning Unit 3
Macros
What Are Macros?
Definition: Macros are programs that store a series of commands
Purpose: automate repetitive tasks and extend Excel’s functionality
Written in VBA (Visual Basic for Applications)
Can be recorded (no coding required) or written manually
File Extensions
Normal Excel files: .xlsx (default, modern)
Older versions: .xls (less compatible)
Macro-enabled files: .xlsm (only these can run macros)
Reason: To reduce risk of macro viruses spreading via Office files
Excel disables macros by default until user enables them
Important: If saving macros, always save as .xlsm
Developer Tools
Macros available under View → Macros
Can activate the Developer toolbar for advanced options
Use Excel’s search box (“customise”) to enable Developer tab
Recording a Macro
Access via toolbar or status bar → Record Macro
Options:
Name (make descriptive)
Save in This Workbook, New Workbook, or Personal Macro Workbook
Example: MovementDemo macro → navigates cells
Recorded actions generate VBA code automatically
Indicator on status bar showing if it is recording macro or not
Code basics:
Stored between Sub and End Sub, macro name after Sub
' indicates comments (ignored by Excel)
Running a Macro
Access via View → Macros → Run
Example: Running MovementDemo selects cell C2
Can also select ranges with commands like Range("C2:D3").Select
Relative References
Default macros use absolute references (always to a fixed cell, e.g., C2)
Turn on Use Relative References → actions depend on starting position
Example: Macro moves “two right, one down” from wherever user starts
Macro Actions
Example task: Automating weekly data formatting
, Macro recorded to:
1. Add cell names (Total, Percentage, Price Level)
2. Freeze top row
3. Format row 1 (bold, light blue fill)
4. Auto-resize columns
5. Insert formula =D2*E2 and autofill
6. Add total with SUM
Test macros carefully:
Sometimes autofill/total placement fails
Lesson: test macros and adjust process if needed
Data Manipulation
Working with a Table
Tables group related data in Excel.
Convert range → Format as Table (Home toolbar)
Features:
Automatic headers (formatted row)
Alternating row colours (depending on style)
Filters enabled by default
Formulas in tables:
Use column names instead of cell addresses (e.g., [Quantity]*[Unit Price])
Auto-expands new columns
Formula auto-fills for all rows
Bottom right hand corner of table has sizing handle
Total Row:
Add via Table Style → Total Row
Dropdown lets you select functions (Sum, Average, Count, etc.)
Importing Data
CSV Files
CSV (Comma Separated Values): plain text, values separated by commas
Widely supported, readable in text editors
Typically includes header row
Text to Columns
Copy CSV data into Excel → use Data → Text to Columns
Select Delimited → Comma
Macros
What Are Macros?
Definition: Macros are programs that store a series of commands
Purpose: automate repetitive tasks and extend Excel’s functionality
Written in VBA (Visual Basic for Applications)
Can be recorded (no coding required) or written manually
File Extensions
Normal Excel files: .xlsx (default, modern)
Older versions: .xls (less compatible)
Macro-enabled files: .xlsm (only these can run macros)
Reason: To reduce risk of macro viruses spreading via Office files
Excel disables macros by default until user enables them
Important: If saving macros, always save as .xlsm
Developer Tools
Macros available under View → Macros
Can activate the Developer toolbar for advanced options
Use Excel’s search box (“customise”) to enable Developer tab
Recording a Macro
Access via toolbar or status bar → Record Macro
Options:
Name (make descriptive)
Save in This Workbook, New Workbook, or Personal Macro Workbook
Example: MovementDemo macro → navigates cells
Recorded actions generate VBA code automatically
Indicator on status bar showing if it is recording macro or not
Code basics:
Stored between Sub and End Sub, macro name after Sub
' indicates comments (ignored by Excel)
Running a Macro
Access via View → Macros → Run
Example: Running MovementDemo selects cell C2
Can also select ranges with commands like Range("C2:D3").Select
Relative References
Default macros use absolute references (always to a fixed cell, e.g., C2)
Turn on Use Relative References → actions depend on starting position
Example: Macro moves “two right, one down” from wherever user starts
Macro Actions
Example task: Automating weekly data formatting
, Macro recorded to:
1. Add cell names (Total, Percentage, Price Level)
2. Freeze top row
3. Format row 1 (bold, light blue fill)
4. Auto-resize columns
5. Insert formula =D2*E2 and autofill
6. Add total with SUM
Test macros carefully:
Sometimes autofill/total placement fails
Lesson: test macros and adjust process if needed
Data Manipulation
Working with a Table
Tables group related data in Excel.
Convert range → Format as Table (Home toolbar)
Features:
Automatic headers (formatted row)
Alternating row colours (depending on style)
Filters enabled by default
Formulas in tables:
Use column names instead of cell addresses (e.g., [Quantity]*[Unit Price])
Auto-expands new columns
Formula auto-fills for all rows
Bottom right hand corner of table has sizing handle
Total Row:
Add via Table Style → Total Row
Dropdown lets you select functions (Sum, Average, Count, etc.)
Importing Data
CSV Files
CSV (Comma Separated Values): plain text, values separated by commas
Widely supported, readable in text editors
Typically includes header row
Text to Columns
Copy CSV data into Excel → use Data → Text to Columns
Select Delimited → Comma
