Our Wealth

  Excel Charts: How to Choose the Right Chart Type Use a line chart to show change over time, a column chart to compare categories, a pie chart only when you have a few parts that add up to a whole, and a scatter chart to show whether two numbers move together. Most unreadable charts come from picking the wrong type for the question, not from poor formatting. I once presented a pie chart with fourteen slices to a management meeting, each slice a different product line. I thought it looked colourful and impressive. Within about thirty seconds, one of the directors asked, politely, what exactly he was looking at, and I realised I couldn't read it properly myself from the back of the room. I rebuilt it as a sorted bar chart that evening, and nobody asked a single question about how to read it the next time. That was more or less the end of my pie chart phase. How do I create a chart in Excel? Select the data you want to chart, including the column and row headings Go to Inse...

  Excel Date Functions: TODAY, DATEDIF, EOMONTH and WORKDAY Explained Excel stores every date as a plain number counting days since 1 January 1900, which is exactly why date maths works at all — adding 30 to a date genuinely adds 30 days, because underneath the formatting, it's just addition. TODAY returns the current date, DATEDIF calculates the difference between two dates in years, months, or days, EOMONTH finds the last day of a month, and WORKDAY adds business days while skipping weekends. For a long while I built a monthly roster manually, typing in the end-of-month date by hand every single time — 31 one month, 30 the next, 28 or 29 in February depending on the year. It never occurred to me this could be a formula rather than something I just had to remember and retype correctly each month, until a colleague building something similar mentioned EOMONTH in passing, like it was the most obvious thing in the world. I'd been manually counting on a desk calendar for rough...

  Excel Keyboard Shortcuts That Actually Save Time A handful of keyboard shortcuts — navigation, selection, formatting, and a few editing ones — cover the vast majority of what slows people down clicking through menus. Learning roughly fifteen of them properly will save far more time than memorising fifty obscure ones you'll never actually use. About fifteen years into using Excel daily, I watched a newer hire jump straight to the bottom of a several-thousand-row spreadsheet with Ctrl+End, then back to the top with Ctrl+Home, in about half a second each way, while I was still manually scrolling with the mouse wheel like I'd been doing the entire time I'd worked there. I distinctly remember feeling a bit foolish watching that happen, given how many years I'd spent doing it the slow way without ever once wondering if there was a better one. What are the navigation shortcuts actually worth learning? These move you around a large spreadsheet instantly, instead of sc...

  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...