85 macro recipes for tax workpapers.
A toggle macro that highlights the entire row and column of whichever cell you click — like Excel's Focus Cell feature, but works on any version. Click to trace, click again to turn off.
Catalogs every chart and chart sheet in a workbook — reports chart type, series count, data source, and flags #REF! sources and hidden-sheet references. Find the broken chart feeding off a deleted sheet in seconds.
Strips the Read-Only Recommended flag and Marked as Final flag from any workbook so you stop seeing the prompt every time you open the file. Two properties, one click, never again.
Scans every sheet for VeryHidden tabs that don't appear in the Unhide dialog, lists what it found, and reveals them all with one click. No data is lost — it was always there, just invisible.
Before sending a tax model to a client, lock every formula cell and unlock every input cell across all sheets. One InputBox for the password, one click to protect the entire workbook. The recipient can change inputs but can't touch your logic.
Export each visible sheet to a separate .xlsx file in seconds. Choose all sheets or pick specific ones — no more Move or Copy, Save As, Close, repeat.
Convert formulas whose every precedent cell is a constant to values in one pass. Archive finalized schedules without touching live calculations — and shrink the file in the process.
Scans every sheet for formulas longer than a threshold you set and compiles them into a report sorted longest-first. One click to find the 847-character nested IF that nobody can audit.
Scans every sheet for Ctrl+Shift+Enter array formulas — the invisible killers that block editing and hide from formula view. Zero input, one report.
Scans every column for formula cells whose R1C1 pattern doesn't match the column's dominant formula. Flags the one cell that was manually altered, copy-pasted wrong, or broken during a restructure — and writes a clickable report. Does not modify the original data.
Pick a template sheet, then this macro compares every other sheet's header row against it and flags missing, extra, or reordered columns on a clickable report. Find the one entity tab that breaks your consolidation before it breaks your formulas.
Scans every hyperlink on every sheet and tests file-path links for existence. Produces a report showing which links are live, which are broken, and which are web or email links that need manual review.
Scans every sheet for dates in mismatched formats — real dates, text dates, ERP exports, bank feeds — and standardizes them all to one format. Report first, then convert.
Finds every cell containing an empty string (the formula result of "") and replaces it with a truly blank cell. Fixes broken pivot tables, restores ISBLANK(), and shrinks the used range — across every visible sheet in one pass.
One click removes the invisible leading apostrophe from every cell in your workbook, converting text-formatted numbers and dates into their actual types. The silent killer of SUMIF, PivotTables, and sorts.
One click trims every invisible space in your workbook — leading, trailing, and internal double-spaces. The silent killer of VLOOKUPs, SUMIFs, and pivot table groupings, fixed in under two seconds.
One InputBox stamps a classification label onto the header or footer of every visible sheet. No more adding 'CONFIDENTIAL — FOR INTERNAL USE ONLY' to 30 tabs one at a time.
Sort every visible sheet alphabetically with one click, or define a custom order from a lookup sheet. Stop hunting for tabs.
One macro that sets freeze panes at a specified row and column on every visible sheet. Two InputBoxes, twenty tabs standardized in under five seconds.
One macro that inserts a SUM row at the bottom of your data, compares every column against your expected totals, and color-codes matches green and mismatches red. Instant sanity check for any received workpaper.
One InputBox, one number, and every column on every sheet gets the same width. Or enter 0 and every column auto-fits to its content. Stop adjusting columns one sheet at a time.
Scans every sheet module in your VBA project and lists all event handlers — Change, Activate, SelectionChange, and more — in a single report sheet so you can find the code that's silently reformatting, recalculating, or hiding your data.
Scans every sheet for volatile functions — INDIRECT, OFFSET, TODAY, RAND, and friends — and compiles them into a sortable report with an estimated recalculation cost. Find every drag on your workbook in 3 seconds.
Catalogs every pivot cache in the workbook — what data each one pulls from, how many pivots use it, and whether it's orphaned or a duplicate. Removes dead caches with one click.
Scans every data connection in the workbook and tells you which ones are orphaned. Removes dead connections with one click while leaving active query links intact.
Produces a one-page diagnostic report covering file size, sheet count, used-range dimensions, named ranges, pivot caches, custom styles, external links, hyperlinks, conditional formatting rules, comments, shapes, and VBA modules. The first thing you run when you inherit a mystery workbook.
Select a folder full of entity workbooks, run the macro, and every sheet imports into your master consolidation workbook — with source-file tracking and automatic name deduplication.
Select any range on any sheet, run the macro, and get a clean CSV file in the same folder — values only, no formulas. Two clicks replaces copy-paste-save-as-delete.
Select any range with your mouse and get an instant MsgBox report: total cells, numeric count/sum/average, blanks, text, formulas, and errors. Faster than writing COUNT, SUM, COUNTA, and COUNTBLANK formulas you'll delete five minutes later.
Scans every sheet and reports protection status, password presence, editable ranges, and cell lock state on a single read-only report. The companion to Sheet Lock/Unlock — know what you're dealing with before you act.
One InputBox sets every visible sheet to the same zoom percentage. Fix inherited workbooks where every tab is at a different zoom level in under a second.
Scans every sheet for cell comments, lists them with counts per sheet, and deletes them all with one confirmation click. The companion to Comment Collector — save notes first, then strip them before sending.
Scans every sheet for floating shapes — text boxes, arrows, rectangles, callouts — lists them by sheet and count, then deletes them all with one confirmation click. Clean up inherited workbooks in seconds.
One click removes freeze panes from every sheet in the workbook. Lists which sheets were affected so you know what changed. The fastest way to un-break an inherited file's scroll positions.
Scans every sheet for conditional formatting rules, lists them with a per-sheet breakdown, and removes them all on confirmation. Fix slow, chaotic workbooks inherited from prior preparers.
Scans a workpaper for cells where the displayed value and the underlying value don't match — formatted to zero decimals but hiding fractional amounts. One click generates a ranked discrepancy report without touching the original data.
Scans every sheet for merged cells, shows you exactly where they are, and unmerges them all with one confirmation click. Values stay in the top-left cell — nothing is lost.
Select a template range with dropdowns and input restrictions, then copy those rules to the same cells on other sheets — all in one pass.
Excel Tables, structured references, dynamic named ranges, and INDIRECT — four patterns that make your SUM ranges expand automatically so your workpapers survive new rows, new tabs, and new preparers.
Scans every sheet for leftover print areas, lists which sheets are affected, and clears them all with one confirmation click. No more hitting Ctrl+P and seeing only rows 1-47 from the prior preparer.
Scans all sheets (or just the active one) and compiles every formula into a searchable, hyperlinked report. Your original workpaper stays completely untouched.
IFERROR, IFNA, ISERROR, ISNA — the four error-handling functions every tax preparer uses and misuses. A decision tree and real workpaper examples to help you catch the errors you expect and surface the ones you don't.
A complete guide to Excel's Go To Special dialog — select every error cell, every hard-coded number, every blank, every conditional format, and more in one click. 8 options, 8 tax workpaper scenarios, zero macros.
Build checks directly into your workpaper structure — control totals, cross-foot verification, hash totals, variance flags, and version drift detection. No macros, no VBA. Just formulas that scream when something's wrong.
SUMIFS, XLOOKUP, EOMONTH, IFERROR, and TEXT — five functions that replace the most common manual work in tax workpapers. Each one explained with a real tax scenario you'll recognize from your own engagements.
Move the 10 most-clicked ribbon commands to the Quick Access Toolbar so they're always one keystroke away. Save 15–20 minutes per day by replacing mouse clicks with Alt+1 through Alt+9.
Side-by-side comparison of XLOOKUP, VLOOKUP, and INDEX-MATCH with real tax workpaper examples. If you've been using VLOOKUP since 2010, here's what you're missing — and how to switch in 30 seconds.
Resets the last-cell cursor position on every sheet so Ctrl+End lands on the actual last cell with data, not row 65,536. Fixes scrolling, printing, and file size.
15 keyboard shortcuts every tax preparer should know, each with a real workpaper scenario. From tracing precedents to selecting visible cells only — replace mouse clicks with keystrokes and reclaim the time you lose to Excel's ribbon.
One click converts every text-formatted number in your workbook to actual numeric values — no more SUMIF returning zero on your ERP trial balance export.
One click unhides every hidden row and column across all sheets. The row/column equivalent of Unhide All Sheets — zero configuration, instant results.
Finds every user-created cell style in your workbook — the kind that's been copied from 12 preparers over 7 years — lists them all in one report, then deletes them with a single confirmation click.
Select your header row, specify which columns must be populated, and this macro finds every blank and lists them on a clickable report. Never miss a missing account code again.
Scans every sheet for data validation rules, lists which sheets are affected, then removes them all with one confirmation click. No more wondering why you can't type in a cell.
Convert every formula on every sheet to its current value with a double-confirm gate. For archiving completed engagements and sending workpapers to clients who shouldn't see your formulas.
Color-codes every cell on the active sheet (or all sheets) by its type — formulas are blue, hard-coded numbers are yellow, text is green. Instantly see what's calculated and what's typed, without clicking a single cell.
Save a date-stamped copy of your workbook with a single click. No typing filenames, no File → Save As dialog — one MsgBox reports the new filename and you move on.
One click hides every gridline on every visible sheet. Run it again and they all come back. Twenty lines of VBA, fully reversible, zero configuration.
Standardizes print settings — landscape, fit-to-width, narrow margins, centered — across every qualifying sheet in one pass. No more setting up Page Setup 30 times before the partner review.
Scans every sheet for hyperlinks, lists what it found and where, then removes them all with one confirmation click. No more sending internal file paths to clients.
Colors every sheet tab by category using a lookup table you edit — not the VBA. Keyword matching auto-assigns green to trial balances, blue to schedules, orange to fixed assets. One click, 30 color-coded tabs.
One click hides every zero value on every visible sheet. Run it again and all zeros come back. Twenty lines of VBA, fully reversible, zero configuration.
Lists every named range — workbook-level and sheet-level — on a single report sheet with scope, visibility, refers-to formula, and current value. The companion to Delete Broken Named Ranges: understand what's there before you clean it up.
One password, one click, every visible sheet locked or unlocked. Lock workpapers for filing, then unlock them for batch edits — without right-clicking 30 tabs and retyping the same password each time.
Scans every sheet in a workbook and builds a complete inventory — visibility, row count, column count, formula status, protection, and more. The first macro you run when someone hands you their file.
One click removes every AutoFilter on every sheet in your workbook. Ten lines of VBA, zero configuration, and the fastest way to see what the prior preparer was looking at.
Welcome to the Tax Automation Blog. Copy-paste VBA macros built for tax preparers, not developers. No programming required — just real macros for real workpapers.
Scans every cell comment on every sheet and compiles them into a single report with hyperlinks back to each source cell. One click replaces 20 minutes of clicking through 30 tabs.
Finds every IFERROR and IFNA formula on every sheet, classifies them as safe or suspicious, and flags the ones that are silently hiding #REF! errors behind a blank cell. Your workpaper looks clean — the macro proves otherwise.
Scans every sheet in a workbook, finds every formula error cell, and builds a clickable report with hyperlinks back to each one. Zero input — open a broken workbook, run the macro, and triage every error in seconds.
Scans every named range in your workbook, identifies which ones are broken (#REF!), lists them with visibility info, and deletes them all with one confirmation click. No more deleting them one at a time in Name Manager.
Select a column, and this macro flags every row where the value appears more than once. Light yellow highlight, zero deletions — you decide what stays and what goes.
Select a date column and an as-of date — the macro inserts a new column showing age in years and months for every date. Non-destructive, works on any date column.
Click the category column, click the value column, and instantly get collapsible SUBTOTAL rows at every group change plus a grand total. No typing, no formula building, no Data → Subtotal menu hunting.
Scans every formula across every sheet and builds a report of every workbook this file links to — which sheets reference each file, how many formulas per sheet, and whether the link is live or broken. Non-destructive. No confirmation dialog.
Scans a workpaper for hard-coded numbers hiding in columns where every other cell is a formula. Flags only the suspicious ones — not every constant on the sheet — and writes a reviewable report to a new tab without touching the original data.
Paste your prior-year and current-year trial balances into two sheets, run the macro, and get a side-by-side comparison with dollar and percentage variances. Accounts that opened, closed, or moved more than your materiality threshold are all flagged in one pass.
One macro that sets the period-end date on the first sheet and links every other sheet to it with a formula. Change one cell, update forty tabs.
Strips leading and trailing spaces from every sheet name, then optionally sorts tabs alphabetically. Handles name collisions gracefully so you don't break the workbook.
One macro finds every cell linking to another workbook — live or broken — and replaces it with the current value. Send clean workpapers without the 'This workbook contains links' warning.
Scans every sheet in a workbook, builds a hyperlinked table of contents on a new index sheet, and auto-updates when tabs are added, renamed, or deleted. The 30-tab workbook just became navigable.
A tongue-in-cheek macro that keeps your status green while you step away from your desk. Teams won't know. Slack won't know. Your conscience might.
Three InputBoxes — firm name, client name, tax year — and every qualifying sheet gets standardized headers and footers for print. Twelve sheets updated in under a second.
Two InputBoxes, one confirm, and every sheet in the workbook is updated. Counts replacements per sheet so you know exactly what changed.
Unhide every hidden sheet in a workbook with one click. Ten lines of VBA, zero configuration, and the fastest win you'll ever get from a macro.
No posts found