Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All VBA snippets

Act on only the formulas, the constants, or the blanks

The audit move: find every number someone typed over a formula in a model they handed you.

1What it does

The most dangerous thing in an inherited model is a hardcoded number sitting in a row of formulas — someone typed over a calculation once to make a total come out right, and it's been wrong ever since. SpecialCells finds cells by their nature rather than their position: formulas, constants, blanks, errors, visible cells. Selecting the numeric constants in a range that should be all formulas highlights every one of those overrides in a single call. It's also how you fill every blank in a column, or clear every error, without a loop.

2The code

Sub HighlightHardcodes()
    Dim ws As Worksheet, calcArea As Range, typed As Range

    Set ws = ActiveSheet
    Set calcArea = ws.Range("C5:N40")      ' should be formulas throughout

    ' SpecialCells raises error 1004 when it finds nothing, so trap it.
    On Error Resume Next
    Set typed = calcArea.SpecialCells(xlCellTypeConstants, xlNumbers)
    On Error GoTo 0

    If typed Is Nothing Then
        MsgBox "No hardcoded numbers in " & calcArea.Address(False, False) & ".", vbInformation
        Exit Sub
    End If

    typed.Interior.Color = RGB(255, 226, 220)      ' flag them for review
    MsgBox typed.Cells.Count & " hardcoded number(s) found in a formula area:" & _
           vbNewLine & typed.Address(False, False), vbExclamation
End Sub

Sub FillBlanksWithZero()
    Dim blanks As Range
    On Error Resume Next
    Set blanks = ActiveSheet.Range("D2:D500").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not blanks Is Nothing Then blanks.Value = 0
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

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 → Module and 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.

3When you use it

Find numbers typed over formulas in an inherited model.
Fill every blank in a column with zero, or with the value above.
Clear or flag every error value in a report in one step.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Forecast area — every cell should grow the one to its left by 4%

44,200	45,968	47,807
22,100	23,000	23,920
31,200	32,448	33,746
3,120	3,245	9,000

Every cell looks like a forecast figure. Two of them aren't formulas.

5Pitfalls

SpecialCells raises run-time error 1004 "No cells were found" instead of returning Nothing.

Fix: Wrap the call in On Error Resume Next, switch it straight back off, then test for Nothing — as above.

Called on a single cell, it silently searches the ENTIRE used range of the sheet.

Fix: Always call it on a multi-cell range you've defined.

It's limited to about 8,192 non-contiguous areas in older versions and fails on a very fragmented result.

Fix: Break the range into chunks if you hit it; modern Excel has largely lifted the limit.

Blanks includes cells that contain an empty string from a formula.

Fix: Those aren't blank to SpecialCells. Test Len(cell.Value) = 0 if formula blanks matter.

6No-code alternatives

Go To Special (F5 → Special) — The same selection by hand, and the fastest way to audit a model once. See the tip.
Show Formulas (Ctrl + `) — Displays every formula so a typed number stands out by looking different. Good for a visual scan.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

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.