Our Wealth

  Absolute vs Relative References: What the $ Sign Actually Does A relative reference (like A2) shifts automatically when you copy or drag a formula elsewhere, while an absolute reference (like $A$2) stays locked to that exact cell no matter where the formula goes. The dollar sign is what tells Excel to lock a reference in place, and mixing the two correctly is what lets one formula work properly when copied across an entire column or row. Years ago I built a commission calculator that multiplied each salesperson's revenue by a commission rate sitting in one fixed cell, say F1. I wrote the formula for row 2, dragged it down 200 rows the way I always did, and didn't think to check it closely because the first few results looked plausible. It wasn't until someone further down the list got a commission figure of literally zero dollars that I went back and realised the formula had quietly shifted its reference to the commission rate cell on every single row, pointing at an ...

  Text Functions in Excel: LEFT, RIGHT, MID and TEXTJOIN Explained LEFT, RIGHT and MID pull out specific characters from a piece of text based on position, while CONCATENATE and TEXTJOIN combine separate pieces of text back together into one. Between the two groups, they cover almost everything you'll ever need to reshape messy or combined text without retyping anything by hand. For a long stretch early on, I built mailing labels for client letters by manually copying each address piece — name, street, suburb, postcode — one at a time into a single cell, retyping commas and spaces between them by hand, client by client. It genuinely didn't occur to me that Excel could stitch text together with a formula, because nobody had ever shown me, and I'd never gone looking. Someone finally saw me doing this for what must have been the fortieth label that afternoon and asked, not unkindly, whether I'd heard of the ampersand symbol. That one small nudge saved me an embarrassin...

  Data Validation in Excel: Building Dropdown Lists That Actually Work Data Validation restricts what someone can type into a cell, most commonly by turning it into a dropdown list of allowed values. Instead of hoping everyone types "NSW" exactly the same way every time, a dropdown forces them to pick from a fixed list, which quietly prevents a huge amount of the messy, inconsistent data that causes formulas to fail later. I once inherited a spreadsheet where the "Region" column had been typed freely for over a year by half a dozen different people. Between them, NSW had been entered as "NSW," "N.S.W," "nsw," and, memorably, "Sydeny" (missing the second S, presumably typed in a hurry). Every SUMIF and COUNTIF built on that column was quietly wrong, because Excel treated each spelling as a completely different value. Fixing the historical data took a proper cleanup, but the bit that actually stopped it happening again was d...

  Excel Tables: Why You Should Stop Using Plain Ranges An Excel Table is a proper structured object you create with Insert > Table, and it automatically expands to include new rows, carries formulas and formatting down automatically, and lets you reference columns by name instead of by cell range. A plain range of data with headers looks similar on screen, but none of that happens automatically — you're managing all of it by hand, and that's usually where problems creep in. I built a monthly sales dashboard years ago referencing a fixed range, A2:A200 , across a dozen different formulas. It worked fine for months, until the business grew past row 200 and new entries started getting added below the range my formulas actually covered. Nobody noticed for a few weeks, because the dashboard didn't error out, it just quietly stopped counting the newest data. I only caught it because a monthly total looked suspiciously low compared to what I remembered from a meeting. Rebui...

  Conditional Formatting in Excel: A Practical Guide Conditional Formatting automatically changes a cell's colour, font, or icon based on rules you set, without you needing to manually format anything yourself. Excel comes with built-in presets for common cases like highlighting duplicates or colour-scaling a range of numbers, but the real power is in the formula-based option, which lets you build a rule around almost any condition you can write as a formula. For longer than I'd like to admit, I built monthly sales heatmaps by eye — manually shading cells red, yellow, or green based on how each region's numbers compared to target, one cell at a time, every single month. It looked fine in the final report, but it took ages and I had to redo the whole thing from scratch whenever the numbers changed even slightly. A colleague eventually watched me doing this and asked, genuinely confused, why I wasn't just using Conditional Formatting with a formula rule. I didn't ...

  How to Clean Messy Data in Excel: TRIM, CLEAN and Removing Duplicates Most "broken" formulas aren't actually broken — they're being fed messy data that only looks clean. TRIM removes extra spaces, CLEAN strips out invisible non-printing characters, and Remove Duplicates gets rid of exact repeat rows. Running your data through these before building formulas on top of it will fix more mystery errors than almost anything else in this series. If you've been reading along with this blog, you've probably noticed a pattern by now — nearly every "why isn't my formula working" section across these posts eventually points at the same culprit: extra spaces, mismatched text and numbers, or hidden characters sitting in cells that look completely normal. I decided this one deserved its own post because it's honestly the root cause behind more support requests I've fielded from colleagues over the years than any single formula issue. I once spent ...

  IFERROR and IFNA: Handling Excel Errors Properly IFERROR catches any error a formula produces and lets you replace it with something more useful, like a blank cell or a friendly message. IFNA does the same thing, but only for the specific #N/A error, leaving every other error type untouched. Most people should reach for IFERROR by default, but IFNA has a real place when you specifically want other errors to still show up rather than get hidden. I have a slightly embarrassing story about this one. Early on, I got into the habit of wrapping absolutely everything in IFERROR and returning a blank result whenever anything went wrong, because it made my spreadsheets look tidy and error-free. I put together a monthly report like this for a while, blank cells instead of red error triangles everywhere, and felt quite pleased with how clean it looked. It wasn't until my manager asked why three clients seemed to have "no data" for two months running that I realised IFERROR had...

  IF, IFS and Nested IF: When Each One Makes Sense IF tests one condition and returns one result if it's true, another if it's false. IFS checks several conditions in order and returns a result for the first one that's true, without needing them nested inside each other. Nested IF stacks multiple IF functions together to handle several conditions the older way, and while it still works, it gets unreadable fast once you're past two or three levels. I built a nested IF formula years ago to categorise sales tiers — Bronze, Silver, Gold, Platinum, based on revenue thresholds. It worked fine for a while, four levels deep, until my boss asked me to add a fifth tier called "Diamond" for a new top bracket. Adding it meant carefully counting parentheses to find exactly where to insert the new condition without breaking the closing brackets at the end, and I got it wrong twice before it worked. That formula was maybe 200 characters long and genuinely difficult to re...