From Zero to Excel Master at RPIT

A client once told RPIT their spreadsheet “loses itself” every time they scroll — the header row was frozen and they didn't know what that meant or how to undo it. Aditi's first job is to make sure she never sounds confused about that call.

Why a client asks this: Freeze Panes and AutoFilter are the two settings every client expects by default and almost never asks for by name — they just say “I can't see the headers” or “I can't find anything.”

“How many kids do we have in Class 10?” is the single most common question a school office asks RPIT, usually over the phone, usually while Aditi is mid-lunch.

Why a client asks this: Filtering answers a one-off question in five seconds without writing a single formula — the fastest tool always wins for a one-time ask.

Before RPIT builds anyone a dashboard, they ask the same rough-and-ready question a school accountant asks first thing every morning: who's furthest behind on fees, right now, today.

Why a client asks this: Sorting is a one-time snapshot — useful for a quick “top of the list” answer, but the moment someone pays, the sort is stale. That's the seed for the dashboard Aditi builds in Chapter 4.

A parent's pincode starts with a 0 in some states. The moment that cell is treated as a number instead of protected text, Excel silently eats the zero — and RPIT has had more than one client mail a form back to the wrong region because of it.

Why a client asks this: A custom format changes only how the number displays, not the value stored — which matters, because it means SUMs and lookups on that column still work normally.

A new admission walks in mid-term. The school wants that student in the system today, everywhere the existing 1,000 already are.

Why a client asks this: This is the exact failure mode that breaks real client workbooks: someone adds a row to one sheet and forgets the other two, and every formula referencing that Student ID downstream returns #N/A.

“Can you just change every ‘Bengaluru’ to ‘Bangalore’?” is a completely reasonable client request. What they don't know is that another column is silently depending on the old spelling.

Why a client asks this: Every VLOOKUP in this workbook is only as reliable as an exact text match. This question exists to make that failure visible on purpose, in a safe practice file, instead of in front of a client.

Before COUNTIFS and SUMPRODUCT, every RPIT trainee has to be fast and correct with the five plain functions — because half of every advanced formula is one of these wearing a condition.

Why a client asks this: One of these five is a deliberate trap — see below — and it's the single most common beginner mistake RPIT sees in client-built sheets.

The single most-repeated correction RPIT's trainer gives new hires: “you copied that formula down and it moved.” This exercise is designed to make that mistake happen once, on purpose, so it never happens on a client file.

Why a client asks this: Relative references (L2) shift when copied; absolute references ($L$2) don't. Most real-world formula bugs are one of these used in the wrong place.

A logistics client once wanted a quick “region code” for routing without adding a whole new lookup system — just the first three digits of the pincode, which roughly map to a postal region in India.

Why a client asks this: LEFT/MID/RIGHT are the cheapest way to split apart a piece of structured text (a pincode, an ID, a product code) without a full text-to-columns operation.

A teacher asked RPIT for “just a quick picture” comparing each class's average — not a dashboard, not a report, just something she could screenshot into a WhatsApp message to the principal.

Why a client asks this: Not every chart request needs a PivotTable behind it — sometimes the fastest honest answer is a plain column chart off a small summary table.

The first question every school principal asks after exam season, every single time, without fail.

Why a client asks this: COUNTIF is the single most-used function in every RPIT client file — one condition, one range, no array thinking required.

A parent-teacher meeting is scheduled for Class 10 only, and the coordinator wants the pass count before she walks in.

Why a client asks this: The moment a question has two conditions joined by “and”, COUNTIF alone can't do it — that's the signal to reach for COUNTIFS.

A Class 8 parent asks how their child compares to the class average — RPIT's client (the school) wants that number ready before they call the parent back.

Why a client asks this: AVERAGEIF is COUNTIF's cousin for averages — same single-condition shape, different aggregation.

The accountant's month-end question: how much money is currently overdue, full stop, before we start breaking it down by class or payment mode.

Why a client asks this: SUMIF answers exactly the kind of “how much, total” question that shows up in every finance conversation RPIT has with a client.

The office wants to know how UPI collections stack up against cash and cards before deciding whether to push parents toward UPI harder next term.

Why a client asks this: SUMIFS is SUMIF with room for more than one condition — here there's only one, but the syntax (sum range first, then condition pairs) is what carries forward to every multi-condition total.

A genuine, unglamorous client request: the school nurse wants a blood-group headcount on file before the annual health camp.

Why a client asks this: This is COUNTIF used the way it shows up most often in real client work: not once, but stamped down a short list of categories.

The workbook already highlights low Percentage and FAIL results — the trainer's brief is simple: “add one more, for attendance, and don't touch the two that are already there.”

Why a client asks this: Conditional formatting turns a static table into something that flags problems on its own, the moment someone opens the file — no formula-reading required from the client.

A client typed a Student ID with a typo, and the workbook they'd been handed showed a wall of #N/A across the row — which looked broken, even though nothing was actually wrong with the formulas.

Why a client asks this: IFERROR doesn't fix a wrong lookup — it makes a correct lookup's failure state readable by someone who isn't you.

RPIT hands over a lot of workbooks to people who will never learn what $L$2:$L$1001 means, but who can absolutely read a formula that says AllPercentages.

Why a client asks this: A named range doesn't change what the formula computes — it changes whether the next person who opens the file understands it at a glance.

“What did the overdue balance look like on the 1st of the month?” is a question a school accountant will ask eventually — and TODAY()-based formulas can't answer it retroactively, because they only ever show today.

Why a client asks this: This is how you turn a moving, live number into a permanent “as of today” snapshot for reporting — the pasted values won't change even if the source data does tomorrow.

“VLOOKUP works until someone inserts a column,” Aditi's trainer says, “and someone always inserts a column.” This is where she learns the fix that survives it.

Why a client asks this: VLOOKUP's third argument (2) is a hardcoded column position — insert a column into Marks between A and B and every VLOOKUP silently starts pulling the wrong column. INDEX/MATCH points at the Student Name column directly, so it survives that exact kind of edit.

Prize-giving season. RPIT's client wants one name per class, and wants it fast, without twelve separate manual lookups.

Why a client asks this: MAXIFS is the direct, modern answer; understanding the equivalent MAX+IF array logic matters because not every client is on a current Excel build.

A school admin office wants a quiet, non-judgmental flag on students whose age looks out of step with their class — a signal to check the record, not an accusation.

Why a client asks this: This kind of “flag anything unusual” formula is common in RPIT's admin-office work — but it only earns its keep if the threshold is actually calibrated to the real data, which this question deliberately tests.

The vice-principal has a hunch: students who fail are also the ones with poor attendance. RPIT's job is to confirm it with a number, not a hunch.

Why a client asks this: SUMPRODUCT multiplies two TRUE/FALSE arrays together (TRUE=1, FALSE=0) and sums the result — it's the classic way to count rows matching two conditions across arrays without COUNTIFS' more rigid syntax, and it's the natural bridge into array thinking.

Someone on the accounts team once typed a Fee Paid amount larger than the Annual Fee itself — a typo, not fraud, but it took a week to notice. RPIT's fix: don't let it happen at all.

Why a client asks this: Data Validation with a custom formula checks a rule against other cells, not just a fixed range or list — this is the difference between a dropdown and real business logic.

A parent asks the single most common question a teacher gets: “what does my child actually need to score to reach 60%?” Goal Seek answers it without a single manual guess-and-check.

Why a client asks this: Goal Seek works backwards from a target instead of forwards from an input — exactly the shape of “what would it take” questions clients ask constantly, and exactly the kind of question a plain formula can't answer directly.

RPIT built a client a bulk-WhatsApp reminder tool once, and every message needed to read like a person wrote it — not a mail-merge. It starts with a single concatenated sentence, per row.

Why a client asks this: String concatenation (&) is what turns columns of raw data back into a sentence a human being will actually read — the last step before any real communication goes out.

The management committee is debating a blanket discount to boost enrollment next year — they want to see the revenue impact at a few different discount levels before they decide.

Why a client asks this: A Data Table recalculates a formula across a whole list of “what if” inputs in one shot — far faster than manually changing one cell and re-checking the total four separate times.

The same committee wants a second comparison: not a discount hypothetical, but the gap between what's actually been collected so far and what would happen if every single overdue balance got paid tomorrow.

Why a client asks this: Scenario Manager, unlike a Data Table, is built for comparing a small number of named, saved states side by side — useful when the “what if” isn't one sliding variable but a specific situation.

A client's data-entry clerk once overtyped a formula cell by accident and didn't notice for two weeks. RPIT's answer: protect the sheet so formula columns can't be touched, but leave the marks-entry columns wide open.

Why a client asks this: By default, every cell in Excel is “Locked” — but locking only takes effect once sheet protection is switched on. The real skill is unlocking the specific cells that should stay editable before protecting the rest.

This is the exact table Aditi's trainer says every school administrator eventually asks for, in almost these exact words: “can I see average performance by class and section, one table, one screen.”

Why a client asks this: A PivotTable answers a two-dimensional question (class × section) that would need 48 separate AVERAGEIFS formulas to replicate manually — and updates itself the instant the source data changes.

RPIT's client is deciding whether to keep offering six payment modes or trim the list — they need to know both how much money and how many parents use each one, side by side.

Why a client asks this: The most-used payment mode and the highest-earning payment mode aren't always the same one — this table exists specifically to check whether they line up.

Same committee, next slide: they want the Paid / Partial / Pending / Overdue split as one glance-able chart, not a table of numbers.

Why a client asks this: A Pivot Chart is a normal chart wired directly to a PivotTable — it inherits the same filters and refreshes with the same data, so it's never out of sync with the numbers behind it.

The whole point of a dashboard is that the client doesn't call RPIT every time they want to check a different class — they click a button themselves.

Why a client asks this: A Slicer is a visible, clickable filter control — the difference between a report someone reads once and a tool someone actually uses on their own.

This is the sheet RPIT actually hands the client. Everything before this question was practice for this one moment: one screen, no scrolling, every number live.

Why a client asks this: “Driven by formulas, not typed numbers” is the whole test — a dashboard that needs to be manually updated every month isn't a dashboard, it's a chore with extra steps.

RPIT's more technical clients have started asking for Power Query specifically — it's worth showing Aditi the difference in approach, not just the destination.

Why a client asks this: A worksheet formula recalculates live, cell by cell; a Power Query step is a repeatable, auditable transformation applied once and then refreshed on demand — the right tool changes once a workbook is doing real data-cleaning rather than live calculation.

A client on a current Microsoft 365 subscription asked why RPIT still writes VLOOKUP when their own version of Excel has something newer — fair question, worth answering hands-on.

Why a client asks this: XLOOKUP drops the fragile “column index number” VLOOKUP relies on, can search in either direction, and returns a clean, readable #N/A-free result by default with an optional fourth argument — but it's genuinely newer than a lot of installed Excel versions.

A client asked, half-joking, whether Excel could “just remember what I click every morning.” It can — that's exactly what a macro is.

Why a client asks this: This is the smallest possible macro — one filter action — chosen deliberately, because the value of macros isn't complexity, it's turning a repeated manual action into one click.

This is the question with no scaffolding: RPIT's client wants a list of students who are failing academically but whose fees are fully paid — good candidates for free extra academic support, since the school isn't chasing them for money. No helper columns allowed, on either sheet.

Why a client asks this: This is what “cross-sheet, no helper columns” actually tests: whether you can combine a condition that lives on one sheet with a condition that lives on another, inside a single formula, instead of reaching for a staging column as a crutch.

The final ask, and the one most schools actually pay RPIT for: a parent walks in and wants everything about their child — marks, fees, contact details — on one clean sheet of paper, not three spreadsheet tabs.

Why a client asks this: This closes the loop on everything in Chapters 1–4: it's the same VLOOKUP logic from Question 21, the same string-building from Question 27, applied as a single-record report instead of a bulk table — which is what “knowing Excel” actually looks like in front of a client.