Posts about Excel.
A pivot table is a tool you operate. GROUPBY is the same group-and-aggregate logic as a single spilled formula - the summary recalculates itself, with no fields to drag and nothing to refresh.
A pivot table is a snapshot that needs refreshing. GROUPBY is a formula that recalculates itself. Swapping one for the other takes about 90 seconds and removes a recurring chore.
REDUCE is the most powerful of the functional array functions and the most overlooked. The lambda which means you can accumulate strings, and replace calculations that used to need VBA.
Joining a column of names or tags into one comma-separated string used to need TEXTJOIN or a helper column. REDUCE does it in one formula, and lets you transform each item before it gets concatenated.
The title changes, the salary changes, and the list of skills that matter changes completely. Worse, the things that made you good as an analyst quietly start working against you at the senior level.
LAMBDA, MAP, REDUCE, SCAN, BYROW. These are functional programming primitives. Excel is shipping them one by one, and most users are still writing nested IFs. The spreadsheet you learned in 2015 has been quietly replaced by something much more powerful.
You'll build a dropdown list that reads from a structured table and includes every new row automatically. No named range maintenance, no 'why isn't my new entry showing up' moments. Structured table plus a spill reference, and your validation list never needs touching again.
MAP takes your LAMBDA and runs it on every element in an array. No helper columns, no dragging formulas, no VBA loops. It's the Excel equivalent of a functional programming pattern, and once you've used it, going back to column-by-column formulas feels like a step backwards.
In a small team, you're the analyst, the engineer, the dashboard designer, and the person who explains what a p-value is at the all-hands.
The analysts who get traction aren't the fastest at pulling data, they're the best at unpacking what was actually being asked. The skill is asking good questions, and nobody trains for it.
Step-by-step from a blank flow, including how to handle the parts that break the first time.
Automation bias is real: if you can automate something, it feels like you should. But some processes contain judgement calls that automation silently gets wrong, edge cases that require human revie…
You can't build every dashboard that gets requested. But 'no' lands differently depending on how you say it — and most data people either say yes to everything and resent it, or say no in ways that…
You'll build a summary that calculates a custom metric for every row in your dataset without a pivot table, without helper columns, and without copying formulas down. BYROW plus a LAMBDA gives you …
BYROW hands your function an entire row at a time. BYCOL does the same by column. Together they replace an entire category of 'drag this formula down and keep it updated' work — and when you combin…
XLOOKUP, LET, LAMBDA, BYROW, SCAN, MAP — Microsoft shipped more genuinely useful functions in the last three years than in the previous decade. If you learned Excel before 2022 and haven't revisite…
BYROW hands your function an entire row at a time — which means 'apply this logic to every row in this range' is one formula in one cell. No dragging, no CTRL+SHIFT+ENTER, no missing the last row w…
The more competent you get, the less people see the effort behind what you deliver. A clean dashboard looks effortless. A fast turnaround looks like it was always fast.
Helper columns aren't wrong. But when a running total requires three of them and breaks every time someone inserts a row, you've created fragility in exchange for familiarity. The Excel array funct…
You'll build a balance tracker using SCAN and a structured table that extends itself as new rows come in. No helper columns, no fragile cell references, no 'just re-paste the formula down.' Add a r…
Running totals used to mean helper columns or array gymnastics. SCAN changes that — it applies a function cumulatively across an array and hands you back every intermediate result. Once you see it …
Most dashboards answer the question 'what happened?' Very few answer 'what should we do?' The gap isn't data — it's intent. A dashboard built for the former often actively gets in the way of the la…
SCAN hands you every intermediate result as it accumulates — which means a running total is one formula that spills down the column. No helper rows, no dragging, no breaking when someone inserts a …
They ask for a pie chart when they need a bar chart. They ask for a table when they need a trend line. This isn't ignorance — it's that they're translating a question into a visual type without kno…
The counter-argument to 'if your formula needs a scrollbar to read, it's wrong.' With LAMBDA, complexity lives in one named place and gets called cleanly everywhere else. The formula in your cell c…
You'll build a named LAMBDA called OVERDUE that takes a date and a threshold and returns a clean status label. Then you'll call it in three different tables in the same workbook. By the end, you'll…
Before LAMBDA, every Excel function that existed was one Microsoft decided to write. After LAMBDA, you can write them yourself — naming the logic, hiding the complexity, and calling your custom fun…
One function definition in the Name Manager, and every cell in the workbook can call it like a built-in. LAMBDA + named functions means you stop copy-pasting logic and start writing like someone wh…
You delivered a clean model, a solid dashboard, a real solution — and got a blank look in the room. The work was right. But explaining technical work to someone who doesn't share your frame of refe…
You'll build a reusable Power Query that finds the most recent entry per group - the kind of query that normally tempts people into messy MAXIFS workarounds in Excel. We're doing it properly, wit…
Stop cringing. There's a way to talk about your accomplishments in Excel and Power BI that sounds competent, not arrogant. It's all about framing.
Complex formulas aren't the problem. Unreadable ones are. Before LET, you had to repeat the same range reference six times or accept that your formula was a black box. LET gives you named variables in
The advice is everywhere: if you're serious about data, you need SQL. But for most analysts already working in Excel and Power BI, the gap isn't SQL knowledge - it's data modelling instinct. Here…
One tweak to your most-used formula and it suddenly reads like plain English. LET() lets you name the messy middle parts — so next month's you won't need to reverse-engineer what current you was …
You deliver good work consistently, your manager knows you're reliable — and yet somehow you're invisible at promotion time. The problem isn't your output. It's that you've never made your work l…
It works, so why change it? Because every workaround you've added to compensate for VLOOKUP's limits is technical debt in your spreadsheet - and it compounds. The cost isn't visible until somethi…
Stop replacing VLOOKUPs. Your workbook deserves XLOOKUP without touching the formula.
I spent years writing 400-character Ctrl+Shift+Enter arrays just to filter a table, but the calculation engine finally changed the rules and killed that misery for good.
It is 2026, and we are still stringing together premium Power Automate connectors just to avoid the Word mail merge wizard.