I get excited whenever Microsoft drops a new Excel function, but when I'm in the trenches with a deadline, I reach for the classics.There's a certain comfort in knowing a formula will work exactly as expected every time.Learning these "archaic" tools has made me a better geek.
Because their logic is transparent, they forced me to understand the data's underlying structure, which has made jumping into more advanced systems like Power Query feel much more intuitive and less intimidating.Here are the old-school Excel functions every power user should keep in their back pocket.SUMPRODUCT: The Swiss army knife of math Handle arrays without the modern baggage Ever tried building a conditional calculation in modern Excel and ended up with multiple helper columns or a sprawling dynamic array formula? That's where SUMPRODUCT re-enters the conversation.
It combines logic and math in a single step without results spilling across the sheet.Even though Excel now offers more modern ways to filter and aggregate data, SUMPRODUCT keeps everything contained in a single formula.There's no spilling, no restructuring, and no "where did that spill go?" problem.
What makes it especially powerful is how it treats logic.A condition like "Department = Design" generates TRUE/FALSE arrays, which Excel implicitly coerces into 1s and 0s in arithmetic contexts.That quiet behavior turns simple comparisons into flexible filtering logic.
SUMPRODUCT isn't elegant in a modern sense—but it's stable, compact, and extremely hard to break.SUBTOTAL: Totals that respect your filters Keep results accurate when rows disappear SUBTOTAL is a reliable reporting tool because it responds to what the user is actually seeing.A normal SUM ignores filters, which is fine in static models—but I've seen it trip up even experienced Excel pros in interactive reports.
SUBTOTAL fixes that by automatically calculating only visible rows.That's why it's still preferred in dashboards and operational spreadsheets: it aligns math with user intent.If a filter changes what's on screen, the number should change too, and SUBTOTAL guarantees that without extra logic.
Related Don’t Use SUBTOTAL in Excel: Use AGGREGATE Instead Generate subtotals with more flexibility.Posts By Tony Phillips INDEX and MATCH: The flexible lookup duo The #1 choice for control and transparency Ask experienced Excel users how they perform lookups, and you'll often hear the same answer: INDEX and MATCH.Not because they don't know XLOOKUP exists, but because they've been using this combination for years and it still works exactly the way they expect.
MATCH finds the position of a value, and INDEX retrieves the corresponding value from that position.That separation matters more than it seems, especially in large or inherited spreadsheets where you end up debugging someone else's logic.There's a familiarity here that newer functions don't always replace.
INDEX and MATCH aren't just a technical choice anymore—they're muscle memory, built up over years of working in spreadsheets where it already does the job reliably without thinking.Modern functions prioritize simplicity.INDEX and MATCH, however, prioritize control, which—along with its familiarity—keeps it alive.
CHOOSE: Simple branching without nested logic A quick logical option in small models CHOOSE is one of my favorite functions because it handles more logic than you'd expect.Instead of evaluating conditions, it simply selects an output based on position.What makes CHOOSE interesting is that it constantly shows up in real operational models where the logic is fixed but important.
Whether it's commission tiers, SLA targets, salary bonuses, or pricing rules, it often replaces simple lookup tables or basic conditional mappings in small models.In these cases, it embeds a stable rule directly into the formula.It's not scalable, but in the right context, it's still one of the quickest ways to think.
TEXT: Formatting values inside formulas Convert numbers into readable strings I'm a stickler for how data looks, and there's nothing worse than a messy concatenation that turns a perfectly good date into a five-digit serial number.This is one of Excel's most annoying habits: the second you combine a value with text, it strips away your cell formatting.TEXT is my go-to for keeping generated outputs clean, as it lets me intercept raw data and force it into a specific format directly inside formulas.
It's a small detail, but it's what separates a professional-grade dashboard from a basic one.By manually defining codes like "mmmm" or "$#,##0", you ensure that your dates stay as months, and your currency keeps its symbols.Even with all the modern reporting tools available, the TEXT function remains the most direct way to polish your work without cluttering your workbook with extra helper columns just to fix your presentation.
Related Stop wrestling with text in Excel: These 8 tools are game-changers Fix "fake" numbers, strip hidden web spaces, and join text professionally using Excel's built-in legacy and modern tools.Posts 1 By Tony Phillips IFERROR: The ultimate imperfection cleaner Keep dashboards looking professional Few things break a dashboard faster than a wall of #DIV/0! or #N/A errors.Subscribe to the newsletter for timeless Excel tools Join the newsletter to get practical, battle-tested Excel techniques: legacy functions, debugging approaches, and presentation fixes explained clearly so you can apply them in real spreadsheets.
Subscribe for usable spreadsheet expertise and straightforwa Get Updates By subscribing, you agree to receive newsletter and marketing emails, and accept our Terms of Use and Privacy Policy.You can unsubscribe anytime.That's why IFERROR is still everywhere.
It instantly replaces any formula errors with a fallback value, so the sheet stays clean and readable.I've found myself habitually wrapping many of my formulas in IFERROR without even thinking about it, especially in reporting sheets where I just want clean output and don't want edge-case errors surfacing.While more precise error handling exists, IFERROR remains popular because it's instant and universal.
Wrap a formula, and the output is safe for presentation.Use IFERROR with caution.Because it's a catch-all, it can easily hide genuine logic errors or broken references that you actually need to fix.
OFFSET: Legacy dynamic ranges that still power dashboards Keep ranges updating even without structured tables Older Excel models often show their age through the way they handle moving data ranges, and OFFSET is often part of why they still work.It builds ranges that shift as data grows, which made it incredibly useful before structured tables became the default way to manage expanding datasets.That said, structured tables are now the modern standard for a reason.
They're more stable, easier to audit, and don't rely on volatile recalculation behavior in the same way OFFSET does.However, tables require a rigid, unbroken structure.If you're building a presentation-ready report that needs "breathing room"—like blank spacer rows or non-contiguous data blocks—a table becomes a liability.
In those scenarios, OFFSET is one of the remaining ways to surgically navigate the grid.By pairing it with a search function to "hunt" for the last row, you can maintain a dynamic dashboard that doesn't break just because you added a few empty rows for better visual layout.It's a legacy tool, but one that still powers a surprising number of real-world spreadsheets where flexibility beats rigidity.
So, which Excel user are you? Are you the kind of person who chases the newest function, or the one who reaches for these older tools without thinking twice? I sit somewhere in the middle.I like new features, but when things get messy or time is tight, I still default to the functions I've used for years.At the end of the day, nobody gets rewarded for using the flashiest Excel function.
The real reward is getting Excel to do what you need it to do using whatever combination of tools works best.Microsoft 365 Personal OS Windows, macOS, iPhone, iPad, Android Free trial 1 month Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.$100 at Microsoft Expand Collapse
Read More
