Tips & tricks
Small tricks. Fewer late nights.
Practical notes on Excel, Power Query, SQL, Python and Claude agents, plus the story of building KeyTips.
- Seven Power Query lessons from five finance problemsJournal uploads, a P&L crosstab and a folder of team workbooks all came down to the same few habits. Here they are, with the case behind each one.
- Turning a wide allocation sheet into a journal uploadShared costs split by percentage have to become one journal line per cost centre. Unpivot does the reshaping, and an account-level reconciliation explains every gap.
- Bringing Windows Office muscle memory to the MacI wanted Alt, H, V, V on my Mac, one key at a time. So I built KeyTips with Claude, and it now covers more than 1,100 sequences.
- Building a journal from sheets that can't be tablesWhen source sheets can't become Excel tables, dynamic named ranges can feed Power Query instead. A debit-only plug then balances the cents without touching credits.
- Why a keyboard shortcut needs a state machineTapping Option is easy to describe and surprisingly hard to detect. Here's the small design that makes sequential shortcuts safe.
- Replacing 11 query branches with one rules tableEvery mapping change used to mean editing code. Moving the logic into an editable rules sheet produced one query that finance users can maintain themselves.
- The shortcuts that weren't therePaste Values and Paste as Picture had no keys. The causes were guessed names and menus read too early, and the fix was a safety net.
- Rebuilding a database P&L crosstab in Power QueryA 16-column monthly P&L over about 300,000 ledger rows. Step order makes the refresh workable, and one Table.Pivot argument keeps the layout from breaking.
- From 0.66 seconds to 0.03A shortcut that lags feels broken. One fix made KeyTips about 20 times faster in PowerPoint, and Excel users will recognise the cause.
- Consolidating the same sheet from many team workbooksSeveral teams, one workbook each, all in a cloud-synced folder. A per-file function stacks their journal sheets and handles the clutter that breaks a naive combine.
- What it takes to ship a vibe-coded Mac appGetting KeyTips working was the quick part. Letting other people install it means signing, notarization, permissions and updates.
- Giving KeyTips a real Liquid Glass iconMy app needed an icon that fit modern macOS. What worked was starting from one idea, then letting Apple's tools draw the glass.
- Alt+= doesn't work on a Mac. Here's what doesExcel for Mac has its own AutoSum shortcut, Command-Shift-T. Here's how it behaves, and how I mapped it to Option-=.
- The unpivot choice that decides if next month worksBudget files with a column per month need unpivoting. One menu option and one automatic step decide whether the query survives a new month.
- Combine a folder of monthly files without surprisesFrom Folder is the classic month-end time saver. Three small additions keep it from failing on lock files or hiding where a number came from.
- Find what's missing with a Power Query anti joinSkip the VLOOKUP-and-filter-for-errors routine. A Left Anti merge returns exactly the rows that have no match, and it refreshes.
- Why your Power Query is slow, and how to check for foldingWhen a query reads from a database, the order of your steps can decide whether the server does the work or your laptop does.
- Your variance % flips sign on a negative budgetDividing by the budget breaks when the budget is a loss. One ABS() and a LET() fix it for good.
- LAG() doesn't know a month is missingMonth-over-month SQL quietly compares the wrong periods when a month has no rows. Build a month spine first.
- Reconcile two ledgers in five lines of pandasAn outer merge with indicator and validate finds every break, and tells you when the keys themselves are the problem.
- Give your agent a tie-out, not your trustAI agents work well on finance data when "done" means the numbers reconcile, and the check runs outside the agent's own judgment.