How to Create a Macro in Excel
Create an Excel macro to automate repeated formatting, cleanup, reporting, and data-entry steps without rebuilding the workflow by hand.
An Excel macro records or runs a set of actions so you can repeat them with one click. Use macros for recurring cleanup, formatting, report prep, and workbook setup tasks where the steps are predictable.
Macros are powerful, but they should automate a stable workflow. Do not start with VBA if the process still changes every week.
Turn on the Developer tab
Step 1. Go to File -> Options -> Customize Ribbon.
Step 2. Check Developer, then choose OK.
Step 3. Open the Developer tab and choose Record Macro.
Step 4. Give the macro a short name with no spaces, such as FormatWeeklyReport.
Step 5. Choose where to store it. Use This Workbook if the macro only belongs in the current file.
After recording starts, Excel captures many of your actions. Clean, deliberate steps matter.
Example: format a weekly restaurant expense report
Suppose a restaurant manager exports weekly expenses and needs the same setup before review: date formatting, currency formatting, filters, frozen headers, and column widths.
Record a macro that:
- Selects the expense table.
- Applies filters.
- Formats the Date column as a short date.
- Formats Amount as currency.
- Freezes the header row.
- Auto-fits the columns.
- Adds a bold title above the table.
The next week, the manager can paste the new export into an expense tracker for restaurants and run the macro instead of repeating the setup by hand.
When recording is enough
Recorded macros are best for visible worksheet actions: formatting, filtering, sorting, inserting columns, clearing old rows, and preparing reports.
They are weaker when the process needs decisions. If the macro needs to detect changing headers, skip bad rows, or combine files, you may need to edit the VBA or use Power Query instead.
Common macro mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Recording random clicks | Macro becomes fragile | Plan the steps before recording |
| Using fixed cell references | Macro breaks when data moves | Start from tables or named ranges |
Saving as .xlsx | Macro is removed | Save as .xlsm |
| Running untrusted macros | Security risk | Only enable macros from trusted files |
WATCH OUT
The Griddy way
Macros are useful, but deciding what to automate and what to leave manual is the hard part.
"Look at this weekly expense workbook and suggest a simple macro for formatting the report, freezing headers, adding filters, and preparing it for review"
Griddy can inspect the workflow, propose the repeatable steps, and help turn a messy recurring process into a safer spreadsheet routine.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Automate the recurring spreadsheet steps around templates
Macros are most useful when a team repeats the same cleanup, formatting, and report-prep steps before reviewing operating templates.
Expense Tracker for Restaurants
Track restaurant food costs, labor support, supplies, repairs, delivery fees, vendors, and receipts in one expense spreadsheet.
Open templateFinanceSmall Business Budget for Restaurants
Plan restaurant sales, food costs, labor, rent, supplies, delivery fees, and operating margin in one free budget spreadsheet template.
Open templateProject ManagementProject Tracker for Restaurants
Track restaurant openings, menu updates, repairs, vendor tasks, marketing projects, owners, blockers, and due dates in one sheet.
Open templateFinanceExpense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
Open template