Excel like a finance pro.
VBA snippets
Each macro has its own page — what it does, the code, a before/after simulation, pitfalls, and a no-code alternative.
New to macros? Set up in 5 minutes▾
- 1
Don't see the Developer tab in the ribbon?
You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.- Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
- Mac:Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
- 2
Paste in the code
Press Alt + F11 to open the Visual Basic editor, then Insert → Moduleand paste the snippet's code into the blank window. Close it with Alt + Q. - 3
Run it
Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in:=GrossMargin(B2, B3).) - 4
Keep the macro — save as .xlsm
File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save. - 5
Macros blocked?
Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.
Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.
Adds a branded 'Contents' sheet with a clickable hyperlink to every other tab — handy for big workbooks.
Saves every worksheet as a separate PDF next to the workbook — board packs in one click.
Refreshes all data connections and PivotTables and blocks until background queries finish.
Loops bottom-up (so deletions don't shift the loop) and removes fully empty rows.
Define your own worksheet function. Paste into a Module, then use =GrossMargin(B2, B3) in any cell.
Opens every workbook in a folder you pick and stacks their rows into one 'Combined' sheet — tagged by source file.
An event macro that writes the save time into a cell — so a printed or emailed copy always shows how current it is.
The workhorse pattern: find the last used row, then walk down and act on each one.
Freeze a range so its formulas collapse to the numbers they returned — no clipboard needed.
For Each over the tabs — format, clean, or stamp every sheet in one pass, skipping the ones you name.
Loop from the bottom up and delete matching rows — the safe way that never skips one.
Append data from one sheet onto the bottom of another — the consolidate primitive.
Wrap slow code so Excel stops repainting and recalculating on every change — the single biggest speed win.
Pull the whole block into memory in one hit, loop it there, and write it back in one hit — the real speed pattern for big data.
Sum amounts per customer — or build a unique list — with Scripting.Dictionary, the closest VBA gets to a pivot in code.
AutoFilter to the rows you want, copy or process only what's visible, then clear the filter.
Use Range.Find to locate a value — or a column by name — so your macro survives inserted or reordered columns.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.