Excel’s Counting Functions – Accounted For

Excel’s Counting Functions: A Support Group for People Who Don’t Trust Their Own Eyes

It’s 11:47 PM. You have a spreadsheet with 4,000 rows. Someone in a meeting tomorrow is going to ask “how many of these are actually filled in?” and you are going to have to answer with a straight face, not with “I scrolled really fast and it felt like a lot.”

This is why COUNT functions exist. Excel, bless its cold logical heart, does not get tired, does not lose its place, and does not need a coffee break at row 2,847. It just counts. That’s it. That’s the whole personality. And yet somehow there are five different flavors of “counting,” because Microsoft looked at a simple concept and said “what if we made this a whole personality quiz.”

So let’s meet the family. Grab a coffee. This is going to be more entertaining than it has any right to be.

COUNT() — The Numbers-Only Bouncer

COUNT stands at the door of your range like a bouncer at an exclusive club, and the dress code is strict: numbers only. Text? Turned away. Blank cells? Not even acknowledged. Dates? Let in, because dates are secretly numbers wearing a costume, and this bouncer isn’t fooled by costumes.

Syntax: =COUNT(range)

Example 1: The Gym Membership That Never Was

You’ve got a column of “Days Attended This Month” for your office fantasy fitness league. Half the cells say things like “vacation,” “sick,” or the deeply honest “no.” The other half have actual numbers.

=COUNT(B2:B20)

This tells you exactly how many people showed up with an actual number to report, ignoring everyone who typed an excuse instead of a digit. Excel doesn’t care about your feelings, and neither, apparently, does your gym attendance.

Example 2: Invoice Amounts With Trust Issues

You’ve got a column of invoice totals, but a few cells say “PENDING” because Steve in accounting still hasn’t sent the number over.

=COUNT(D2:D50)

Result: the count of invoices that actually have a dollar figure, cleanly ignoring Steve’s optimism-based placeholder text. COUNT doesn’t do vibes. It does numbers.

COUNTA() — The One Who Counts Everything, Including Your Chaos

If COUNT is the strict bouncer, COUNTA is the bouncer’s cousin who lets literally everyone in — numbers, text, dates, that one cell where someone typed a single space because they’re a monster, an error value, whatever. The only thing COUNTA refuses to count is a cell that is truly, completely empty.

Syntax: =COUNTA(range)

Example 1: Who Actually RSVP’d

You sent out a form. The “Response” column has “Yes,” “No,” “Maybe,” and a bunch of blank cells from people who are, frankly, cowards.

=COUNTA(C2:C40)

This counts everyone who typed something — even a wishy-washy “Maybe” counts as a response. The blanks are the only ones who get ghosted by this formula, which feels fair.

Example 2: The To-Do List Audit

Column A has task names. You want to know how many tasks actually exist in your list, whether they’re marked done, in progress, or just sitting there as a name with no status.

=COUNTA(A2:A100)

Boom — total task count, no filtering, no judgment. Well, some judgment, if you look at how many are actually marked “done.”

COUNTBLANK() — The Function That Points at the Empty Chairs

COUNTBLANK is the opposite energy of COUNTA. It walks into the room and counts only the empty chairs. It’s the function equivalent of a teacher doing attendance and specifically noting who didn’t show up.

Syntax: =COUNTBLANK(range)

Example 1: The Survey Nobody Finished

You handed out a 10-question survey. Column F is “Question 10 Answer,” and a suspicious number of people bailed before reaching it.

=COUNTBLANK(F2:F80)

This tells you exactly how many people rage-quit your survey before the finish line, which is valuable data for exactly one purpose: making question 10 shorter next time.

Example 2: The Onboarding Checklist With Gaps

New hires fill out a form, and column E is “Emergency Contact Phone Number” — a field that, statistically, at least a dozen people will “forget” to fill in.

=COUNTBLANK(E2:E60)

Now you know exactly how many people need a very polite, slightly threatening follow-up email.

COUNTIF() — The Bouncer With One Very Specific Rule

COUNTIF is where things get interesting. It’s still a bouncer, but now it has an actual dress code you get to write yourself. “Only let in cells that say ‘Complete.'” “Only let in numbers over 100.” “Only let in cells that start with ‘Invoice-‘.” It’s one condition, applied ruthlessly.

Syntax: =COUNTIF(range, criteria)

Example 1: How Many People Chose Pizza

Your office lunch poll has everyone’s choice in column B: Pizza, Salad, or “I’ll just eat at my desk in shame.”

=COUNTIF(B2:B30,"Pizza")

Instant, definitive proof of what the people actually want, which — let’s be honest — was never in question.

Example 2: Overdue Invoices

Column C has due dates, and you want to know how many are before today.

=COUNTIF(C2:C100,"<"&TODAY())

This counts every invoice whose due date has already slipped into the past, which is Excel’s polite way of saying “you have a collections problem.”

COUNTIFS() — The Bouncer Who Now Has a Clipboard and a List of Demands

COUNTIFS is COUNTIF after it went to management training. Now it doesn’t just have one rule — it has multiple rules, and a cell only gets counted if it satisfies every single one of them. This is the function you reach for the moment your boss says “okay but how many of those were ALSO from March.”

Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, ...)

Example 1: Pizza Lovers Who Are Also on Time

You want to know how many people chose Pizza (column B) and RSVP’d before the Friday deadline (column D has RSVP dates).

=COUNTIFS(B2:B30,"Pizza",D2:D30,"<="&DATE(2026,7,10))

Now you know exactly how many punctual pizza people you’re dealing with. This is oddly specific and exactly the kind of thing your manager will ask for.

Example 2: Overdue AND Over $500

Back to the invoices — but now you only care about the ones that are both overdue (column C) and actually significant amounts (column D).

=COUNTIFS(C2:C100,"<"&TODAY(),D2:D100,">500")

This is the exact number of invoices that should be keeping your finance team up at night, and now you can quote it in a meeting with the confidence of someone who definitely didn’t just guess.

The Grand Finale: A Nested COUNTIFS Monstrosity

Okay. Deep breath. Here’s the scenario your boss will absolutely give you one day, worded exactly like this:

“I need to know how many sales reps closed MORE than 5 deals in Q2, but ONLY count reps whose region is either ‘North’ or ‘South,’ and exclude anyone whose status column says ‘On Leave.'”

Multiple regions in an OR condition, wrapped inside an AND condition, wrapped inside a count. This is where a single COUNTIFS isn’t quite enough on its own, because COUNTIFS handles AND beautifully but throws its hands up at OR. The fix: add two COUNTIFS calls together for the OR part.

Assume:

Column B = Region
Column C = Deals Closed
Column D = Status

=COUNTIFS(B2:B200,"North",C2:C200,">5",D2:D200,"<>On Leave")
+COUNTIFS(B2:B200,"South",C2:C200,">5",D2:D200,"<>On Leave")

Let’s translate that back into human:

First COUNTIFS: count reps in the North region, with more than 5 deals closed, who are not on leave.
Second COUNTIFS: same rules, but for the South region.
Add them together, because a rep can’t be in both regions at once (unless your HR department has some explaining to do).

Want to make it even more elegant and future-proof, so you’re not manually adding formulas every time someone invents a new region? Wrap the region logic in SUMPRODUCT instead:

=SUMPRODUCT((C2:C200>5)*(D2:D200<>"On Leave")*((B2:B200="North")+(B2:B200="South")))

This one-liner multiplies three logical tests together like a tiny boolean orchestra: “more than 5 deals” times “not on leave” times “is North OR South.” Any row that satisfies all three ends up contributing a 1 to the total; anything else contributes a 0. SUMPRODUCT just adds it all up. It looks intimidating the first time you see it. It is, secretly, just COUNTIFS that learned how to do OR without having an existential crisis.

The Actual Takeaway

COUNT — numbers only, everyone else gets the door.
COUNTA — anything that isn’t truly blank gets counted.
COUNTBLANK — counts the silence, the gaps, the people who ghosted your form.
COUNTIF — one rule, ruthlessly enforced.
COUNTIFS — multiple rules, all required, no exceptions.
Nested COUNTIFS / SUMPRODUCT — for when your boss’s request has the word “but” in it more than twice.

Master these five and you will never again have to answer “how many” with a shrug and a guess. And honestly, in corporate life, that’s about as close to a superpower as you’re going to get.

LET me introduce you to Excel’s LET function

LET Me Explain: Why This One Function Fixed the Ugliest Habit in Your Spreadsheets

Because typing the same formula four times in a row is not a personality trait. It’s a cry for help.

Let me paint you a picture.

You’re building a formula. It needs to check something, then do a calculation with that something, then do another calculation with that something, and finally format that something into a sentence for a report. Reasonable request. Excel should handle this easily.

So you write it. And because Excel formulas don’t have variables — or at least, they didn’t used to — you end up writing the same lookup, the same calculation, the same nested mess, four separate times inside one formula. Once to check it. Once to use it in the “if true” branch. Once to use it in the “if false” branch. Once more because you forgot you already wrote it and pasted it in again out of muscle memory.

Your formula is now 400 characters long. It contains the same XLOOKUP repeated so many times that if XLOOKUP were a person, it would have filed a restraining order. It works — technically — but if you need to change one tiny thing about that lookup, you now have to find and update it in four different places, inside one unreadable wall of parentheses, at 6pm, on a Friday, with the file due in ten minutes.

This is the exact moment LET was invented for. And if you’ve never used it, congratulations — you’ve been doing the spreadsheet equivalent of writing out “the customer’s total order value including tax and shipping” longhand every single time instead of just… naming it once and calling it back.

Let’s fix that.

First, What LET Actually Does (The 30-Second Version)

LET lets you name a value, or the result of a calculation, and reuse that name inside the same formula — without repeating the calculation itself.

Instead of calculating something over and over, you calculate it once, give it a name, and then just use the name for the rest of the formula. Like giving a nickname to your painfully complicated coworker’s full official job title so you don’t have to say the whole thing every time you talk about them.

Syntax:

LET(name1, value1, name2, value2, ..., calculation)

You can define as many name/value pairs as you want, and the very last argument is always the actual calculation that uses those names. Excel evaluates each named value exactly once, no matter how many times you reference it afterward. That last part is not a small detail — we’ll come back to it, because it’s the difference between a spreadsheet that opens instantly and one that makes your laptop fan sound like it’s preparing for takeoff.

Three things LET gives you that you didn’t have before:

You stop repeating yourself. Write the calculation once. Use the name everywhere else.
Your formulas become readable. Instead of XLOOKUP(A2,B:B,C:C)*XLOOKUP(A2,B:B,D:D)-XLOOKUP(A2,B:B,E:E)*0.1, you get something that reads almost like a sentence.
Your formulas get faster. Repeated calculations are repeated work. LET calculates once and reuses the result, instead of asking Excel to redo the same expensive lookup or calculation multiple times per cell, multiplied across every row in your sheet.

If you’ve ever built a formula so long that it wrapped around your screen three times and you had genuinely lost track of what it was even trying to do — this function was built directly for you, specifically, as a person.

Two Simple Examples, Because We Start Gentle

Example 1: Stop Repeating a Calculation You’ve Already Done

Say you’re calculating a discounted price. The subtotal is in A2, and if it’s over 100, you apply a 10% discount.

Before LET, a normal person writes this:

=IF(A2*1.22>100,(A2*1.22)*0.9,A2*1.22)

Look at that. A2*1.22 — the price with VAT added — appears three times. If your VAT rate ever changes, or you notice a typo, you now have to fix it in three places inside one cell, and if you miss one of them, your spreadsheet will lie to you silently, with total confidence, forever.

With LET:

=LET(
  priceWithVAT, A2*1.22,
  IF(priceWithVAT>100, priceWithVAT*0.9, priceWithVAT)
)

You calculate A2*1.22 exactly once, name it priceWithVAT, and then just talk about priceWithVAT like it’s a normal noun for the rest of the formula. Change the VAT rate once, and every reference updates. This is the whole pitch of LET in miniature: calculate once, name it, move on with your life.

Example 2: Making a Formula Actually Readable

Say you want to calculate someone’s age in years from a birthdate in A2, and add a label depending on whether they’re a minor.

Before LET:

=IF(DATEDIF(A2,TODAY(),"Y")<18, DATEDIF(A2,TODAY(),"Y")&" (minor)", DATEDIF(A2,TODAY(),"Y")&" (adult)")

DATEDIF(A2,TODAY(),"Y") — three times. Same calculation, copy-pasted with tiny variations, which is exactly the kind of formula that looks fine today and becomes an archaeology project in eight months when someone else (or future you, who is legally a different person by then) has to edit it.

With LET:

=LET(
  age, DATEDIF(A2,TODAY(),"Y"),
  IF(age<18, age&" (minor)", age&" (adult)")
)

Nobody needs a computer science degree to read this. age is calculated once, and the formula becomes a sentence you can actually follow: if age is less than 18, say minor, otherwise say adult. This is what “self-documenting” means in practice — the formula explains itself, instead of requiring a private investigator.

💡 Pro Tip: Name your variables like you’d name a variable in real code — age, priceWithVAT, discountRate — not x, y, temp1. LET doesn’t care either way, but the next person opening this file (probably you) absolutely will.

Now the Extremely Complex Examples — Where LET Stops Being “Nice” and Starts Being Necessary

Simple examples are cute. They show you the syntax. They do not show you why professionals building serious workbooks treat LET as close to mandatory. For that, we need formulas that would be genuinely, actively dangerous without it — the kind of formula that, written the old way, becomes an unmaintainable monster that only one person in the office understands, and that person just put in their two weeks’ notice.

Complex Example 1: The Sales Commission Calculator From Hell

The scenario: A sales manager needs to calculate commission for each rep, per deal, with the following rules — because of course there are rules, there are always rules:

Look up the rep’s base commission rate from a Reps table (by rep ID).
Look up the deal size tier bonus from a Tiers table (by deal value, using an approximate match — bigger deals get a bonus percentage on top).
If the deal was closed in the last 30 days, apply a “fast close” bonus of an additional 2%.
The final commission is: (base rate + tier bonus + fast close bonus) × deal value, but capped at a maximum of 25% total rate, because finance set a ceiling and finance’s word is law.

Without LET, here’s what this formula actually looks like when someone tries to write it in one shot:

=IF((XLOOKUP(A2,Reps[ID],Reps[BaseRate])+XLOOKUP(B2,Tiers[Min],Tiers[Bonus],0,-1)+IF(TODAY()-C2<=30,0.02,0))>0.25,0.25,(XLOOKUP(A2,Reps[ID],Reps[BaseRate])+XLOOKUP(B2,Tiers[Min],Tiers[Bonus],0,-1)+IF(TODAY()-C2<=30,0.02,0)))*B2

Read that again. Now read it a third time, because I guarantee you skimmed it the first two times — everybody does, that’s the whole problem. XLOOKUP(A2,Reps[ID],Reps[BaseRate]) appears twice. The entire tier-bonus-plus-fast-close-bonus calculation appears twice, once inside the cap check and once inside the actual result. If finance changes the cap from 25% to 20% next quarter — which finance will absolutely do, without warning, on a Thursday — you now need to hunt down every occurrence of 0.25 across potentially hundreds of formulas, hoping you don’t also accidentally change some unrelated 0.25 that meant something completely different three columns over.

One typo in any one of those four repeated lookups, and the formula still runs. It just quietly returns the wrong commission to the wrong salesperson, who will absolutely notice, and will absolutely bring it up in the next team meeting, and now you’re explaining spreadsheet architecture to someone who wanted to talk about their bonus.

With LET, the exact same logic:

=LET(
  baseRate, XLOOKUP(A2,Reps[ID],Reps[BaseRate]),
  tierBonus, XLOOKUP(B2,Tiers[Min],Tiers[Bonus],0,-1),
  fastCloseBonus, IF(TODAY()-C2<=30,0.02,0),
  totalRate, baseRate+tierBonus+fastCloseBonus,
  cappedRate, MIN(totalRate,0.25),
  cappedRate*B2
)

Every lookup runs exactly once. The 25% cap exists in exactly one place, so when finance moves the goalposts, you change one number. And critically — anyone new to this file can read it top to bottom like a recipe: get the base rate, get the tier bonus, get the fast-close bonus, add them up, cap it, multiply by deal value. That is not a coincidence. That is the entire point of the function.

💡 Pro Tip: Notice cappedRate is built from totalRate, which is built from three other named values — LET lets your named variables reference each other, in order, like a normal chain of reasoning. This is enormously underused and enormously powerful.

Complex Example 2: The Text-Parsing Nightmare That Actually Justifies Its Own Existence

The scenario: You’ve inherited a column of dirty, inconsistent order reference codes from a legacy system, formatted like "ORD-2026-04-88231-EU" — order, year, month, sequence number, region. Someone in operations needs you to extract just the sequence number, but only if the order is from the EU region and was placed in the last two calendar years, otherwise return "N/A". And the codes aren’t perfectly consistent — some have extra spaces, some are lowercase, because of course they are.

Without LET, this is the formula a determined but slightly unhinged person writes at 11pm:

=IF(AND(RIGHT(UPPER(TRIM(A2)),2)="EU",VALUE(MID(UPPER(TRIM(A2)),5,4))>=YEAR(TODAY())-2),VALUE(MID(UPPER(TRIM(A2)),MID(UPPER(TRIM(A2)),1,0)+15,5)),"N/A")

(And realistically it would take several broken attempts to even get this far, because you’d need to calculate the position of the sequence number dynamically, which means nesting a FIND inside a MID inside… you get the idea. This is the formula equivalent of assembling flat-pack furniture using only the picture on the box, with three pieces missing, at midnight.)

UPPER(TRIM(A2)) — the cleaned-up version of the code — needs to happen every single time you touch the string, because you can’t clean it once and refer back to it. You’re cleaning the same messy text three, four, five times inside one formula, which is not just ugly, it’s genuinely wasteful: Excel is redoing the same TRIM and UPPER operations over and over on every single row, across potentially tens of thousands of rows, for no reason except that the language didn’t give you a way to say “do this once.”

With LET:

=LET(
  cleanCode, UPPER(TRIM(A2)),
  region, RIGHT(cleanCode,2),
  orderYear, VALUE(MID(cleanCode,5,4)),
  sequencePosition, FIND("-",cleanCode,FIND("-",cleanCode,FIND("-",cleanCode)+1)+1)+1,
  sequenceNumber, VALUE(MID(cleanCode,sequencePosition,5)),
  isRecentEU, AND(region="EU", orderYear>=YEAR(TODAY())-2),
  IF(isRecentEU, sequenceNumber, "N/A")
)

This is not a shorter formula. It is, however, a formula a human being can actually maintain six months from now without opening it, sighing audibly, and starting over from scratch. cleanCode is computed once and reused four times. Each named step describes exactly what it’s doing — you can trace the logic in plain English just by reading the variable names top to bottom: clean it, get the region, get the year, find where the sequence number starts, extract it, check if it qualifies, return the result or “N/A.”

If operations comes back next month and says “actually we also need to check the APAC region” — you edit one line (isRecentEU) instead of hunting through a 300-character wall of nested MIDs and FINDs trying to figure out which parenthesis belongs to which clause. This is the difference between a formula you can hand off to a colleague and a formula that only survives as long as you personally remain employed at the company.

⚠️ Watch out: In the “without LET” version, one wrong parenthesis anywhere in that soup and Excel gives you a cryptic error — or worse, no error at all, just a silently wrong answer. Untangling which of the five repeated UPPER(TRIM(A2)) instances has the typo is its own special kind of Tuesday. LET doesn’t just make formulas readable — it makes them debuggable, because when something’s wrong, you can check one named step at a time instead of reverse-engineering a novel.

The Performance Angle Nobody Mentions Enough

Here’s the part that turns LET from “nice for readability” into “actually necessary at scale”: every time a value is repeated in a normal formula, Excel recalculates it. Every. Single. Time. Across every row.

In the commission formula above, XLOOKUP(A2,Reps[ID],Reps[BaseRate]) ran twice per cell in the non-LET version. Multiply that across 10,000 rows of sales data, and you’ve just asked Excel to perform 10,000 unnecessary lookups for absolutely no benefit — pure wasted computation, just sitting there making your file slower to open, slower to recalculate, and more likely to freeze at the exact moment you’re trying to demo it live in a meeting.

LET calculates each named value exactly once per row, no matter how many times it’s referenced afterward. On a small sheet, you won’t notice. On a large one — thousands of rows, several LET-worthy formulas, a few expensive lookups — the difference between the repeated version and the LET version can be the difference between a spreadsheet that recalculates instantly and one that makes you stare at the little “Calculating… (4 processors)” message in the status bar like it owes you money.

Pro Tips: The Stuff That Makes LET Actually Click

Combine LET with LAMBDA for reusable custom functions. Once you’re comfortable naming values inside a formula, the natural next step is wrapping a LET formula inside LAMBDA and saving it as a named function in the Name Manager — effectively building your own custom Excel function, with your own name, that you can reuse across the entire workbook. That’s a whole article on its own, but know that it exists and that LET is the gateway drug to it.

You don’t need to use every name in the final calculation. You can define a helper value purely to make an earlier step readable, even if it’s just feeding into another named value rather than the final output. Nobody’s checking your work. Name things for clarity, not economy.

Order matters. Each named value can only reference names defined before it, not after. LET reads top to bottom, like a recipe — you can’t use an ingredient you haven’t measured out yet.

Nest LET inside array formulas and SUM/COUNT patterns. LET plays nicely inside SUMPRODUCT, FILTER, and other array-returning functions, letting you name intermediate arrays instead of recalculating them at every step. This is where LET quietly becomes essential for anyone doing serious dynamic-array work, not just single-cell formulas.

Rename with F2 confidence. If you rename a LET variable, Excel doesn’t auto-update every reference to it the way a real programming environment would — you have to update each usage yourself. It’s not perfect. It’s still enormously better than nothing.

The Bottom Line

LET doesn’t add any new mathematical capability to Excel. It can’t calculate anything you couldn’t already calculate before. What it does is remove the tax you were paying every time you wrote a formula that needed the same value more than once — the tax of repetition, unreadability, wasted recalculation, and formulas so tangled that changing one number meant hunting through four different hiding spots hoping you found them all.

Simple formulas get easier to read. Complex formulas — the ones with nested lookups, layered conditions, and text parsing that would otherwise require a treasure map — go from “technically works, understood by exactly one person, that person is currently on vacation” to “readable by anyone who opens the file, edited safely by changing one line instead of five.”

Next time you catch yourself typing the same XLOOKUP or the same TRIM(UPPER(...)) for the third time inside one formula, stop. Wrap it in LET. Name it something sensible. Thank yourself in six months.

And the next time a colleague hands you a 400-character formula with the same lookup repeated four times and says “I don’t know why it’s so slow” — you’ll know exactly what to tell them.

Shay Stibelman is a digital consultant based in Milan. He helps businesses and educators work better with the digital tools they already have, and has strong, possibly excessive opinions about people who write the same XLOOKUP three times in one cell. He writes at blog.stibelman.com and makes video tutorials for people who’d rather watch someone else’s formula break first.

Coming up next: LET’s cooler, more ambitious sibling — LAMBDA, or how to build your own custom Excel functions without writing a single line of VBA.

Tabelle Pivot: Come Costruirle, Come Romperle (Involontariamente), e Come Ripararle Senza Piangere

Da qualche parte in questo momento, in un ufficio che potrebbe benissimo essere il tuo, c’è qualcuno che sta fissando una tabella pivot rotta.

L’ha costruita venti minuti fa. Funzionava. Era bellissima. Mostrava dati, colori, totali — quella persona si sentiva un professionista di Excel. Un ninja del foglio di calcolo. La persona più intelligente della stanza, o almeno del proprio appartamento in smart working.

E poi ha aggiunto dieci righe di nuovi dati.

E adesso la tabella pivot mostra ancora i numeri di tre mesi fa e fa finta che quei dieci nuovi record non esistano, come un collega che finge di non vederti in corridoio perché non vuole parlarti.

Quella persona non sa cosa è successo. Pensa di aver rotto qualcosa. Sta considerando di chiudere il file senza salvare e far finta che non sia mai esistito.

Questo articolo è per quella persona.

E anche — diciamocelo — per chiunque non abbia ancora mai aperto una tabella pivot e continui a rimandare con quel classico “prima o poi me la guardo.” Prima o poi è adesso. Siediti.

Prima di tutto: perché dovresti preoccuparti delle tabelle pivot

Le tabelle pivot sono considerate una delle cinque competenze fondamentali di Excel. Non una delle cose “carine da sapere se hai tempo.” Non una funzione avanzata per quelli col dottorato in informatica e una tazza con scritto “I ❤️ Data.”

Una competenza fondamentale. Che significa: se non la sai, stai lavorando più del necessario. Sempre.

I forum di supporto Excel sono pieni zeppi di persone che descrivono la stessa storia in tre atti: costruisco la tabella pivot → funziona → aggiungo dati → non funziona più → panico totale. La storia ha sempre lo stesso finale tranquillizzante, e lo vedremo tra poco. Prima, capiamo di cosa stiamo parlando.

Cos’è una tabella pivot (e perché cambia la vita)

Spiegazione in due righe: una tabella pivot è uno strumento che riassume i tuoi dati automaticamente, senza formule, senza calcoli manuali, senza copiare-incollare nulla da nessuna parte.

Hai un foglio con 3.000 righe di dati di vendita — prodotto, data, regione, importo, venditore, categoria, colore della luna quella sera. Una tabella pivot ti permette di chiedere, in pochi secondi e con qualche trascinamento di mouse: “Quanto ha venduto ogni regione nel Q3?” oppure “Qual è il prodotto più venduto per categoria?” oppure “Chi è il venditore che porta a casa di più?” — e ottieni la risposta istantaneamente, in un formato leggibile, aggiornabile, e filtrabile.

Senza scrivere una sola formula.

Se stai pensando “ma io faccio già queste cose con SOMMA.SE e CERCA.VERT” — sì, lo so. Le fai. E ci metti tre ore invece di tre minuti, e ogni volta che cambia qualcosa devi ricominciare daccapo. Non giudicare. Anche io ho vissuto quel periodo. Non era un bel periodo.

I tuoi dati devono essere in ordine. Prima ancora di toccare la tabella pivot.

Qui si sbaglia in tanti e si capisce perché: si vuole arrivare subito alla parte interessante. Ma i dati sporchi producono tabelle pivot storte, e una tabella pivot storta è peggio di nessuna tabella pivot.

Tre regole d’oro da seguire prima di fare qualsiasi cosa:

Regola 1: Ogni colonna deve avere un’intestazione.

Prima riga = nomi delle colonne. “Vendite”, “Regione”, “Data”, “Prodotto”, “Venditore”. Se la prima riga contiene già dati invece di intestazioni, la tabella pivot si confonde — e tu ti confonderai insieme a lei.

Regola 2: Nessuna riga o colonna vuota nel mezzo dei dati.

Excel legge il tuo dataset come un blocco continuo. Una riga vuota a metà è come uno strappo nel tessuto: tutto quello che c’è dopo viene ignorato. Le righe vuote sono un tradimento silenzioso. Eliminale.

Regola 3: Coerenza nei formati.

Se una colonna contiene date, devono essere tutte date — non alcune date e alcune stringhe di testo che si vedono come date ma tecnicamente non lo sono. Se una colonna contiene numeri, devono essere numeri, non numeri formattati come testo (che Excel tratta come sassi, non come valori). Controlla. Sempre.

Come costruire una tabella pivot in 60 secondi

Dato per scontato che i dati siano puliti — ed è un assunto importante, tienitelo a mente — ecco la procedura completa:

Clicca in un punto qualsiasi all’interno dei tuoi dati. Non sull’intestazione, non su una cella vuota. Da qualche parte dentro il dataset.
Vai alla scheda Inserisci nella barra in alto.
Clicca su “Tabella pivot”. Compare una finestra di dialogo. Excel ha già selezionato automaticamente il tuo intervallo di dati — verificalo con un occhio. Se è corretto, perfetto.
Scegli dove mettere la tabella pivot. “Nuovo foglio di lavoro” è quasi sempre la scelta giusta. Tienila separata dai dati originali. Clicca OK.
Sei dentro. Un nuovo foglio si apre con una tabella pivot vuota a sinistra e il pannello dei campi a destra.

Quella tabella vuota che ti fissa non è un errore. È normale. Adesso la riempi tu.

Il Pannello dei Campi: l’interfaccia che sembra complicata e non lo è

Sul lato destro dello schermo hai il pannello “Campi tabella pivot”. In alto, una lista di tutte le intestazioni del tuo dataset. In basso, quattro aree: Filtri, Colonne, Righe, Valori.

La logica è semplice:

Righe → come vuoi raggruppare i dati? Per regione? Per prodotto? Per venditore? Per mese?
Valori → cosa vuoi misurare? Totale vendite? Quantità? Media degli importi?
Colonne → se vuoi una seconda dimensione di raggruppamento (es. mesi in orizzontale e regioni in verticale)
Filtri → per filtrare l’intera tabella su una variabile (es. mostrami solo i dati dell’anno corrente)

In pratica: trascini “Regione” in Righe e “Vendite” in Valori. In meno di due secondi Excel ti mostra il totale vendite per ogni regione. Fatto. Quello è l’output che prima avresti costruito con SOMMA.SE su ogni singola regione, manualmente, una per una.

Pro tip: Per i Valori, Excel di default fa sempre la somma. Se vuoi la media, il conteggio, il massimo o il minimo — clicca sulla freccia accanto al campo in “Valori”, seleziona “Impostazioni campo valore” e scegli il calcolo che ti serve. Questo è il dettaglio che la metà delle persone non sa e che cambia completamente quello che puoi fare con una tabella pivot.

La parte dove tutto va storto (e dove tutti vanno nel panico)

Bene. Tabella pivot costruita. Funziona. I numeri sono corretti, il capo è contento, tu ti senti bene con te stesso.

E poi succede una di queste cose:

Arrivano nuovi dati da aggiungere al dataset
Qualcuno corregge dei valori nel foglio sorgente
Aggiungi nuove colonne al dataset originale

Torni alla tabella pivot.

I numeri non sono cambiati.

I nuovi dati non ci sono. Le correzioni non si vedono. Le nuove colonne non compaiono nel pannello dei campi. La tabella pivot è ferma nel tempo come una fotografia, completamente ignara di tutto quello che è successo nel foglio accanto.

Panico. Confusione. “Ho rotto tutto.”

Non hai rotto niente. Questo è esattamente il comportamento corretto — e frustrante — di una tabella pivot.

Perché succede (la spiegazione di 30 secondi)

Una tabella pivot è uno snapshot. Quando la costruisci, legge i dati in quel momento esatto e li ricorda. Da quel momento in poi, non guarda più il foglio sorgente — si tiene i dati che ha già.

È come fare una fotografia. La foto mostra com’era la stanza quel giorno. Se poi sposti i mobili, la foto non cambia.

Per aggiornare la tabella pivot, devi dircelo esplicitamente. Non lo fa da sola. Non c’è un modo per farla “connettere in tempo reale” senza strumenti aggiuntivi. Devi aggiornarla manualmente ogni volta che i dati cambiano.

La buona notizia: farlo richiede esattamente due secondi.

Il fix: clic destro → Aggiorna

Questa è la cosa più semplice del mondo e la cosa che il 70% delle persone non sa fare quando incontra il problema per la prima volta:

Clicca con il tasto destro in qualsiasi punto all’interno della tabella pivot → seleziona “Aggiorna”.

Fatto. La tabella pivot rilegge i dati, incorpora tutto quello che è cambiato, e si aggiorna istantaneamente.

Alcune persone mettono un post-it sul monitor con scritto “AGGIORNA DOPO OGNI MODIFICA.” Non è triste. È saggezza operativa.

Pro tip: Se hai più tabelle pivot nello stesso file, puoi aggiornare tutte contemporaneamente. Vai alla scheda Analizza tabella pivot (compare quando sei dentro la tabella pivot) → clicca sulla freccia sotto “Aggiorna” → seleziona “Aggiorna tutto.” Una mossa sola, tutto aggiornato.

Il problema più sottile: i nuovi dati fuori dall’intervallo

Aggiornare risolve il problema delle modifiche ai dati esistenti. Ma c’è un caso più insidioso che fa impazzire le persone: le nuove righe aggiunte in fondo al dataset.

Ecco cosa succede: la tua tabella pivot è stata costruita quando il dataset aveva, diciamo, 500 righe. Excel ha memorizzato “leggi dall’A1 alla G500.” Adesso hai aggiunto altre 50 righe e il dataset va fino alla riga 550.

Aggiorni la tabella pivot.

Le prime 500 righe vengono rilette correttamente. Le 50 nuove? Fuori dall’intervallo originale. Ignorate. Come se non esistessero.

Risultato: tabella pivot aggiornata ma incompleta. I numeri cambiano un po’ (per le modifiche ai dati esistenti) ma i nuovi record non ci sono. E se non sai cercare questa cosa, è quasi impossibile da diagnosticare.

La vera soluzione: Tabella Excel + tabella pivot

Questo è il setup professionale. Quello che usano le persone che non vogliono passare la vita a cliccare e a chiedersi perché i numeri non tornano.

Prima di costruire la tabella pivot, converti il tuo dataset in una Tabella Excel (con la T maiuscola, perché in Excel “tabella” con la minuscola può significare qualsiasi cosa, mentre “Tabella” con la maiuscola è un oggetto specifico con comportamenti specifici).

Come si fa:

Clicca in un punto qualsiasi nel tuo dataset
Premi Ctrl + T (oppure vai su Inserisci → Tabella)
Verifica che “Tabella con intestazioni” sia selezionato
Clicca OK

Il tuo dataset si trasforma: appare una formattazione a righe alternate, compare una freccia di filtro su ogni intestazione, e in alto a sinistra nella barra della formula compare il nome della tabella (di solito “Tabella1”, che puoi rinominare nel pannello di design).

Perché questo cambia tutto: una Tabella Excel è dinamica. Quando aggiungi righe in fondo, si espande automaticamente e include quelle righe nel suo intervallo. Non devi ridefinire niente. La Tabella cresce con i dati.

E poiché la tua tabella pivot è costruita sopra una Tabella Excel invece che sopra un intervallo statico, quando aggiorni la tabella pivot, legge l’intera Tabella — incluse tutte le righe aggiunte dopo la creazione.

Devi ancora ricordarti di aggiornare. Ma il problema delle righe tagliate fuori dall’intervallo sparisce completamente.

Tabella Excel → tabella pivot. Questa è la configurazione che non si rompe.

Pro Tips: le cose che nessuno ti dice all’inizio

Rinomina i tuoi campi nei Valori. Quando trascini “Vendite” in Valori, Excel lo chiama “Somma di Vendite.” Brutto, poco professionale, inutilmente verboso. Clicca sulla cella con quell’intestazione nella tabella pivot e rinominala come vuoi — “Fatturato Totale”, “Vendite Q3”, quello che ti serve. Il campo sottostante non cambia. Solo il nome visualizzato.

Usa i filtri dei report per creare viste dinamiche. Trascina un campo in “Filtri” e compare un menu a tendina sopra la tabella pivot. Puoi filtrare l’intera tabella su quel campo — per anno, per categoria, per area geografica — con un solo clic. Vuoi una versione per ogni regione? Vai su Analizza → Opzioni → Mostra pagine filtro di report. Excel crea un foglio separato per ogni valore del filtro, automaticamente. Questo è il tipo di cosa che fa sembrare molto organizzati senza esserlo davvero.

Il doppio clic che nessuno conosce. Fai doppio clic su qualsiasi numero in una tabella pivot. Excel apre un nuovo foglio con tutte le righe del dataset originale che hanno contribuito a quel numero. Non devi filtrare niente, non devi cercare niente. Doppio clic, e vedi la fonte. Questa funzione si chiama “drill-down” e cambia completamente il modo in cui si usa una tabella pivot per analizzare i dati.

Aggiorna automaticamente all’apertura del file. Clicca con il tasto destro sulla tabella pivot → Opzioni tabella pivot → scheda Dati → spunta “Aggiorna dati all’apertura del file.” Da quel momento, ogni volta che apri il file, la tabella pivot si aggiorna da sola. Per chi lavora con file collegati a database o con dati che cambiano frequentemente, questo è oro.

Cambia il calcolo nei Valori. Di default: somma. Ma puoi avere conteggio, media, massimo, minimo, percentuale del totale, percentuale della riga, percentuale della colonna, differenza rispetto a un valore precedente. Clicca sulla freccia del campo in “Valori” → Impostazioni campo valore → esplora. La maggior parte delle persone usa le tabelle pivot al 20% delle loro capacità perché non sa che questa schermata esiste.

Slicer: filtri visivi che sembrano professionali. Vai su Analizza tabella pivot → Inserisci filtro dati. Compaiono dei pulsanti grandi e cliccabili per filtrare la tabella pivot per categoria, regione, periodo, qualsiasi campo tu scelga. Sono visivamente chiari, intuitivi, e rendono qualsiasi dashboard immediatamente più leggibile. Se devi presentare dati a qualcuno che Excel non lo conosce bene, gli Slicer fanno fare a te una figura molto migliore di quella che meriti.

Il checklist di sopravvivenza

Prima di costruire una tabella pivot, rispondi sì a tutte queste:

[ ] Ogni colonna ha un’intestazione unica nella prima riga?
[ ] Non ci sono righe o colonne vuote nel mezzo del dataset?
[ ] I dati numerici sono effettivamente in formato numerico (non testo)?
[ ] Le date sono effettivamente in formato data?
[ ] Ho convertito il dataset in Tabella Excel con Ctrl+T?

Se hai risposto sì a tutto: vai. Se no: sistema prima. La tabella pivot ti ringrazierà. Tu ti ringrazierai.

In sintesi (perché è quello che facciamo qui)

Le tabelle pivot riassumono i tuoi dati senza formule. Si costruiscono in meno di un minuto. Non si aggiornano da sole — devi fare clic destro → Aggiorna ogni volta che i dati cambiano. Se aggiungi nuove righe al dataset, converti prima i dati in Tabella Excel con Ctrl+T: in questo modo l’intervallo si espande automaticamente e non perdi righe.

Quello che ti sembrava uno strumento misterioso riservato ai maghi di Excel è in realtà cinque clic e un trascinamento di mouse.

Vai a costruire la tua prima tabella pivot. Aggiungi dei dati. Guarda come non si aggiorna. Fai clic destro, clicca Aggiorna, e senti quella piccola soddisfazione silenziosa di capire come funziona qualcosa che prima ti sembrava oscuro.

E la prossima volta che un collega dice “non capisco niente di Excel” — puoi aiutarlo. Oppure annuire, tornare alla tua scrivania, e sentirti silenziosamente superiore per il resto della giornata.

Entrambe le opzioni sono valide.

Guarda il video che spiega un po’ tutto, un po’ velocemente e fa un po’ ridere.

Shay Stibelman è un consulente di marketing digitale basato a Milano. Aiuta aziende e professionisti a lavorare meglio con gli strumenti digitali che già hanno — o che dovrebbero avere da un pezzo. Scrive su blog.stibelman.com e realizza video tutorial per chi preferisce vedere qualcun altro sbagliare per primo. Strategia del tutto legittima.

Prossimamente: le tabelle pivot nell’era di Copilot — cosa può fare l’AI al posto tuo e cosa, francamente, è meglio che tu continui a fare da solo.

A Deep Dive Into Excel’s Time and Date Functions

Because time is money, and you’re currently losing both by not knowing these.

Let me paint you a picture.

It’s Monday morning. You have a spreadsheet. The spreadsheet has dates in it. Someone — let’s call them “Past You” — has entered those dates in seventeen different formats, across three different columns, using a mix of text strings, actual date values, and what appears to be a cry for help disguised as a number.

You need to calculate how many days until a project deadline. You need to extract the month. You need to figure out whether a delivery happened on a weekend. And you need all of this before the 9am standup, which is in four minutes.

This is the moment Excel’s date and time functions were born for. And yet — most people know maybe three of them.

Not you. Not anymore. By the end of this article, you’re going to know all of them, how they work, when to use them, and why someone who doesn’t know them is basically navigating a city without GPS and too proud to ask for directions.

Let’s go.

First, A Two-Minute Explanation That Will Save You Hours

Before we dive in, here’s the thing most people never explain about dates in Excel: Excel doesn’t actually store dates as dates.

Seriously. Under the hood, every date is just a number. January 1, 1900 = 1. January 2, 1900 = 2. Today (April 16, 2026) = some number in the 46,000s. Excel just formats those numbers to look like dates, which is very considerate of it.

Why does this matter? Because once you understand that dates are numbers, everything about date functions suddenly makes logical sense. Adding 7 to a date gives you next week. Subtracting two dates gives you the number of days between them. The formula isn’t doing anything mystical — it’s just arithmetic with a nice coat of paint.

Times work the same way, but as decimal fractions of a day. Noon (12:00) = 0.5. 6am = 0.25. Midnight = 0. Got it? Good. Now let’s talk functions.

The “What Day/Time Is It Right Now?” Functions

TODAY()

What it does: Returns today’s date. Recalculates every time the file opens or refreshes.

Syntax: =TODAY()

Real-life example: You’re tracking a project deadline in B2 (let’s say it’s May 30, 2026). In C2 you put =B2-TODAY() and now you always know, instantly and without doing math in your head, exactly how many days you have left. Which might be motivating. Or terrifying. Either way, accurate.

💡 Pro Tip: TODAY() is “volatile” — it recalculates constantly. If you need a permanent timestamp of when something was entered, use Ctrl+; instead, which hard-codes the date and never changes.

NOW()

What it does: Returns the current date and time down to the minute. Also recalculates every time the sheet refreshes.

Syntax: =NOW()

Real-life example: You’re building a log of customer support tickets. Every time a new ticket is submitted, you want to record the exact timestamp. =NOW() in the timestamp column gives you something like “16/04/2026 09:47” — date and time, together, in one cell. Extremely useful when “when was this submitted?” is a question that leads to arguments.

💡 Pro Tip: NOW() returns a number with decimals. If you subtract NOW() from a future date and you get a weird decimal, that’s because it’s including the fractional time of day. Use INT(NOW()) to strip the time and get just today’s date as a whole number.

The “Build a Date From Parts” Functions

DATE(year, month, day)

What it does: Constructs a proper Excel date from three separate numbers — a year, a month, and a day. This is your rescue function when someone has helpfully stored the year in column A, the month in column B, and the day in column C.

Real-life example: You have an export from an ancient database system. Column A says “2026”, column B says “4”, column C says “16”. None of it is usable as a date yet. =DATE(A2,B2,C2) stitches them together into April 16, 2026 — a real, actual Excel date you can calculate with.

💡 Pro Tip: DATE() handles overflow beautifully. =DATE(2026,13,1) doesn’t give you an error — it gives you January 1, 2027, because month 13 just rolls into the next year. This is wildly useful for adding months to dates without weird edge cases.

TIME(hour, minute, second)

What it does: Same concept as DATE(), but for time. Constructs an Excel time value from separate hour, minute, and second components.

Real-life example: You’re managing shift schedules. In your system, start times come in as three separate columns: 9 (hour), 30 (minute), 0 (second). =TIME(A2,B2,C2) combines them into 9:30:00 AM — a real time value you can then use for duration calculations, comparisons, and scheduling logic.

💡 Pro Tip: Need to add 90 minutes to a time? Don’t add 90. Add TIME(0,90,0). Excel will handle the rollover correctly. Adding the raw number 90 will add 90 days, not minutes, and you’ll have a very confusing schedule.

The “Tear a Date Apart” Functions

YEAR(date)

What it does: Extracts just the year from a date. Returns a four-digit number.

Real-life example: You have a column of transaction dates going back five years and you want to create a pivot table by year. Add a helper column with =YEAR(A2) and suddenly you have a clean “Year” field to group on. Simple, extremely useful, used approximately nine million times a day in spreadsheets around the world.

MONTH(date)

What it does: Extracts the month number (1 through 12) from a date.

Real-life example: Your sales report needs a “Month” column for filtering. =MONTH(A2) gives you a number. Combine with =TEXT(A2,"MMMM") if you want the actual month name instead. Report looks good, boss is happy, you leave on time. This is the dream.

💡 Pro Tip: To convert a month number back to a name, use =TEXT(DATE(2000,A2,1),"MMMM"). Classic Excel party trick.

DAY(date)

What it does: Extracts the day of the month (1 through 31) from a date.

Real-life example: You need to flag all invoices issued on the last day of the month. =DAY(A2)=DAY(EOMONTH(A2,0)) — yes, we’ll get to EOMONTH shortly — checks whether the day of an invoice date equals the last day of its month. Month-end billing logic, solved.

HOUR(time), MINUTE(time), SECOND(time)

What they do: Extract the hour, minute, or second from a time value, respectively.

Real-life example (all three): You’re analyzing customer call durations. The start time is in A2, end time in B2, and the duration is in C2 as a time value. Now you need this in a readable format. =HOUR(C2)&"h "&MINUTE(C2)&"m "&SECOND(C2)&"s" turns 0:47:23 into “0h 47m 23s.” Your operations manager will use words like “professional” and “organised.” You will nod calmly.

The “What Day of the Week Is This?” Functions

WEEKDAY(date, [return_type])

What it does: Returns a number representing the day of the week for a given date. The return_type argument controls the numbering system (1=Sunday through Saturday, 2=Monday through Sunday, etc.). Default is 1.

Real-life example: You’re scheduling automated reports and need to make sure nothing sends on a weekend. =WEEKDAY(A2,2) returns 6 for Saturday and 7 for Sunday (Monday-based numbering). Wrap it in an IF: =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") and you’ve got an instant flag. No more reports going out on a Saturday to an inbox nobody checks.

💡 Pro Tip: Use return_type = 2 (Monday = 1, Sunday = 7) if you’re in Europe or working on business logic that treats Monday as the start of the week. This avoids a lot of off-by-one headaches.

WEEKNUM(date, [return_type])

What it does: Returns the week number of the year for a given date. Week 1 is the week containing January 1st. The return_type argument controls which day the week starts on.

Real-life example: You’re reporting on weekly sales performance and your stakeholders reference “Week 15” and “Week 28” instead of actual dates (because they’re creatures of habit and you’ve accepted this). =WEEKNUM(A2) gives you the ISO week number, which you can use to group, sort, and slice your data by week without ever writing a formula that involves seven nested IFs. Everyone who has written seven nested IFs to solve this problem knows the pain being avoided here.

ISOWEEKNUM(date)

What it does: Returns the ISO week number — the international standard used in Europe and most professional business contexts. ISO weeks always start on Monday and the first week of the year is the one containing the first Thursday.

Real-life example: You’re working with a European partner who refers to “KW16” (Kalenderwoche 16 — German for calendar week 16). =ISOWEEKNUM(A2) gets you on the same page without having to explain to your German colleague why your week numbers are off by one. Cross-border collaboration, sorted.

The “How Much Time Has Passed?” Functions

DATEDIF(start_date, end_date, unit)

What it does: Calculates the difference between two dates in a specified unit: “Y” for years, “M” for months, “D” for days, “YM” for months excluding years, “MD” for days excluding months, “YD” for days excluding years.

This function is technically undocumented by Microsoft. It exists, it works perfectly, but Microsoft didn’t include it in their official function list for mysterious reasons that nobody has ever fully explained. It’s the Excel equivalent of a secret room.

Real-life example: You’re calculating employee tenure for an HR report. =DATEDIF(A2,TODAY(),"Y") gives you complete years of service. Want to be precise? =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months" gives you “3 years, 7 months.” The kind of thing that makes an HR system look polished and thorough. Very satisfying.

💡 Pro Tip: Always make sure end_date is greater than start_date. DATEDIF throws an error if the dates are the wrong way around and, unlike most Excel errors, it is not subtle about it.

DAYS(end_date, start_date)

What it does: Returns the number of days between two dates. Cleaner than subtraction for some scenarios.

Real-life example: Subscription renewal tracking. Column A has sign-up dates, column B has today’s date. =DAYS(B2,A2) gives you subscription age in days. When this hits 365, you know it’s renewal time. Simple, readable, does exactly one thing.

DAYS360(start_date, end_date, [method])

What it does: Calculates days between two dates based on a 360-day year (twelve 30-day months). This is a financial standard, not a mistake.

Real-life example: You’re working in accounting and calculating interest on a loan or bond using the 30/360 day count convention that the financial industry uses. =DAYS360(A2,B2) gives you the “banking days” between two dates. If someone in finance handed you a spreadsheet using this function and you thought it was a typo — it wasn’t.

💡 Pro Tip: The optional method argument is for US (FALSE) vs. European (TRUE) day count conventions. Yes, there are two. Yes, this matters in international financial documents. Yes, getting them mixed up is awkward.

NETWORKDAYS(start_date, end_date, [holidays])

What it does: Returns the number of working days (Monday–Friday) between two dates. You can optionally pass a range of holiday dates to exclude those too.

Real-life example: Project management gold. Client signs off on a project on April 16. The deliverable is due May 16. =NETWORKDAYS(A2,B2,HolidayRange) tells you how many actual working days you have, excluding weekends and any public holidays you’ve listed. This number will either reassure you or cause a mild crisis. Either outcome is useful information.

💡 Pro Tip: Build a dedicated “Holidays” named range in your workbook and reference it in every NETWORKDAYS formula. When holidays change next year, you update one list and every formula updates automatically. This is the kind of forward-thinking that makes colleagues ask if you took a course somewhere.

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

What it does: Same as NETWORKDAYS but lets you define which days are the “weekend.” Because not everyone works a Monday–Friday schedule, and Excel knows this.

Real-life example: You’re calculating delivery times for a logistics company where the drivers work Tuesday–Saturday and have Sunday–Monday off. =NETWORKDAYS.INTL(A2,B2,2,HolidayRange) with weekend code “2” means Monday and Tuesday are treated as non-working days. No more manually adjusting day counts because the formula assumed a Western workweek that your operations don’t follow.

💡 Pro Tip: The weekend argument accepts both number codes (1–7, 11–17) and a 7-character string like “1000001” where each character represents Mon–Sun and “1” means non-working day. The string method is more flexible for unusual schedules.

WORKDAY(start_date, days, [holidays])

What it does: Returns the date that is a certain number of working days after (or before, with a negative number) a start date. Skips weekends automatically.

Real-life example: An order is placed on April 16. Your SLA promises delivery within 5 business days. =WORKDAY(A2,5,HolidayRange) returns April 23 — which is the correct delivery deadline after skipping the weekend. Much better than telling a customer their order arrives “in 5 days” and then having them show up on Saturday to collect it.

WORKDAY.INTL(start_date, days, [weekend], [holidays])

What it does: WORKDAY with the same custom weekend flexibility as NETWORKDAYS.INTL.

Real-life example: Same logistics scenario as above, but now you’re calculating the dispatch date by counting backwards. =WORKDAY.INTL(DeadlineDate,-3,2) tells you: if this needs to arrive by X, we need to ship by Y, on a Tuesday-Saturday schedule. Operations managers tend to love people who think this way.

The “Find The Start or End of a Period” Functions

EOMONTH(start_date, months)

What it does: Returns the last day of the month that is a specified number of months before or after a date. Months = 0 gives you the last day of the current month.

Real-life example: You’re calculating invoice due dates set to the last day of the month following the billing date. =EOMONTH(A2,1) returns the last day of the month after any given billing date. January invoice? Due February 28 (or 29 in a leap year — Excel handles this correctly without you doing anything heroic). This alone is worth the price of knowing EOMONTH.

💡 Pro Tip: EOMONTH(A2,0)+1 gives you the first day of the next month. Because the day after the last day of a month is always the first day of the next one. Simple math, endlessly useful.

EDATE(start_date, months)

What it does: Returns the date that is exactly a specified number of months before or after a given date. Unlike adding 30, this respects the actual calendar.

Real-life example: Subscription renewals. Customer signs up on January 31. One month later is… February 28 (or 29). Not March 2. =EDATE(A2,1) handles this correctly where =A2+30 does not. If your subscription billing is off by a few days every month, there’s a good chance someone used A2+30 somewhere. Go find it.

💡 Pro Tip: Use negative values to go backwards in time. =EDATE(A2,-6) gives you the date six months before A2. Useful for calculating notice periods, lookback windows, and “when should we have started worrying about this.”

The “Convert Text to Actual Dates” Functions

DATEVALUE(date_text)

What it does: Converts a date stored as text into a proper Excel serial number (real date). Essential when importing data from systems that export dates as strings.

Real-life example: Your CRM export gives you dates like “April 16, 2026” stored as text. Excel won’t calculate with them. =DATEVALUE("April 16, 2026") converts it into a real date value you can actually use. Then format the cell as a date, and you’re back in business.

💡 Pro Tip: If your text dates are in a regional format Excel doesn’t recognize, DATEVALUE may give you an error. This is Excel’s polite way of saying “I have no idea what 16/04/2026 means in your locale.” In that case, combine MID, LEFT, RIGHT, and DATE to manually parse the components. Not glamorous, but effective.

TIMEVALUE(time_text)

What it does: Converts a time stored as text into an Excel decimal time value.

Real-life example: Your helpdesk system exports call times as text strings like “09:47:00 AM.” =TIMEVALUE("09:47:00 AM") returns 0.406944… which Excel recognizes as a time and can calculate with. Now you can compute average call duration, flag calls over a certain length, and build actual operational reporting instead of just staring at a column of text.

The “Format and Display” Functions

TEXT(value, format_text)

What it does: Converts any number or date into a text string with a specified format. Technically a general-purpose function, but utterly indispensable for dates.

Real-life example: You’re building an automated email subject line in a formula. You want it to say “Weekly Report – Week 16, April 2026.” ="Weekly Report – Week "&WEEKNUM(TODAY())&", "&TEXT(TODAY(),"MMMM YYYY") produces exactly that, updating itself every week. Your reports look like they were crafted by hand. They were not. That’s the whole point.

💡 Pro Tip: Common date format codes — “D” = day number, “DD” = zero-padded day, “DDD” = Mon/Tue/Wed, “DDDD” = Monday/Tuesday…, “MMM” = Jan/Feb/Mar, “MMMM” = January/February…, “YYYY” = four-digit year. Combine freely. Format to taste.

The “Rounding Times” Functions

FLOOR(number, significance) and CEILING(number, significance)

What they do: Round a time (or date) down (FLOOR) or up (CEILING) to the nearest specified interval.

Real-life example: You’re calculating billable hours and your company bills in 15-minute increments. A call lasts 47 minutes. Rounded up to the nearest quarter hour: =CEILING(A2,TIME(0,15,0)) gives you 1:00 — one hour billed. Rounded down: =FLOOR(A2,TIME(0,15,0)) gives you 0:45. Your billing system is now as pedantic as your accountant. That’s a feature.

MROUND(number, multiple)

What it does: Rounds a number to the nearest specified multiple. Works on times too.

Real-life example: You want to round all meeting times to the nearest 30-minute slot for a cleaner schedule view. =MROUND(A2,TIME(0,30,0)) rounds 10:17 to 10:00 and rounds 10:22 to 10:30. Schedule looks neat, everybody thinks you planned it this way. You absolutely did not plan it this way.

The Less-Famous Ones (That Are Secretly Brilliant)

YEARFRAC(start_date, end_date, [basis])

What it does: Returns the fraction of a year between two dates. The basis argument controls the day-count convention (actual/actual, 30/360, etc.).

Real-life example: You need to calculate pro-rated annual fees for a service contract that started mid-year. =YEARFRAC(ContractStart,ContractEnd,1) gives you something like 0.623 — meaning 62.3% of the year was covered. Multiply by the annual fee and you have the exact pro-rated amount. Finance teams use this constantly. Most people have never heard of it.

DATESTRING(date)

What it does: Converts a date to a text string in the system locale format. Note: this is a legacy function and availability varies by Excel version.

Real-life example: Mostly used in older workbooks or specific regional output requirements. You’re better off using =TEXT(A2,"DD/MM/YYYY") and controlling the format yourself. But if you encounter DATESTRING in someone else’s spreadsheet, now you know what it does instead of quietly panicking.

Putting It All Together: The Formula That Impresses Everyone

Want a formula that shows off everything? Here’s one for a project status dashboard.

Assume A2 is a project start date, B2 is a planned end date, and today is TODAY().

="Project is "&DATEDIF(A2,TODAY(),"D")&" days in. "&NETWORKDAYS(TODAY(),B2)&" working days remaining, due "&TEXT(B2,"DDDD, MMMM D")&IF(WEEKDAY(B2,2)>5," (⚠️ deadline falls on a weekend!)",".")

This one formula tells you how long the project has been running, how many working days are left, the deadline in plain English, and flags it if the deadline lands on a weekend. It updates every day automatically.

Is it a bit much for a Monday morning? Possibly. Does it make you look like an Excel wizard? Absolutely.

The Bottom Line

Excel’s date and time functions aren’t just a list of things to memorize. They’re a toolkit for turning raw timestamps into actual, actionable information — deadlines, durations, schedules, billing, planning, reporting.

Most people use three of them. Today you learned all of them. That gap between “most people” and you? That’s your edge.

Now go open a spreadsheet and do something useful with it. The deadline is closer than you think — and thanks to NETWORKDAYS, you now know exactly how much closer.

Here’s a cheatsheet for your troubles: https://blog.stibelman.com/excel-date-time-functions-cheatsheet/

Shay Stibelman is a digital consultant based in Milan, Italy. He helps businesses work smarter with the tools they already pay for — and occasionally writes 4,000-word articles about Excel dates because someone has to, and he has a thing about people losing time to easily solvable problems.

VLookup and XLookup – The definitive guide

Everything you ever wanted to know, explained in plain human terms, with real examples, and without making you cry in front of an Excel spreadsheet.

Every year, millions of people open Excel, stare at two separate tables, and think: “there must be a way to connect these things without copying them one by one.” There is. It’s called VLOOKUP. And for a few years now there’s also XLOOKUP, which does the same thing but without the embarrassing flaws. This is the guide nobody ever made you read before leaving you alone with a #N/A error and a ruined Tuesday afternoon.

VLOOKUP: the classic that never goes out of style (but throws tantrums)

VLOOKUP stands for Vertical Lookup. In practice: you give it a value, it goes and finds it in a column of a table, and returns the corresponding value from another column in the same row. Like a waiter who looks up your name on the reservation list and tells you which table you’re sitting at.

Simple in theory. In practice, as we’ll see, that waiter sometimes says #N/A and disappears into the kitchen.

The full syntax

VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])

Parameter	What it does	Example
lookup_value – required	The value you want to look up. Can be text, a number, or a cell reference.	`A2` (the cell containing the employee code)
table_array – required	The table to search in. Important: the lookup column MUST be the first column (leftmost) of this selection.	`B2:E500` or `HRTable[#All]`
col_index_num – required	The column number within the table from which to return the result. If the table has 4 columns and you want the third, type 3.	`3` (returns the value from the 3rd column)
range_lookup – optional	`FALSE` = exact match (almost always what you want). `TRUE` = approximate match (useful for brackets, e.g. taxes, score ranges). Default: `TRUE`, so always type FALSE if you’re not sure.	`FALSE`

Classic trap

The range_lookup parameter defaults to TRUE. This means that if you forget it, Excel will perform an approximate match. On unsorted data, this produces wrong results without warning you. No error. Silently. Like a colleague who misunderstood the instructions but won’t admit it.

Basic VLOOKUP example

VLOOKUP(A2; SalaryTable; 3; FALSE)
→ Looks up the code in A2 in the first column of SalaryTable, returns the value from the 3rd column

Pro Tip

Always use structured table references (Table1[Column]) instead of hard-coded ranges (B2:E500). If you add rows to the table, the formula updates automatically. If you use a hard-coded range, you’ll have to remember to update it manually — and you will definitely forget at the worst possible moment.

XLOOKUP: the younger sibling who turned out better

XLOOKUP arrived in 2019 with Microsoft 365 and fixed virtually all of VLOOKUP’s structural problems. It’s not just “a newer VLOOKUP” — it’s a completely redesigned function that works differently, more flexibly, and more intuitively.

If VLOOKUP is the waiter who searches the list from top to bottom and can only look to the right, XLOOKUP is the maître d’ who can look anywhere, tell you if you’re not on the list without making a scene, and even search starting from the end.

The full syntax

XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])

Parameter	What it does	Example
lookup_value – required	The value you want to find. Identical to VLOOKUP.	`A2`
lookup_array – required	The column (or row) to search in. Doesn’t have to be the first one. Can be any column in the table.	`ClientsTable[ID]`
return_array – required	The column (or row) to return the result from. Completely separate from the lookup column. Can be to the left, to the right, anywhere.	`ClientsTable[ClientName]`
if_not_found – optional	What to return if the value is not found. If omitted, returns #N/A. With this parameter, you can return custom text, zero, or anything else.	`"Not found"` or `0`
match_mode – optional	`0` = exact match (default). `-1` = exact or next smaller. `1` = exact or next larger. `2` = with wildcard characters (*, ?).	`0`
search_mode – optional	`1` = first to last row (default). `-1` = last to first (useful if you want the most recent occurrence). `2` or `-2` = binary search (on sorted data, much faster).	`-1` (to find the most recent row)

Pro Tip

The if_not_found parameter alone is worth switching to XLOOKUP. The old IFERROR(VLOOKUP(...); "Not found") is verbose and repeats the formula twice. With XLOOKUP you write it once, clean, readable — and your colleagues will look at you with respect.

XLOOKUP(A2; ClientsTable[ID]; ClientsTable[ClientName]; "Client not registered")
→ Looks up A2 in the ID column, returns the ClientName. If not found: "Client not registered"

When to use one and when to use the other: 4 real examples

The short answer: always use XLOOKUP if your Excel supports it. But life is complicated, colleagues use older versions of Office, and sometimes there are legitimate reasons why VLOOKUP is still the right choice. Here they are.

VLOOKUP · Limitations

The lookup column must be the first one (leftmost)
References columns by number, not by name
No native error handling
Cannot search right to left
Cannot find the last occurrence

XLOOKUP · Advantages

Search in any column, return from any column
Uses column names, not arbitrary numbers
Built-in error handling with if_not_found
Searches in both directions
Finds the last occurrence with search mode -1

Example 1 · Library · VLOOKUP wins
File shared with colleagues on Excel 2016

The librarian has a catalogue with 8,000 books: ISBN code in column A, title in B, author in C, section in D. She needs to create a file that her part-time colleague — who uses Excel 2016 on the desktop computer in the basement — can open and use without errors.

XLOOKUP doesn’t exist in Excel 2016. If the librarian uses XLOOKUP, the colleague sees nothing but a #NAME? error on every cell. A potential diplomatic incident between floors. VLOOKUP wins here, full stop.

Solution · VLOOKUP (compatibility)
VLOOKUP(A2; Catalogue!A:D; 2; FALSE)
→ Looks up the ISBN in A2, returns the title (column 2). Works on any version of Excel.

Pro Tip

If you’re not sure which version of Excel your colleagues are using, go to File → Save As and check for a compatibility warning. Or just ask them directly. It’s socially acceptable.

Example 2 · Medical Practice · XLOOKUP wins
The lookup column is not the first one

The administrator at a medical practice has a patient table: surname in column A, first name in B, tax ID in C, assigned doctor in D, date of last visit in E. She needs to look up patients by tax ID (column C) and return the doctor (column D).

With VLOOKUP, the lookup column must be the first one. The tax ID is in column C. So she would have to move the columns around — probably breaking other references — or use the classic INDEX+MATCH trick. Or simply use XLOOKUP, which doesn’t have this problem.

Solution · XLOOKUP (search on any column)
XLOOKUP(F2; PatientsTable[TaxID]; PatientsTable[AssignedDoctor]; "Patient not found")
→ Looks up the tax ID in F2 in the TaxID column, returns the doctor. No need to move columns.

Example 3 · School · XLOOKUP wins
Finding the last occurrence (the most recent grade)

The school office keeps a grade register with thousands of rows: every time a teacher enters a grade, they add a row. The same student has dozens of rows. The principal wants to know the last grade entered for each student — i.e. the most recent one, which is the lowest occurrence in the table.

VLOOKUP always finds the first occurrence from the top. There is no native way to make it find the last one. With XLOOKUP, just set the search mode to -1: it searches from the last row upwards, so it finds the most recent occurrence.

Solution · XLOOKUP (last occurrence)
XLOOKUP(A2; GradeRegister[StudentID]; GradeRegister[Grade]; "No grade"; 0; -1)
→ Mode -1: searches from the last row. Returns the student's most recent grade.

Pro Tip

The -1 mode in XLOOKUP is also useful in help desks and warehouses to find the latest transaction or the most recent status update on a ticket or order. Whenever you have time-based data with duplicates and want the latest entry, think XLOOKUP with mode -1.

Example 4 · Shop / Point of Sale · VLOOKUP wins
Simplified tables with a fixed structure

The store manager has a price table with product code in column A and price in column B. Two columns. He’s used it for years. The structure never changes. He sends it every week to three branches, all of which run different versions of Excel — including a tablet running Excel Mobile.

In this case VLOOKUP is perfectly adequate and universally compatible. There’s no column constraint to work around, no need for advanced error handling, no reverse search. Using XLOOKUP here adds nothing and risks breaking the file on older devices. Simplicity wins.

Solution · VLOOKUP (simplicity and compatibility)
VLOOKUP(A2; PriceList!A:B; 2; FALSE)
→ Looks up the product code, returns the price. Works everywhere, including Excel Mobile.

“The right function isn’t always the newest one. It’s the one that solves the problem without creating new ones.”

XLOOKUP and VLOOKUP inside other functions: 3 examples

So far we’ve used lookup functions on their own. But Excel becomes truly powerful when you start using them as ingredients inside larger formulas. The looked-up value becomes the input for a calculation, a condition, or another function. These three situations will almost certainly come up for you.

Nesting 1 · HR Office · IF + XLOOKUP
Conditional bonus calculation based on employee level

The HR department needs to calculate the end-of-year bonus. The rule: if the employee’s level (retrieved from the HR table) is “Senior”, a 15% bonus is applied to the base salary. Otherwise, no bonus.

A naive approach: first make a column with XLOOKUP to retrieve the level, then make another column with IF that reads that column. Result: two columns, double the work, double the chance of making a mistake.

The correct approach: use XLOOKUP directly as an argument of IF. The IF function evaluates the result of XLOOKUP without you needing to see it in a separate cell.

Formula · IF(XLOOKUP(...))
IF(
XLOOKUP(A2; HRTable[ID]; HRTable[Level])= "Senior";
B2 * 1.15;
B2
)
→ If the level found is "Senior" → salary + 15%. Otherwise → salary unchanged.

The formula updates automatically every time the HR table is modified. No manual copy-pasting. No helper columns. No Tuesday afternoon spent updating things by hand.

Pro Tip

This pattern — IF(XLOOKUP(...)="value"; action_if_true; action_if_false) — is one of the most useful patterns in all of Excel. Learn it by heart. Use it every time you need to make a decision based on data that lives in another table.

Nesting 2 · Pharmacy / Warehouse · SUMIF + VLOOKUP
Summing stock for all products belonging to a category

The pharmacy warehouse manager has a stock table with product code, quantity, and category. He needs to create a summary showing the total quantity for each category. But he doesn’t have the category directly in the right column — he needs to retrieve it from the product table for each item.

The solution is to use VLOOKUP as the criteria argument in SUMIF, or — even more elegantly — build a helper column with VLOOKUP and then use it as the criteria. Here we show the helper column approach, which is more readable and less error-prone.

Helper column · Retrieving the category
VLOOKUP(A2; ProductTable!A:C; 3; FALSE)
→ Retrieves the category for each product code. You now have the category in column D.

Final formula · SUMIF on the category column
SUMIF(D:D; "Painkillers"; B:B)
→ Sums all quantities (column B) where the category (column D) is "Painkillers".

Result: a dynamic category summary that updates every time the stock or product table changes. The warehouse manager no longer has to calculate anything by hand and can stop using the phone calculator he keeps hidden under the counter.

Nesting 3 · Help Desk · TEXT + XLOOKUP
Building automatic messages with data pulled from a table

The help desk operator needs to automatically generate a summary text for each ticket: “Ticket #1042 — Client: John Smith — Priority: High — Assigned to: Julia M.” The ticket ID is in column A, all other data is in a separate table.

The solution is to use multiple XLOOKUPs inside a CONCATENATE function (or directly with the & operator), building the string piece by piece with data retrieved from the table.

Formula · Building a message with nested XLOOKUP
"Ticket #"&A2&" — Client: "&
XLOOKUP(A2; Tickets[ID]; Tickets[Client]) &
" — Priority: "&
XLOOKUP(A2; Tickets[ID]; Tickets[Priority]) &
" — Assigned to: "&
XLOOKUP(A2; Tickets[ID]; Tickets[Operator])
→ Result: "Ticket #1042 — Client: John Smith — Priority: High — Assigned to: Julia M."

Pro Tip

This approach is the foundation for building email templates, automated reports, or printable labels directly from Excel. Every time you find yourself copying data by hand to fill in the same text over and over, think of this pattern.

Other functions inside XLOOKUP: 3 examples

Now let’s go the other way: functions nested inside XLOOKUP. Instead of providing a static value to look up, you provide the result of a formula. This allows you to perform dynamic lookups, transform data before searching, or calculate the lookup value on the fly.

Inside LOOKUP 1 · Warehouse / Logistics · XLOOKUP with NETWORKDAYS
Calculating the expected delivery date based on supplier lead time

The logistics manager has a supplier table with supplier ID and lead time in days. For each order he wants to calculate the expected delivery date: order date + supplier lead time, skipping weekends and public holidays. All in a single cell, with no helper columns.

The solution: nest XLOOKUP as an argument of WORKDAY, which calculates a future working date by adding a number of working days to a start date. The days are pulled dynamically from the supplier table via XLOOKUP.

Formula · WORKDAY(date; XLOOKUP(supplier))
WORKDAY(
B2; ← order date
XLOOKUP(A2; Suppliers[ID]; Suppliers[LeadDays]; 0)
)
→ Adds the supplier's lead time working days to the order date. If the supplier doesn't exist: 0 days added.

The if_not_found = 0 parameter of XLOOKUP ensures that new suppliers not yet in the table don’t generate errors — the delivery date simply matches the order date, implicitly signalling that data is missing.

Pro Tip

WORKDAY accepts an optional third parameter: a list of public holidays. If you have a column with national holidays (Christmas, bank holidays, etc.), add it there and the function will automatically skip them in its calculations.

Inside LOOKUP 2 · Sales Office · XLOOKUP with UPPER and TRIM
Finding a client even when the name has been typed incorrectly

The sales rep receives emails with client company names written in different ways: sometimes all caps, sometimes with extra spaces, sometimes with mixed capitalisation. The CRM table stores names in uppercase with no extra spaces. A direct lookup fails because "mario srl" ≠ "MARIO SRL".

The solution is to normalise the lookup value before passing it to XLOOKUP, using UPPER to convert everything to uppercase and TRIM to remove extra spaces. Both functions are nested as the first argument of XLOOKUP.

Formula · XLOOKUP(UPPER(TRIM(...)))
XLOOKUP(
UPPER(TRIM(A2));
CRM[ClientName];
CRM[SalesRep];
"Client not found in CRM"
)
→ Normalises the text in A2 before searching. " mario srl " becomes "MARIO SRL" and is found.

Watch out

This only works if the data in the lookup column of the CRM table is also in uppercase with no extra spaces. If the data there is inconsistent too, you’ll need to apply the same normalisation to the lookup column as well — and at that point it’s better to clean the data at the source rather than doing acrobatics with formulas.

Inside LOOKUP 3 · School · XLOOKUP with TODAY and date comparison
Automatically finding the teacher on duty based on today’s date

The school office has a table with supervision duty dates and the name of the teacher assigned to each date. Every morning someone opens the file and wants to immediately know who is on duty today, without manually searching through the table.

The solution is to use the TODAY() function as the lookup value inside XLOOKUP. TODAY() always returns the current date, so the formula automatically finds the teacher on duty for the day the file is opened. Zero manual interaction.

Formula · XLOOKUP(TODAY(); ...)
XLOOKUP(
TODAY();
Duties[Date];
Duties[TeacherOnDuty];
"No duty scheduled for today"
)
→ Every time the file is opened, it automatically shows the teacher on duty for today.

The if_not_found parameter handles weekends and unscheduled days without generating errors — essential if the file gets opened on a Saturday by an anxious deputy head.

Pro Tip

This same pattern — XLOOKUP(TODAY(); ...) — works for any table with dates: seasonal opening hours, prices that change by period, resource availability by date. It’s one of the most underrated and most useful patterns in Excel.

Summary: what to take away

If you had to explain all of this to a colleague in three minutes by the coffee machine, here’s what you’d say:

VLOOKUP is stable, universal, and works on any version of Excel. It has two major limitations: the lookup column must be the first one, and it doesn’t handle errors natively. It’s perfectly fine for simple tables and files shared with people using older versions of Excel.

XLOOKUP is the modern version without those limitations. It searches in any column, handles missing values with the if_not_found parameter, can search from the last row, and is much easier to read. Use it whenever your Excel supports it (Microsoft 365 and Excel 2021 onwards).

Nesting: both functions become far more powerful when you combine them with IF, SUMIF, TEXT, CONCATENATE as outer functions, or with TODAY, UPPER, TRIM, WORKDAY as inner arguments. Learn the patterns, not the individual formulas.

And the next time you see #N/A, don’t kick the computer. First check whether there’s an invisible space. It almost always is. Almost always.

Final Pro Tip

Select a cell with the value that isn’t being found, press F2 to enter edit mode, and look carefully at the edges of the text: if the cursor starts slightly to the right of the first character, there’s a leading space. Use TRIM and live happily ever after.

CERCA.VERT e CERCA.X: La Guida Definitiva

Tutto quello che avreste voluto sapere, spiegato in modo umano, con esempi veri, e senza farvi piangere davanti al foglio Excel.

Ogni anno, milioni di persone aprono Excel, guardano due tabelle separate, e pensano: “ci deve essere un modo per collegare queste cose senza copiarle a mano una per una.” C’è. Si chiama CERCA.VERT. E da qualche anno c’è anche CERCA.X, che è la stessa cosa ma senza i difetti imbarazzanti. Questa è la guida che nessuno vi ha mai fatto leggere prima di lasciarvi soli con un #N/D e un martedì pomeriggio distrutto.

CERCA.VERT: il classico che non passa mai di moda (ma che fa i capricci)

CERCA.VERT significa Cerca Verticalmente. In pratica: date un valore, vi va a cercarlo in una colonna di una tabella, e vi restituisce il valore corrispondente da un’altra colonna della stessa riga. Come un cameriere che cerca il vostro nome sulla lista prenotazioni e vi dice a che tavolo siete seduti.

Semplice in teoria. In pratica, come vedremo, quel cameriere a volte vi dice #N/D e sparisce in cucina.

La sintassi completa

CERCA.VERT(valore; matrice_tabella; indice_colonna; [corrisp_intervallo])

Parametro	Cosa fa	Esempio
valore – obbligatorio	Il valore che volete cercare. Può essere un testo, un numero, o un riferimento a cella.	`A2` (la cella che contiene il codice dipendente)
matrice_tabella – obbligatorio	La tabella in cui cercare. Attenzione: la colonna di ricerca DEVE essere la prima colonna (quella più a sinistra) di questa selezione.	`B2:E500` oppure `TabellaHR[#Tutto]`
indice_colonna – obbligatorio	Il numero della colonna della tabella da cui prendere il risultato. Se la tabella ha 4 colonne e volete la terza, scrivete 3.	`3` (restituisce il valore dalla 3ª colonna)
corrisp_intervallo facoltativo	`FALSO` = cerca corrispondenza esatta (quasi sempre quello che volete). `VERO` = cerca corrispondenza approssimativa (utile per scaglioni, es. tasse, fasce di punteggio). Default: `VERO`, quindi scrivete sempre FALSO se non siete sicuri.	`FALSO`

Trappola classica

Il parametro corrisp_intervallo ha default VERO. Questo significa che se ve lo dimenticate, Excel farà una ricerca approssimativa. Su dati non ordinati, questo produce risultati sbagliati senza avvisarvi. Senza errore. In silenzio. Come un collega che ha capito male le istruzioni ma non lo ammette.

Esempio base di CERCA.VERT

CERCA.VERT(A2; TabellaStipendi; 3; FALSO)
→ Cerca il codice in A2 nella prima colonna di TabellaStipendi, restituisce il valore dalla 3ª colonna

Pro Tip

Usate sempre i riferimenti di tabella strutturata (Tabella1[Colonna]) invece dei range rigidi (B2:E500). Se aggiungete righe alla tabella, la formula si aggiorna da sola. Se usate il range rigido, dovete ricordarvi di aggiornarlo manualmente, e sicuramente ve ne dimenticherete nel momento peggiore.

CERCA.X: il fratello minore che è cresciuto meglio

CERCA.X è arrivato nel 2019 con Microsoft 365 e ha risolto praticamente tutti i problemi strutturali di CERCA.VERT. Non è solo “un CERCA.VERT più nuovo” — è una funzione completamente ripensata che funziona in modo diverso, più flessibile e più umano.

Se CERCA.VERT è il cameriere che vi cerca sulla lista dal basso verso l’alto e può guardare solo a destra, CERCA.X è il maitre che può guardare ovunque, dirvi se non siete in lista senza fare scene, e persino cercare partendo dalla fine.

La sintassi completa

CERCA.X(valore; matrice_ricerca; matrice_restituzione; [se_non_trovato]; [modalità_corrispondenza]; [modalità_ricerca])

Parametro	Cosa fa	Esempio
valore obbligatorio	Il valore che volete trovare. Identico a CERCA.VERT.	`A2`
matrice_ricerca obbligatorio	La colonna (o riga) in cui cercare. Non deve essere per forza la prima. Può essere qualsiasi colonna della tabella.	`TabellaClienti[ID]`
matrice_restituzione obbligatorio	La colonna (o riga) da cui prendere il risultato. Completamente separata dalla colonna di ricerca. Può essere a sinistra, a destra, ovunque.	`TabellaClienti[NomeCliente]`
se_non_trovato facoltativo	Cosa restituire se il valore non viene trovato. Se omesso, restituisce #N/D. Con questo parametro, potete restituire testo personalizzato, zero, o qualsiasi altra cosa.	`"Non trovato"` oppure `0`
modalità_corrispondenza facoltativo	`0` = corrispondenza esatta (default). `-1` = esatta o prossimo minore. `1` = esatta o prossimo maggiore. `2` = con caratteri jolly (*, ?).	`0`
modalità_ricerca facoltativo	`1` = dalla prima all’ultima riga (default). `-1` = dall’ultima alla prima (utile se volete l’occorrenza più recente). `2` o `-2` = ricerca binaria (su dati ordinati, molto più veloce).	`-1` (per trovare la riga più recente)

Pro Tip

Il parametro se_non_trovato da solo vale già il passaggio a CERCA.X. Il vecchio SE.ERRORE(CERCA.VERT(...); "Non trovato") è verbose e ripete la formula due volte. Con CERCA.X lo scrivete una volta sola, pulito, leggibile, e i vostri colleghi vi guarderanno con rispetto.

CERCA.X(A2; TabellaClienti[ID]; TabellaClienti[NomeCliente]; "Cliente non registrato")
→ Cerca A2 nella colonna ID, restituisce il NomeCliente. Se non esiste: "Cliente non registrato"

Quando usare uno e quando usare l’altro: 4 esempi reali

La risposta breve: usate sempre CERCA.X se il vostro Excel lo supporta. Ma la vita è complicata, i colleghi usano versioni vecchie di Office, e a volte esistono motivi legittimi per cui CERCA.VERT è ancora la scelta giusta. Eccoli.

CERCA.VERT · Limiti

La colonna di ricerca deve essere la prima (a sinistra)
Si riferisce alle colonne per numero, non per nome
Nessuna gestione nativa degli errori
Non cerca da destra verso sinistra
Non trova l’ultima occorrenza

CERCA.X · Vantaggi

Cerca in qualsiasi colonna, restituisce da qualsiasi colonna
Usa nomi di colonna, non numeri arbitrari
Gestione errori integrata con se_non_trovato
Cerca in entrambe le direzioni
Trova l’ultima occorrenza con modalità -1

Esempio 1 · Biblioteca · Vince CERCA.VERT
File condiviso con colleghi su Excel 2016

La bibliotecaria ha un catalogo con 8.000 libri: codice ISBN nella colonna A, titolo in B, autore in C, reparto in D. Deve fare un file che anche la collega part-time — che usa Excel 2016 sul computer fisso del piano -1 — possa aprire e usare senza errori.

CERCA.X non esiste in Excel 2016. Se la bibliotecaria usa CERCA.X, la collega vede solo un errore #NOME? su ogni cella. Possibile scenario di crisi diplomatica tra piani. Qui vince CERCA.VERT, punto.

Soluzione · CERCA.VERT (compatibilità)
CERCA.VERT(A2; Catalogo!A:D; 2; FALSO)
→ Cerca l'ISBN in A2, restituisce il titolo (colonna 2). Funziona su qualsiasi versione di Excel.

Pro Tip

Se non siete sicuri di quale versione di Excel usino i vostri colleghi, andate su File → Salva con nome e guardate se c’è un avviso di compatibilità. O chiedete direttamente. È socialmente accettabile.

Esempio 2 · Ambulatorio Medico · Vince CERCA.X
La colonna di ricerca non è la prima

L’amministrativa dell’ambulatorio ha una tabella pazienti: cognome in colonna A, nome in B, codice fiscale in C, medico di riferimento in D, data ultima visita in E. Deve cercare i pazienti per codice fiscale (colonna C) e restituire il medico (colonna D).

Con CERCA.VERT, la colonna di ricerca deve essere la prima. Il codice fiscale è in colonna C. Quindi dovrebbe spostare le colonne, rompendo probabilmente altri riferimenti, oppure usare il classico trucco INDEX+CONFRONTA. Oppure — semplicemente — usare CERCA.X che non ha questo problema.

Soluzione · CERCA.X (ricerca su qualsiasi colonna)
CERCA.X(F2; TabellaPazienti[CodiceFiscale]; TabellaPazienti[MedicoRiferimento]; "Paziente non trovato")
→ Cerca il CF in F2 nella colonna CF, restituisce il medico. Nessun bisogno di spostare colonne.

Esempio 3 · Scuola · Vince CERCA.X
Trovare l’ultima occorrenza (il voto più recente)

La segreteria scolastica tiene un registro voti con migliaia di righe: ogni volta che un professore inserisce un voto, aggiunge una riga. Lo stesso studente ha decine di righe. La dirigente vuole sapere l’ultimo voto inserito per ogni studente — cioè il più recente, che è l’occorrenza più in basso nella tabella.

CERCA.VERT trova sempre la prima occorrenza dall’alto. Non esiste modo nativo di fargli trovare l’ultima. Con CERCA.X basta impostare la modalità di ricerca a -1: cerca dall’ultima riga verso l’alto, quindi trova l’occorrenza più recente.

Soluzione · CERCA.X (ultima occorrenza)
CERCA.X(A2; RegistroVoti[IDStudente]; RegistroVoti[Voto]; "Nessun voto"; 0; -1)
→ Modalità -1: cerca dall'ultima riga. Restituisce il voto più recente dell'alunno.

Pro Tip

La modalità -1 di CERCA.X è anche utile negli help desk e nei magazzini per trovare l’ultima transazione o l’ultimo aggiornamento di stato su un ticket o un ordine. Ogni volta che avete dati temporali con duplicati e volete l’ultimo, pensate a CERCA.X con modalità -1.

Esempio 4 · Negozio / Punto Vendita · Vince CERCA.VERT
Tabelle semplificate con struttura fissa

Il responsabile del negozio ha una tabella prezzi con codice prodotto in colonna A e prezzo in colonna B. Due colonne. La usa da anni. La struttura non cambia mai. La deve inviare ogni settimana ai tre punti vendita che hanno tutti Excel diversi, incluso un tablet con Excel Mobile.

In questo caso CERCA.VERT è perfettamente adeguato e universalmente compatibile. Non c’è nessun vincolo di colonna da aggirare, nessuna necessità di gestione errori avanzata, nessuna ricerca inversa. Usare CERCA.X qui non aggiunge nulla, e rischia di rompere il file sui dispositivi più vecchi. Semplicità vince.

Soluzione · CERCA.VERT (semplicità e compatibilità)
CERCA.VERT(A2; ListinoPrezzi!A:B; 2; FALSO)
→ Cerca il codice prodotto, restituisce il prezzo. Funziona ovunque, anche su Excel Mobile.

“La funzione più giusta non è sempre la più nuova. È quella che risolve il problema senza crearne di nuovi.”

CERCA.X e CERCA.VERT dentro altre funzioni: 3 esempi

Finora abbiamo usato le funzioni CERCA da sole. Ma Excel diventa davvero potente quando iniziate a usarle come ingredienti all’interno di formule più grandi. Il valore cercato diventa l’input per un calcolo, una condizione, o un’altra funzione. Queste tre situazioni vi capiteranno quasi sicuramente.

Nidificazione 1 · Ufficio HR · SE + CERCA.X
Calcolo bonus condizionale basato sul livello del dipendente

Il reparto HR deve calcolare il bonus di fine anno. La regola: se il livello del dipendente (recuperato dalla tabella HR) è “Senior”, si applica un bonus del 15% sullo stipendio base. Altrimenti, nessun bonus.

Un approccio ingenuo: prima si fa una colonna con CERCA.X per recuperare il livello, poi si fa un’altra colonna con SE che legge quella colonna. Risultato: due colonne, doppio lavoro, doppia possibilità di sbagliare.

L’approccio corretto: si usa CERCA.X direttamente come argomento del SE. La funzione SE valuta il risultato di CERCA.X senza che voi abbiate bisogno di vederlo in una cella separata.

Formula · SE(CERCA.X(...))
SE(
CERCA.X(A2; TabellaHR[ID]; TabellaHR[Livello])= "Senior";
B2 * 1.15;
B2
)
→ Se il livello trovato è "Senior" → stipendio + 15%. Altrimenti → stipendio invariato.

La formula si aggiorna automaticamente ogni volta che la tabella HR viene modificata. Nessun copia-incolla manuale. Nessuna colonna di servizio. Nessun martedì pomeriggio dedicato ad aggiornare le cose a mano.

Pro Tip

Questo schema — SE(CERCA.X(...)="valore"; azione_se_vero; azione_se_falso) — è uno dei pattern più utili di tutto Excel. Imparatelo a memoria. Usatelo ogni volta che dovete prendere una decisione basata su un dato che si trova in un’altra tabella.

Nidificazione 2 · Farmacia / Magazzino · SOMMA.SE + CERCA.VERT
Sommare le scorte di tutti i prodotti appartenenti a una categoria

Il magazziniere della farmacia ha una tabella scorte con codice prodotto, quantità, e categoria. Deve creare un riepilogo che mostri la quantità totale per ogni categoria. Ma non ha la categoria direttamente nella colonna giusta — deve recuperarla dalla tabella prodotti per ogni articolo.

La soluzione è usare CERCA.VERT come argomento del criterio in SOMMA.SE, oppure — ancora più elegante — costruire una colonna ausiliaria con CERCA.VERT e poi usarla come criterio. In questo caso mostriamo l’approccio a colonna ausiliaria, che è più leggibile e meno soggetto a errori.

Colonna ausiliaria · Recupero categoria
CERCA.VERT(A2; TabellaProdotti!A:C; 3; FALSO)
→ Recupera la categoria per ogni codice prodotto. Ora avete la categoria in colonna D.

Formula finale · SOMMA.SE sulla colonna con la categoria
SOMMA.SE(D:D; "Antidolorifici"; B:B)
→ Somma tutte le quantità (colonna B) dove la categoria (colonna D) è "Antidolorifici".

Risultato: un riepilogo dinamico per categoria che si aggiorna ogni volta che cambiano le scorte o la tabella prodotti. Il magazziniere non deve più calcolare niente a mano e può smettere di usare la calcolatrice del telefono tenendola nascosta sotto il bancone.

Nidificazione 3 · Help Desk · TESTO + CERCA.X
Costruire messaggi automatici con dati presi da una tabella

L’operatore dell’help desk deve generare automaticamente un testo riepilogativo per ogni ticket: “Ticket #1042 — Cliente: Mario Rossi — Priorità: Alta — Assegnato a: Giulia M.” L’ID ticket è in colonna A, tutti gli altri dati sono in una tabella separata.

La soluzione è usare più CERCA.X dentro una funzione CONCATENA (o direttamente con l’operatore &), costruendo la stringa pezzo per pezzo con i dati recuperati dalla tabella.

Formula · Costruzione messaggio con CERCA.X annidato
"Ticket #"&A2&" — Cliente: "&
CERCA.X(A2; Ticket[ID]; Ticket[Cliente]) &
" — Priorità: "&
CERCA.X(A2; Ticket[ID]; Ticket[Priorità]) &
" — Assegnato a: "&
CERCA.X(A2; Ticket[ID]; Ticket[Operatore])
→ Risultato: "Ticket #1042 — Cliente: Mario Rossi — Priorità: Alta — Assegnato a: Giulia M."

Pro Tip

Questo approccio è la base per costruire template di email, report automatici, o etichette stampabili direttamente da Excel. Ogni volta che vi trovate a copiare dati a mano per riempire un testo sempre uguale, pensate a questo pattern.

Altre funzioni dentro CERCA.X: 3 esempi

Ora andiamo dall’altra parte: funzioni annidate all’interno di CERCA.X. Invece di fornire un valore statico da cercare, fornite il risultato di una formula. Questo permette di fare ricerche dinamiche, trasformare i dati prima di cercarli, o calcolare il valore cercato al volo.

Dentro CERCA 1 · Magazzino / Logistica · CERCA.X con GIORNI.LAVORATIVI.TOT
Calcolare la data di consegna prevista in base al lead time del fornitore

Il responsabile logistico ha una tabella fornitori con ID fornitore e giorni di lead time. Per ogni ordine vuole calcolare la data di consegna prevista: data ordine + lead time del fornitore, saltando weekend e festivi. Il tutto in una cella sola, senza colonne di appoggio.

La soluzione: si annida CERCA.X come argomento di GIORNO.LAVORATIVO, che calcola una data lavorativa futura aggiungendo un numero di giorni lavorativi a una data di partenza. I giorni vengono presi dinamicamente dalla tabella fornitori tramite CERCA.X.

Formula · GIORNO.LAVORATIVO(data; CERCA.X(fornitore))
GIORNO.LAVORATIVO(
B2;← data dell'ordine
CERCA.X(A2; Fornitori[ID]; Fornitori[LeadDays]; 0)
)
→ Aggiunge i giorni lavorativi di lead time del fornitore alla data ordine. Se il fornitore non esiste: 0 giorni aggiunti.

Il parametro se_non_trovato = 0 di CERCA.X fa sì che i fornitori nuovi, non ancora in tabella, non generino errori: semplicemente la data di consegna coincide con la data dell’ordine, segnalando implicitamente che mancano dati.

Pro Tip

GIORNO.LAVORATIVO accetta un terzo parametro opzionale: l’elenco dei giorni festivi. Se avete una colonna con le festività nazionali (Natale, Ferragosto, ecc.), inseritela lì e la funzione le salterà automaticamente nei calcoli.

Dentro CERCA 2 · Ufficio Commerciale · CERCA.X con MAIUSC e ANNULLA.SPAZI
Cercare un cliente anche quando il nome è stato digitato male

Il commerciale riceve email con nomi di aziende cliente scritti in modi diversi: a volte tutto maiuscolo, a volte con spazi in più, a volte con maiuscole miste. La tabella CRM tiene i nomi in maiuscolo senza spazi extra. La ricerca diretta fallisce perché "mario srl" ≠ "MARIO SRL".

La soluzione è normalizzare il valore cercato prima di passarlo a CERCA.X, usando MAIUSC per convertire tutto in maiuscolo e ANNULLA.SPAZI per eliminare gli spazi superflui. Si annidano entrambe le funzioni come primo argomento di CERCA.X.

Formula · CERCA.X(MAIUSC(ANNULLA.SPAZI(...)))CERCA.X(
MAIUSC(ANNULLA.SPAZI(A2));
CRM[NomeCliente];
CRM[ResponsabileCommerciale];
"Cliente non trovato nel CRM"
)
→ Normalizza il testo in A2 prima di cercare. " mario srl " diventa "MARIO SRL" e viene trovato.

Attenzione

Questo funziona solo se anche i dati nella colonna di ricerca della tabella CRM sono in maiuscolo senza spazi. Se i dati sono disomogenei anche lì, dovete applicare la stessa normalizzazione anche alla colonna di ricerca — e a quel punto conviene pulire i dati alla fonte, non fare acrobazia con le formule.

Dentro CERCA 3 · Scuola · CERCA.X con OGGI e confronto date
Trovare automaticamente il docente di turno in base alla data odierna

La segreteria ha una tabella con le date dei turni di sorveglianza e il nome del docente assegnato per ciascuna data. Ogni mattina qualcuno apre il file e vuole sapere subito chi è di turno oggi, senza cercare manualmente nella tabella.

La soluzione è usare la funzione OGGI() come valore da cercare all’interno di CERCA.X. OGGI() restituisce sempre la data corrente, quindi la formula trova automaticamente il docente di turno per il giorno in cui viene aperto il file. Zero interazione manuale.

Formula · CERCA.X(OGGI(); ...)
CERCA.X(
OGGI();
Turni[Data];
Turni[DocenteDiTurno];
"Nessun turno programmato per oggi"
)
→ Ogni volta che il file viene aperto, mostra automaticamente il docente di turno per oggi.

Il parametro se_non_trovato gestisce i weekend e i giorni non programmati senza generare errori — fondamentale se il file viene aperto anche di sabato dal vicedirettore ansioso.

Pro Tip

Questo stesso schema — CERCA.X(OGGI(); ...) — funziona per qualsiasi tabella con date: orari di apertura stagionali, prezzi che cambiano per periodo, disponibilità di risorse per data. È uno dei pattern più sottovalutati e più utili di Excel.

Riepilogo: cosa portarsi a casa

Se doveste spiegare tutto questo a un collega in tre minuti davanti alla macchinetta del caffè, ecco cosa direste:

CERCA.VERT è stabile, universale, e funziona su qualsiasi versione di Excel. Ha due grandi limiti: la colonna di ricerca deve essere la prima, e non gestisce gli errori nativamente. Va benissimo per tabelle semplici e file condivisi con persone che usano Excel vecchio.

CERCA.X è la versione moderna e priva di quei limiti. Cerca in qualsiasi colonna, gestisce i valori non trovati con il parametro se_non_trovato, può cercare dall’ultima riga, e si legge molto più facilmente. Usatelo ogni volta che il vostro Excel lo supporta (Microsoft 365 e Excel 2021 in poi).

Nidificazione: entrambe le funzioni diventano molto più potenti quando le combinate con SE, SOMMA.SE, TESTO, CONCATENA come funzioni esterne, oppure con OGGI, MAIUSC, ANNULLA.SPAZI, GIORNO.LAVORATIVO come argomenti interni. Imparate i pattern, non le singole formule.

E la prossima volta che vedete #N/D, non prendete a calci il computer. Controllate prima se c’è uno spazio invisibile. Quasi sempre è quello. Quasi sempre.

Pro Tip Finale

Selezionate una cella con il valore non trovato, premete F2 per entrare in modifica, e guardate bene i bordi del testo: se il cursore parte un po’ più a destra del primo carattere, c’è uno spazio davanti. Usate ANNULLA.SPAZI e vivete felici.

NotebookLM Is Secretly Unhinged (And You’re Not Using It Right)

You’ve heard of NotebookLM. You might have even used it. You uploaded a PDF, asked it a question, it answered with a little footnote citation, you thought “huh, neat” — and then you closed the tab and went back to Googling things like a person who peaked in 2019.

That’s fine. No judgment. (Some judgment.)

Because here’s the thing: NotebookLM is not a smarter search engine. It’s not a fancy summarizer. It’s not even, really, a chatbot. What it actually is — once you stop using it at surface level — is closer to a private intelligence analyst that you’ve locked in a room with all your documents and told to figure everything out. And lately, that analyst has been getting some genuinely alarming upgrades.

This is the deep dive. The stuff the casual users don’t know. The part where NotebookLM goes from “oh that’s useful” to “wait, it can do what?”

Let’s go.

First: Why NotebookLM Is Fundamentally Different From Every Other AI Tool

Most AI tools work from what they already know. They’ve read the internet, absorbed billions of documents, and they answer your questions from that giant pile of training data. Which sounds great until you realize the giant pile includes Reddit arguments, SEO-optimized nonsense, and things that were true in 2022 and aren’t anymore. This is where hallucinations come from — the AI is pattern-matching from memory, not reading a source, and sometimes the memory is wrong. Confidently wrong. With full eye contact.

NotebookLM doesn’t do that. It’s “grounded” — meaning it only reasons from the sources you give it. Nothing else. No internet wandering, no drawing on general knowledge, no confident fabrications. Every answer it gives you comes with a citation that links directly to the specific paragraph in your document it pulled from. Every. Single. One.

This sounds like a limitation. It is actually a superpower. Because “accurate, verifiable, and specific to your actual material” is more useful than “knows everything vaguely” approximately 100% of the time when you’re doing real work.

And as of 2026, the platform supports up to 600 sources per notebook on paid tiers, with each source supporting up to 500,000 words. That’s not a notebook. That’s a library with a very fast librarian.

The Insane Things NotebookLM Can Actually Do

1. It Makes Podcasts. Real Ones. That You Can Interrupt.

At some point, someone at Google had the idea to make NotebookLM generate a two-host conversational podcast from your documents. This should have been a gimmick. It became one of the most viral AI features of the last two years, with over 10 million monthly users, because the output is genuinely unsettling in how natural it sounds — two AI hosts riffing, disagreeing slightly, going on tangents, synthesizing your 80-page report into a 12-minute conversation you’d actually listen to on a commute.

That alone is insane. But then they added Interactive Mode.

In Interactive Mode, you can join the podcast. Press a button mid-episode and become a third participant. The hosts pause. You ask a question. They answer it — from your sources, cited, grounded — and then continue. You’re essentially gate-crashing your own AI radio show to demand a deeper explanation of slide 14. This is either the future of learning or a sign that we’ve completely lost the plot, and honestly it might be both.

Power user move: don’t just generate one Audio Overview and call it done. Customize each one. Tell the hosts to “focus on the financial implications” or “take a more skeptical, contrarian tone” or “assume the listener already knows the basics.” Generate multiple episodes from the same notebook. You now have a podcast series about your own research. This is a normal thing to do apparently.

2. It Makes Videos. Cinematic Ones. From Your Documents.

The Video Overview feature uses Google’s Veo model to generate narrated, scripted, visually structured explainer videos from your notebook’s content. Not slideshows. Not text-to-speech over static images. Actual cinematic explainers — scripted narratives with visuals and narration built entirely from what’s in your sources.

For educators generating training content. For marketers turning a research report into a shareable video summary. For literally anyone who has ever thought “I need to explain this thing but I do not want to spend six hours in Premiere Pro” — this is the answer. In minutes. From a button.

What remains appropriately bananas is that the video knows your content. It’s not hallucinating a generic explainer — it’s constructing the narrative from your actual documents. Which means it’s wrong about as often as your documents are wrong, which is a much better baseline than “wrong about as often as a confident AI is wrong.”

3. You Can Give It a 10,000-Character Personality

Custom instructions in NotebookLM used to cap out at 500 characters — enough for “be concise and professional,” not enough for anything interesting. In 2026 that limit expanded to 10,000 characters. This changes the tool’s entire personality.

Power users maintain what amounts to a template library of AI personas. Not just “be professional” — entire system-level instructions that define the AI’s role, reasoning style, constraints, and output format. Some examples that are genuinely useful:

The Socratic Learning Coach — instead of giving you answers, it asks you questions about the material and provides corrections when you’re wrong. This is active recall, which is how you actually learn things, as opposed to reading a summary and thinking you’ve learned things (you haven’t).

The Senior Strategy Consultant — responds only in terms of SWOT analysis, actionable recommendations, and executive-level framing. No fluff. No “great question.” Just brutal strategic clarity.

The Devil’s Advocate Auditor — specifically instructed to push back on everything, find weaknesses in arguments, and surface assumptions the sources haven’t proven. You stop asking it “is this good?” and start asking it “why is this wrong?” and suddenly you’re doing much better research.

The point is that the same notebook, with different custom instructions, becomes a completely different tool. Most people set it once to “be helpful” and never touch it again. The power users swap personas the way a consultant switches hats depending on the meeting.

4. It Will Tell You What’s Missing From Your Research

Here’s a trick that feels slightly illegal in its usefulness: instead of asking NotebookLM to summarize what’s in your documents, ask it to identify what’s not there.

The “Source Gap” prompt works like this: “Based on the sources in this notebook, identify what information is missing, which claims are unproven, and where the sources contradict each other.”

You’ve just turned the AI into an auditor of your own research. It will surface blind spots you didn’t know you had. Claims you made that no source actually backs up. Sections where two documents disagree and you never noticed. For anyone writing a report, building a business case, or preparing for a presentation where someone smart is going to poke holes in your argument — this is the move.

Most people use AI to build things. This is using AI to stress-test them. Different, and considerably more valuable.

5. Cross-Notebook Intelligence (The “Second Brain” Setup)

For a long time, the notebook silo was NotebookLM’s most frustrating limitation. Your Marketing notebook couldn’t talk to your Finance notebook. Your Research notebook didn’t know about your Strategy notebook. Great for focus, terrible for synthesis.

The 2026 integration with the main Gemini app changed this. You can now “mount” multiple notebooks as live sources inside Gemini, then query across all of them simultaneously. Your marketing data and your financial data and your competitor analysis — all in the same conversation, synthesizing toward a single strategic question.

This is the “Second Brain” architecture that productivity nerds have been trying to build manually for years. NotebookLM just made it a feature. Query it right and you stop asking “where did I put that information?” entirely — you just ask the question and it knows.

6. The Recursive Knowledge Loop (This One’s Genuinely Clever)

Here’s an advanced workflow that will immediately make your output better and will feel slightly like cheating.

The problem with big research notebooks is noise. You’ve got 30 sources, the AI is synthesizing across all of them, and the output is comprehensive but sometimes muddier than you want. The solution is recursive refinement:

Step 1: Prompt the AI to synthesize everything into one structured master note — a single, clean document that captures only what matters. Export it to Google Docs. Review it. Clean it up. Make it exactly what you want the AI to work from.

Step 2: Upload that refined document back as a source. Deselect all the original raw sources. Now the notebook’s entire intelligence is grounded in your pre-refined “gold standard” document instead of the noisy originals.

Step 3: Generate your Audio Overview, slide deck, video, or whatever you need from this cleaned-up foundation.

The output quality jump is immediately noticeable. You’ve essentially pre-filtered the AI’s context so it only draws from the best version of your research. This is how the people generating suspiciously polished AI-assisted reports are doing it — they’re not just prompting harder, they’re managing the quality of what the AI reads.

7. The “Prompt as a Source” Hack

This one is beautifully silly and completely works.

NotebookLM’s chat window has a character limit. Complex multi-stage workflows with detailed instructions hit that limit fast. The workaround: write your entire workflow as a Google Doc — a 2,000-word set of instructions, references, multi-level logic, output formats, the works — and upload it as a source. Then in the chat, just type: “Execute the workflow described in Source 12.”

The AI reads the source, follows the instructions, and your complex workflow is now effectively unlimited in length. You’ve bypassed the character limit by turning your prompt into a document. It’s technically not a hack so much as using the tool correctly in a way nobody told you about.

8. It Will Transcribe Your Calls and Turn Them Into Deliverables

NotebookLM now supports audio and video uploads — MP3s, MP4s, YouTube links — and transcribes them directly into the notebook’s knowledge base. The transcription becomes a searchable, citable source, just like any document.

The use case that immediately clicks for consultants, coaches, and anyone who sits through a lot of meetings: upload your call recording, and within minutes you have a searchable transcript that you can query for specific moments, summarize into show notes, convert into a FAQ document, or turn into a blog post. Without a third-party transcription service. Without copying and pasting anything. Without reading an 87-minute meeting transcript with your own eyes like some kind of animal.

What makes this better than a standalone transcription tool is the integration. The transcript lives in the same environment as your other research, which means you can cross-reference it immediately. “What did the client say about budget constraints in the call, and how does that compare to what the competitive analysis says about pricing sensitivity?” That’s a real question you can now ask.

9. AI-Generated Slides That You Can Argue With

NotebookLM generates slide decks from your sources, which is useful. What most people don’t know is that you can then argue with the slides in the chat panel and make it redo specific ones.

After generating a deck, you can prompt: “Redo slide 4 to focus on the executive summary and make it more formal.” Or “Slide 7 has too much text — restructure it as a visual comparison.” The AI revises individual slides based on your feedback without regenerating the whole deck.

Power user move: export to PPTX for final polish. The AI gets you to 85% without any of the layout busywork. You take it the last 15% manually in PowerPoint or Slides like a normal person with slightly more dignity than someone who’s fully outsourced their presentations to a machine. (No shame to that person either. We’re all figuring this out.)

10. The Three-Tool Chain That Professional Researchers Use

NotebookLM is excellent at grounded synthesis. It is deliberately not a creative tool, because creativity requires the kind of unpredictability that makes grounded research less accurate. So don’t try to make it do everything.

The workflow that’s quietly becoming standard in research-heavy professional environments:

Perplexity for initial sourcing — it’s built for search and surfacing high-quality URLs fast.

NotebookLM for deep synthesis — import those URLs, cross-reference them with your internal documents, generate structured notes, audit for gaps.

Claude or ChatGPT for creative output — take NotebookLM’s refined, accurate synthesis and hand it to a more generative model for the actual writing, ideation, or creative drafting.

Each tool does what it’s best at. You stop asking one tool to do everything and getting mediocre results across the board. You get Perplexity’s search speed, NotebookLM’s accuracy, and Claude’s writing quality — in sequence, intentionally. This is not cheating. This is knowing your tools.

Pro Tips: The Stuff That Saves You Real Time

Set your custom instructions before you do anything else. Most people generate half a notebook’s worth of output and then realize the AI’s been responding in a style that doesn’t fit. Custom instructions first. Always.

Use white-label notebooks for team knowledge bases. Share a notebook with “Chat-only access” so colleagues can query it without touching your source structure. You’ve just built a searchable, cited company knowledge base that answers questions instantly. In an afternoon. For free.

Generate multiple Audio Overviews per notebook. Each one can take a different angle, tone, or audience. One for executives, one for your team, one for yourself on a run. Same data, completely different listening experience.

For Deep Research, use it to fill the 20% your internal docs don’t cover. Your internal documents probably have 80% of what you need for a project. Deep Research browses live websites to find the rest — competitor pricing, recent regulatory changes, industry benchmarks — and imports the results as a cited source directly into your notebook. Don’t use it to replace your internal research. Use it to complete it.

Build your persona template library and reuse it everywhere. A well-written 10,000-character custom instruction for “Senior Product Manager review mode” or “Socratic exam prep mode” takes 20 minutes to write and saves hours every time you use it. Write it once, paste it in whenever you need it.

The Bottom Line

NotebookLM started life as a clever note-taking tool and has quietly become something else entirely — a full-scale research and content production environment that most of its users are still treating like a fancy search bar.

The gap between what it can do and what most people use it for is enormous. The tips above close that gap. And once you close it, you’ll find it very hard to go back to managing research the old way — the tab-switching, copy-pasting, “where did I put that?” way that eats hours and produces mediocre output.

You now have no excuse. Go build a second brain. Argue with your slides. Join your own podcast. This is your life now.

Shay Stibelman writes about AI tools, digital productivity, and the increasingly blurry line between “working smarter” and “letting the robots do everything” — at blog.stibelman.com. He makes video tutorials for people who’d rather watch someone else figure it out first, which is still a completely valid strategy.

Coming up next: How to build a full content production workflow using only NotebookLM, Claude, and a dangerous amount of caffeine.

Stop using Claude to write for you

AI Is a Brilliant Editor. Stop Making It Do Your Homework.

Using Claude to sharpen your voice — not replace it

Let me describe something that happens approximately ten thousand times a day, in offices, universities, and home desks everywhere from Milan to Minneapolis.

Someone has to write something. A blog post, an email, an essay, a proposal. They open Claude (or whatever AI tool they’ve decided is their personality this week), type “write me a 500-word post about [topic],” and then copy whatever comes out, change maybe three words, and call it done.

Claude — bless its magnificent silicon heart — obliges. It produces text. Beautiful, structured, grammatically impeccable text. Text that sounds like it was written by someone who has read everything ever published but has never once been stuck in traffic, argued with a supplier, or eaten a disappointing sandwich.

And here’s the problem: that text sounds like it. Not like you.

Your professor notices. Your client notices. Your newsletter subscribers definitely notice — and quietly unsubscribe while making a face.

The issue isn’t the AI. The issue is how you’re using it. You’re hiring a ghostwriter when you need an editor. And the difference between those two things is everything.

What Actually Goes Wrong When AI Writes For You

Let’s do a quick experiment. Ask any AI to write something “professional” about, say, climate change. I’ll wait.

You got something back, right? And I’d bet good money it contained at least one of the following:

“multifaceted challenge”
“synergistic approach”
“stakeholder ecosystems”
Passive voice that nobody would use in actual speech
An opinion that was perfectly balanced on all sides, because the AI didn’t want to offend anyone

That last one is the killer. Because you have opinions. Actual, human, slightly-irrational-in-the-best-way opinions. And AI’s default instinct is to sand them down until they’re smooth, inoffensive, and completely forgettable.

The text isn’t bad, exactly. It’s just not yours. It sounds like a very polished press release written by a committee. And nobody — nobody — subscribes to a newsletter because they love press releases from committees.

There’s also the detection problem. AI detectors are getting very good. And even when they miss it technically, humans catch it instinctively. We’ve all developed a kind of radar for writing that’s technically correct but weirdly soulless. You know the feeling. You start reading something and thirty words in you think “…is this a robot?” and you’re right.

The Correct Way to Use Claude for Writing

Here’s the approach that actually works — and it’s three steps, none of which involve asking Claude to write anything from scratch.

Step 1: Write Your Messy Draft First

Open a document. Any document. Your notes app, a Google Doc, the back of an envelope if that’s what’s available.

Write your thoughts. Badly. In fragments. In bullet points. In whatever chaotic form they take when you’re thinking out loud. Don’t edit. Don’t filter. Don’t worry about structure or grammar or whether your sentences are complete.

Here’s what that looks like in practice:

- climate policy is infuriating because we know exactly what needs 
  to happen and just... don't
- renewables getting cheap fast, EVs catching up, good
- but governments still moving at glacial speed, bad
- feels like we keep having the same conversation for 20 years
- personally skeptical that individual action is the point, 
  think systemic change matters more
- young people justifiably angry about inheriting this mess

Is this beautiful prose? No. Does it sound like a human being who has thoughts and feelings about things? Absolutely yes. This is gold. This is the raw material.

Don’t skip this step. It’s the whole thing. If you start by asking Claude to write and then try to “edit it to sound like you,” you’re fighting uphill the entire time. You’re always reacting to Claude’s choices instead of expressing your own. Start with your voice, then refine it.

Step 2: Give Claude the Right Job Description

Most people mess this up here. They paste their notes into Claude and say “improve this” or “turn this into a paragraph.” Claude, desperately trying to be helpful, then rewrites everything and strips out all your personality in the process.

You need to be specific. You need to give Claude a role — and the role is editor, not author.

Here’s the prompt to copy and use:

“You are an editor, not a ghostwriter. Your job is to refine my draft for clarity, flow, and structure — while keeping my voice, my vocabulary, and my exact opinions completely intact.

Do NOT rewrite my ideas. Do NOT make them more formal if they’re casual. Do NOT soften or remove my opinions. If I write ‘honestly’ or ‘I think’ or ‘this is frustrating,’ keep it.

Here’s my draft:

[paste your bullet points here]

Output: a refined version that still sounds unmistakably like me.”

Notice what’s happening here. You’re not asking Claude to think for you. You’re asking it to help you express what you already think, more clearly. That’s a completely different request, and you get completely different results.

Pro tip: Add context about your tone. “I write casually and directly, I occasionally use sarcasm, and I never use the word ‘synergistic.'” The more specific your constraints, the better the output.

Step 3: Read It Out Loud and Fix What Doesn’t Sound Like You

Claude will give you something good. You’re not done.

Read the output out loud. Actually out loud, not in your head. Your ear will catch what your eye misses.

The moment you hit a sentence and think “I would never say that” — change it. Write it in your words. It doesn’t matter if Claude’s version was technically better. It matters that it sounds like a real person wrote it, and that person is you.

Things to watch for and delete immediately:

Any word ending in “-istic” that you didn’t put there yourself
Passive voice that appeared mysteriously (“it has been noted that…”)
Your strong opinion that somehow became “some argue that, while others believe…”
Sentences longer than you could comfortably say in one breath
Any phrase that sounds like it belongs in a McKinsey slide deck

The finished piece should sound like you on a good day — rested, clear-headed, having thought about the topic properly. Not like you replaced by a very polite robot.

Why Claude Specifically for This

I’ve tested a lot of AI tools on this particular task — the “refine my voice without removing my voice” ask — and Claude handles it noticeably better than most.

The reason, as far as I can tell: Claude actually reads the nuance in the instruction. When you say “keep my casual tone,” most models hear “casual” and nod along, and then hand you back something that’s still weirdly stiff. Claude seems to understand that “casual” means leaving in the contractions, the slightly-too-long sentences, the opinions stated plainly without hedging.

It also handles messy input well. You can paste in bullet points, sentence fragments, half-thoughts, and it will work with what’s there instead of panicking and defaulting to corporate-speak.

And — this is the important one — it knows the difference between “fix my grammar” and “rewrite my personality.” Tell it which one you want. It’ll listen.

Your Cheat Sheet

If you remember nothing else from this article, remember this:

1. Write messy notes first. Your ideas, your words, your opinions. No editing.

2. Paste into Claude as an editor prompt. Key phrase: “keep my voice, my vocabulary, and my exact opinions.”

3. Read the output out loud. Anything that doesn’t sound like you? Change it yourself.

4. The signature stays yours. Because you did the thinking. Claude just helped you express it better.

The Prompt Library (Copy These)

Save these. Adapt them as needed.

For essays and academic writing:

“Edit this draft for clarity, structure, and flow. Keep my voice, my arguments, and my word choices. Don’t make it more formal or academic than it already is. Flag anything that’s unclear, but don’t replace my ideas. Draft: [paste here]”

For professional emails:

“You’re an editor. Tighten this email draft — it still needs to sound like ME, not a corporate template. Keep my tone [casual/direct/warm]. Remove the unnecessary parts. Don’t add phrases like ‘as per my previous email’ unless I wrote them. Draft: [paste here]”

For social media and blog posts:

“Edit this draft for flow and clarity. My writing style is [casual/sarcastic/conversational] — preserve it at all costs. Don’t polish it so much it loses personality. Draft: [paste here]”

For creative writing:

“Act as a line editor, not a co-author. Suggest improvements to sentence rhythm and word choice, but don’t change the story, the voice, or the style. If something is intentionally unconventional, leave it. Draft: [paste here]”

The Bottom Line

Every great writer has an editor. Hemingway had Maxwell Perkins. Every major author you’ve ever admired went through someone else’s red pen before their work reached you.

Your editor now happens to live in the cloud, responds in under three seconds, and has read approximately everything. That’s not a replacement for your brain — it’s a superpower for your brain.

Use it. But use it correctly.

Write first. Let Claude refine. Read it out loud. Fix what doesn’t sound like you.

And for the love of everything — delete the word “synergistic” every single time it appears. Without exception. It has never improved a sentence. It never will.

Role: You are a Senior Copy Editor specializing in stylistic preservation.

Objective: Refine the provided draft for clarity, structural flow, and grammatical precision.

Strict Constraints:

Voice Preservation: Do not sanitize or formalize the tone. If the draft is casual, keep it casual. If it is provocative, keep it provocative.

Vocabulary & Syntax: Retain my specific word choices and sentence structures (e.g., phrases like "I think" or "Honestly" must remain).

No Content Alteration: Do not rewrite my ideas, soften my opinions, or add external perspectives. Your job is to polish the "vessel," not change the "liquid" inside.

Structural Flow: Only adjust transitions and organization to ensure the argument is easy to follow without losing the author’s original intent.

Draft for Review:

[Paste your bullet points/text here]

Requested Output: A polished version of the text that remains unmistakably mine in tone and conviction.

Shay Stibelman is a digital marketing consultant based in Milan, Italy. He helps businesses get smarter with the tools they already have — and occasionally yells at AI output that uses the word “multifaceted” without provocation.

Stop Typing Descriptions Like a Caveman: The Wildest AI Image Tricks Nobody Told You About

So you’re generating AI images by typing things like “a beautiful sunset, cinematic, dramatic lighting, 8k, masterpiece.” And you’re getting… something. Something that sort of looks like what you wanted, if you squint, tilt your head, and lower your expectations.

Meanwhile, other people — the suspiciously talented ones who keep posting incredible AI visuals online and acting like it’s nothing — are doing things that make your workflow look like cave painting. Literally describing images in code. Decomposing photos into layers like it’s Photoshop 3000. Cloning an entire visual style and then changing only the color of someone’s shirt with a text command.

This article is about those tricks. And by the end of it, you’ll either be one of those people, or you’ll at least understand why they’re insufferably smug about their AI-generated product shots.

Let’s get into it.

First, a Very Quick Reality Check

AI image generation in 2025 is not what it was two years ago. We’ve gone from “impressive but kinda weird fingers” to full-blown professional-grade visual engines that can maintain character consistency, render legible text (yes, finally), and edit specific elements of an image without touching the rest.

The tools you need to know about right now: Nano Banana (Google’s Gemini 2.5 Flash Image model, which yes, is actually called that, and no, that name will never not be funny), FLUX.1 Kontext and FLUX.2 from Black Forest Labs, and Qwen-Image-Layered from Alibaba’s Qwen team.

Each one does something that should probably not exist yet. All of them are either free or very cheap. And none of your colleagues know about most of this. You’re welcome.

Trick #1: JSON Prompting — Because “Cinematic Vibes” Is Not a Professional Standard

Here’s the dirty secret of AI image generation: natural language prompts are great for brainstorming, and terrible for repeatability.

You write “futuristic office, moody lighting, professional” and you get something cool. You try to recreate it tomorrow? Different model weights, different random seed, slightly different output. Your “brand consistency” is just vibes at that point.

Enter JSON prompting — and specifically, what it does on Nano Banana.

Nano Banana is built on Gemini 2.5 Flash, which was trained extensively on structured data formats including JSON. This means when you feed it a prompt in JSON format instead of plain text, the model parses it with significantly more precision. Research in 2025 showed that structured prompts improve accuracy on complex tasks by a wide margin — and when you use them correctly, the results are borderline eerie.

Here’s what a basic JSON prompt looks like:

{
  "scene": "minimalist tech startup office, open plan, floor-to-ceiling windows",
  "resolution": "4K",
  "aspect_ratio": "16:9",
  "style": "editorial photography, clean, modern, natural light",
  "mood": "calm, focused, professional"
}

That’s already better than “make it look professional lol.” But here’s where it gets genuinely clever.

The Clone-and-Swap Trick

Want to generate the same image ten times but change only one variable? Say you’re making a product ad and you want to test five different background colors, three different copy headlines, and two different lighting setups. Normally this means ten separate prompt sessions and ten rounds of “why doesn’t this look consistent with the last one.”

With JSON, you build a master template and literally swap out one field at a time:

{
  "scene": "product flat lay, skincare bottle on marble surface",
  "resolution": "4K",
  "aspect_ratio": "1:1",
  "background_color": "dusty rose",
  "lighting": "soft diffused natural light",
  "text_elements": [
    {
      "text": "Pure. Simple. Yours.",
      "position": "bottom center",
      "font_style": "light serif, elegant"
    }
  ]
}

Now change "dusty rose" to "sage green". Regenerate. Change the tagline. Regenerate. You’re not re-describing the whole scene from scratch — you’re editing a config file. This is how product teams generate entire visual catalogs from a single master prompt.

Pro tip on canvas definition

Always define resolution and aspect ratio first. The biggest beginner mistake is skipping this, which results in the model choosing for you — and then you wonder why everything comes out in a slightly odd crop that works for nothing. You can specify 1K, 2K, or 4K. Specify it. Always.

The text rendering game-changer

Nano Banana’s other secret weapon is that it can actually render legible text inside images — something most AI image models handle like a toddler with a Sharpie. But it only works reliably when you use the text_elements array in your JSON prompt, specifying the exact text, position, font style, and size. Vague is the enemy here. Be surgical.

Trick #2: FLUX.1 Kontext — The AI That Finally Listens

You know what’s maddening about most AI image editing? You say “change the jacket to red” and it changes the jacket to red, turns the background slightly warmer, shifts the face a little to the left, and replaces your subject’s nose with something you didn’t ask for.

That’s because traditional inpainting tools work by masking a region, then generating everything from scratch inside that mask. Which sounds fine until you realize “generate from scratch” means the AI gets to make a bunch of decisions you didn’t ask for.

FLUX.1 Kontext does something different. It performs what’s called instruction-based image editing — you tell it what to change, it changes that specific thing and leaves the rest of the image physically untouched. Not “mostly untouched.” Actually untouched.

Tell it “change the shirt color to red.” It changes the shirt. Tell it “remove the glasses.” It removes the glasses and fills in the face correctly. Tell it “swap the background to a rainy London street.” It swaps the background. The character stays the same. The lighting adjusts to match. Nothing drifts.

This is huge for anyone who works iteratively. Which is everyone who makes images professionally.

The Conversational Editing Workflow

Here’s the trick most people miss: because Kontext maintains context across edits, you can stack instructions like a conversation. Start with your base image. Then:

“Add a coffee cup to the table on the left.”
“Make it nighttime outside the window.”
“Give the person a slightly more formal outfit.”
“Add soft lamp lighting from the right.”

Each step builds on the previous result. You’re not regenerating from scratch each time — you’re directing, like a photographer giving notes to a set designer in real time. The creative process actually feels like a creative process instead of a slot machine.

Why the speed matters more than you think

Kontext generates at under 10 seconds per edit. That sounds like a spec-sheet detail, but it’s actually what makes iterative editing viable. When each edit takes 30+ seconds, you stop experimenting. You commit too early. You end up with “good enough.” At under 10 seconds, you iterate freely, and free iteration is where good work happens.

Trick #3: Qwen-Image-Layered — AI Photoshop From the Future (That’s Free)

Okay. This one is genuinely unhinged, in the best way.

Most AI image editors treat your image like a mural painted on a wall. You want to change one part? Good luck not accidentally smearing the rest. The reason is technical but also kind of philosophical: regular AI image models see your photo as one giant grid of fused pixels — foreground, background, shadows, text, everything baked together into one inseparable mass.

Professional design software solved this decades ago. They use layers. You move text without touching the background. You recolor an object without re-rendering the whole scene. AI image models never had this because they operate on flattened images. Until now.

Qwen-Image-Layered is an open-source model from Alibaba that does something no one thought would arrive this fast: it takes a regular flat image and automatically decomposes it into multiple separate, transparent RGBA layers — basically generating a Photoshop PSD file from a JPEG. Automatically. From a single prompt.

You tell it how many layers you want. Ask for 4, you get 4 layers. Ask for 8, you get finer separation. A poster with bold text breaks down into: the background, the main subject, the typography, the decorative elements — each as its own independent, editable layer with its own transparency channel.

Then you edit each layer independently. Want to recolor just the product? Edit layer 2. Want to swap the text? Edit layer 3. Nothing else moves. Nothing else drifts. Because you’re editing a layer, not re-generating an image.

The product photo workflow that kills your shot list

Here’s a real use case that will make any marketer’s eyes light up:

Take one product photo.
Run it through Qwen-Image-Layered and decompose it into 4 layers: background, product, props, text/branding.
Edit Layer 1 (background) to swap in five different scene variations — studio white, kitchen counter, outdoor table, lifestyle setting.
Edit Layer 2 (product) to recolor for different SKU variants.
Recombine.

You just generated 10+ product images from a single original photo without a single additional photoshoot. The kind of thing that used to cost a full day of studio time now costs about twenty minutes and a moderately powerful computer.

Recursive decomposition (yes, it goes deeper)

One more thing: the decomposition is recursive. You can take any layer and decompose that into sub-layers. Need to separate the reflection on the product from the product itself? Decompose the product layer. It goes as deep as you need. This is either incredibly useful or a productivity black hole, depending on your relationship with perfectionism.

It’s free, open-source (Apache 2.0 license), available on HuggingFace, and frankly embarrassing for companies currently charging $50/month for layer-based editing software.

Trick #4: FLUX.2 Multi-Reference Stacking — Consistency at Scale

Here’s a problem that haunts anyone generating AI images for brand work: consistency. You generate a great character, a great style, a great product look — and then trying to replicate it across different scenes is a nightmare. The vibe shifts. The face drifts. The lighting feels different. The brand colors are “close enough” until they’re not.

FLUX.2 — the latest generation from Black Forest Labs — handles this at an architectural level. It can process up to ten reference images simultaneously, merging them into a coherent generation that inherits style, character appearance, and product identity from all of them at once.

This isn’t a filter layered on top. The architecture natively processes multiple visual embeddings and fuses them before the generation step. In practice: feed it your brand photography style guide (3–4 reference images), your character or spokesperson (2–3 images from different angles), and your product (2 images). It synthesizes all of that into a single, coherent visual output that respects all of it simultaneously.

Typography that doesn’t melt

FLUX.2 also significantly improved text rendering inside images. Baseline alignment, kerning, and font weight hold up even in complex compositions. If you’ve ever watched a previous AI model turn the word “SALE” into “SAIE” or “SMLE,” you understand why this is worth celebrating.

Compositional instructions that actually stick

Previous models had a habit of treating complex prompts like abstract mood boards. “Left object at 30 degrees, right object with diffused lighting, center-aligned text” would collapse into a blurry approximation of vibes. FLUX.2 actually follows compositional constraints. Which sounds like the bare minimum, and yet here we are, grateful for it.

Pro Tips Section: The Stuff That Actually Saves You Time

Start with natural language, then convert to JSON. Use a plain text prompt to get a result you like. Then convert that prompt into JSON, adding all the parameters you’d want to control — resolution, style, lighting, composition, text elements. Now you have a reusable template.

Use white backgrounds for single-subject images. Especially when generating product images for e-commerce. White backgrounds give you maximum flexibility for later editing in any tool, and they play nicely with Qwen-Image-Layered’s decomposition engine.

For character consistency across scenes, use Kontext iteratively. Generate your base character once. Then use Kontext’s conversational editing to place them in different environments, outfits, and scenarios — rather than regenerating the character from scratch each time. You’ll get far more consistent facial structure and physical proportions.

Batch with JSON, not with your mouse. If you need 20 variations of the same image, don’t click your way through them. Write a base JSON template, create a simple script that loops through your variables (background color, text, object position), and generate automatically. This is what the power users mean when they say “I scaled avatar creation 15x.” They mean they stopped doing it manually.

For FLUX models: lower the creativity setting when you need realism. The “strength” parameter in image-to-image workflows controls how much the model deviates from your input. High strength = creative reimagining. Low strength = controlled adjustment. Most people leave this at default and then complain the output drifted too much. Turn it down.

Qwen-Image-Layered tip: name your layers. When you decompose, keep a simple text note of what each numbered layer contains. The model doesn’t label them for you, and by layer 6 you will absolutely forget which one is the “text overlay” versus the “foreground decoration.” Future you will be grateful.

The Bottom Line

We’re at a weird inflection point where the gap between “someone who knows these tricks” and “someone who doesn’t” is starting to show up in actual professional output — in how fast people work, how consistent their visuals look, and how many rounds of revision they’re sitting through.

None of this requires a design background. None of it requires coding experience (except maybe the batch JSON scripting, and even that’s one Claude conversation away from done). It requires knowing which tools exist and how to use them in ways that go slightly beyond their default settings.

You now know. Go make something embarrassingly good.

Shay Stibelman writes about AI, digital tools, and the productive chaos of working smarter. He also makes video tutorials for people who’d rather watch someone else figure it out first, which, honestly, is a valid life strategy.

NotebookLM: 10 Tips That Separate the Clickers From the Power Users

So you’ve heard of NotebookLM. Maybe you even tried it. You uploaded a document, asked it a question, it answered, and you thought “okay, cool” — and then went back to doing things the old way.

First of all, same. Second of all, you’re leaving an enormous amount on the table.

NotebookLM is one of those tools that looks simple on the surface and then turns out to have an entire underground city beneath it. The tips below are what separate people who use it occasionally from people who’ve quietly restructured their entire workflow around it. Ranked, because everything is better when it’s ranked.

Let’s go.

Before Anything Else: Why NotebookLM Is Different

Quick reminder, because it matters for everything that follows.

Most AI tools work like a very well-read person: they know a lot of general stuff, and they answer from that general knowledge. The problem? Sometimes they make things up. Confidently. With a straight face. It’s called “hallucination” and it’s the AI equivalent of that colleague who always sounds certain and is occasionally completely wrong.

NotebookLM works differently. It only answers from the documents you give it. Nothing else. It’s not browsing the internet, it’s not drawing on general knowledge — it’s reading your stuff and synthesizing your stuff. Every answer comes with a citation, which you can click to verify. Every. Single. One.

This is not a limitation. This is the whole point. And once you internalize that, these tips will make a lot more sense.

Tip #1: One Notebook, One Topic. No Exceptions.

This sounds boring. It is also the most important thing in this article.

The temptation is to create one giant notebook and throw everything into it — all your projects, all your documents, all your research. It feels organized. It is not organized. It’s a junk drawer with a label on it.

When you mix unrelated content in a single notebook, the AI’s ability to find connections and surface relevant information gets diluted. It’s like asking a very smart person to think about marketing strategy, project timelines, HR policy, and last year’s invoices all at the same time. Even they’d look at you funny.

The fix is simple: one notebook per project, per topic, per purpose. A “Marketing Research” notebook. A “Client X Project” notebook. A “Competitor Analysis” notebook. Each one becomes a focused little expert on exactly that thing and nothing else. The results are dramatically better.

Input discipline. That’s the whole tip.

Tip #2: The Note-to-Source Loop (a.k.a. the Recursive Brain Trick)

This one is a little mind-bending but very worth it.

Here’s the problem: when you have 10 raw documents in a notebook, there’s a lot of noise. Repetition, tangents, conflicting info, irrelevant sections. The AI does its best, but it’s working with messy material.

Here’s the fix: ask NotebookLM to synthesize all of it into one clean, structured note first. A comparison table, a summary document, a structured overview — whatever fits your purpose. Then take that note, clean it up manually if needed, and re-upload it as the only source in the notebook. Deselect all the originals.

Now the AI is working from a clean, verified, “gold standard” document you’ve curated yourself. The Audio Overviews it generates will be sharper. The slide decks will be more focused. The answers will be cleaner.

You’re essentially using the AI to make better raw material for the AI. Recursive, slightly philosophical, extremely useful.

Tip #3: Custom Instructions — All 10,000 Characters of Them

NotebookLM lets you set custom instructions for how the AI should behave in your notebook. Think of it as a permanent system prompt — a briefing you give the AI before every single conversation.

NotebookLM recently expanded this to 10,000 characters (that’s a lot of characters), which means you can now write genuinely detailed instructions. Not just “be formal” — but an entire persona, a role, a set of constraints, a preferred output format, the works.

Power users keep a “persona library” and paste in different ones depending on the task:

The Socratic Coach — doesn’t give you answers, asks you questions about the material so you actually have to think (and retain things)
The Senior Strategy Consultant — cuts straight to SWOT analysis, actionable recommendations, and executive-level framing
The Devil’s Advocate — specifically looks for holes in your argument, contradictions in the data, and reasons your plan might fail

That last one, by the way, is genuinely useful before any big presentation or proposal. Better to hear the problems from your AI than from your client.

Tip #4: Deep Research for the Gaps in Your Own Knowledge

Your internal documents are great, but they don’t know what happened last month. They don’t know what your competitor just announced. They don’t know the regulatory change that was published last week.

NotebookLM has a Deep Research mode that goes out and browses live websites to fill those gaps. You give it a question, it does the legwork across hundreds of sources and comes back with a cited report. You then import that report as a source into your notebook.

The result is a hybrid knowledge base: your internal documents plus the current state of the world, all in one place, all queryable. It’s the difference between working with a snapshot and working with a live picture.

Tip #5: Stop Generating One Audio Overview and Walking Away

The Audio Overview feature — where NotebookLM generates a podcast with two AI hosts discussing your documents — has, remarkably, been used by over 10 million people a month. Which means most of those people generated it once, listened passively, and called it done.

Don’t do that.

The actual power move: customize the prompt before you generate. You can tell the hosts what to focus on, what tone to take, what angle to explore. “Focus on the financial implications.” “Take a more skeptical tone.” “Debate the two main approaches and don’t pick a winner.”

Then generate multiple episodes from the same material, each exploring a different angle. You now have a mini podcast series about your own documents, which is either very cool or very weird, depending on your personality.

And in Interactive Mode, you can join the conversation yourself. Interrupt the hosts. Ask them to go deeper on a specific point. Act as a guest on your own podcast about your own meeting notes. Honestly? We live in remarkable times.

Tip #6: Query Across Notebooks (The Second Brain Move)

For a while, the biggest frustration with NotebookLM was that notebooks were silos. Your Marketing notebook couldn’t talk to your Finance notebook. You had multiple specialized experts who didn’t know each other existed.

That changed. You can now connect multiple notebooks through the Gemini app and query across all of them at once. So when the strategic question requires both the marketing data and the financial data, you don’t have to jump between two notebooks and manually connect the dots yourself.

This is what people mean when they talk about a “second brain.” Not one massive document dump — a network of specialized, focused notebooks that can be interrogated together when needed.

Tip #7: Ask It What’s Missing (The Source Gap Prompt)

Most people use NotebookLM as a summarizer. Summarize this. Explain that. What are the key points?

Useful. But not the most powerful thing you can do.

The most powerful prompt in the advanced user toolkit is the Source Gap prompt: ask the AI to tell you what’s not in the documents. What’s missing. What assumptions are unproven. Where the sources contradict each other. What questions the material raises but doesn’t answer.

You’re asking it to be an auditor, not a summarizer. And auditors find the things that matter — the gaps, the blind spots, the weak links in the argument. For market research, strategic planning, or any document where the stakes are high, this is invaluable.

“What important context is not covered in these documents?” is one of the most useful prompts you will ever type.

Tip #8: Transcribe Everything. Seriously, Everything.

NotebookLM supports audio and video uploads (YouTube links, MP4 files, MP3 recordings), and it will transcribe and analyze them just like text documents.

Think about what that means. Call recordings. Client interviews. Conference presentations you attended. Internal webinars. That hour-long product review meeting where someone promised to send the notes and never did.

All of it becomes searchable, queryable, and summarizable. You can turn a recorded client call into structured notes, FAQs, or a follow-up email in minutes. A recorded training session becomes a searchable knowledge base. A YouTube tutorial on a tool you’re learning becomes source material you can interrogate.

Third-party transcription services cost money and still give you a wall of text you have to process yourself. NotebookLM transcribes it and puts it directly into an environment where you can ask questions about it. That’s a different category of useful.

Tip #9: Revise Your Slides Like a Demanding Art Director

NotebookLM can generate slide decks directly from your source material. One click, full deck, done. Which is impressive enough on its own.

But the real move is what you do after. Once the deck is generated, you can go into the chat and tell it to revise specific slides. “Redo slide 4 to focus on the executive summary.” “Make slide 7 more visual and less text-heavy.” “The intro slide needs to start with the problem, not the solution.”

You’re essentially art-directing an AI slide designer who doesn’t take things personally and never says “but I thought we agreed on this layout.” Just iterate until it’s right, then export to PPTX and polish the final version yourself.

It won’t replace a good designer for anything that needs to look genuinely beautiful. But for an internal strategy presentation at 9am on a Tuesday? It’ll get you there.

Tip #10: Use the Citations to Navigate, Not Just to Verify

Every answer NotebookLM gives you includes clickable footnote-style citations linking directly to the source passage. Most people click them occasionally, to check if the AI got it right.

Power users click them constantly — not to verify, but to navigate.

Got a 500-page document? Don’t use Ctrl+F and hope for the best. Ask NotebookLM a question about the topic you need, and click the citation. You’ve just jumped directly to the relevant section using semantic search. The chat panel becomes a high-speed navigation interface for dense material.

For anyone who works with long contracts, technical documentation, lengthy reports, or academic papers, this alone is worth the price of entry. (Which, for the free tier, is zero. So.)

Bonus: The Three-Tool Chain That Power Users Actually Use

Here’s a workflow that’s become increasingly popular among people who’ve fully leaned into AI-assisted research:

Step 1 — Perplexity for initial web sourcing. It’s great at finding high-quality, current URLs on a topic quickly.

Step 2 — NotebookLM for deep, grounded analysis. Import those URLs, cross-reference with your internal documents, generate structured notes and synthesis.

Step 3 — ChatGPT or Claude for creative output. Take the refined synthesis from NotebookLM and move it to a more creatively fluent model for drafting, writing, or ideation.

Each tool does what it’s best at. Perplexity searches. NotebookLM synthesizes accurately. Claude or ChatGPT writes fluidly. Together, they cover the full research-to-output pipeline without any single tool having to be great at everything.

The Bottom Line (Again, But With More Conviction This Time)

NotebookLM is not a chatbot. It’s not a search engine. It’s not a note-taking app.

It’s a precision research environment that happens to also generate podcasts, slide decks, mind maps, and video summaries from your documents. Used casually, it’s a useful time-saver. Used strategically — with focused notebooks, custom personas, recursive refinement, and the right prompts — it’s genuinely a different way of working with information.

The tips above aren’t tricks. They’re a framework. Start with Tip #1 (notebook discipline), layer in the others as they become relevant to your work, and don’t try to implement all ten in the first week. You’ll lose your mind. Or at least your enthusiasm.

Pick one. Try it. See what changes.

The 500-page document isn’t going to read itself. But NotebookLM will, and it’ll tell you exactly what’s in it, what’s missing, and what you should probably do about it.

Shay Stibelman is a digital marketing consultant based in Milan, Italy. He helps businesses work smarter with the digital tools they already have — or the ones they really should have by now.