Each section names the problem, the idea in one line, what you will be able to do, and the method from the book.
1. One sheet, one job
The problem: Your files break the moment someone else touches them.
A workbook is easy to trust when every sheet has one job and every number has one home.
By the end of the chapter you can:
- Lay out any workbook so others know where to type and what to trust
- Move every assumption into one clearly marked place
- Write a ReadMe sheet that lets someone else run the file
The three-layer build
Split the work into layers that flow in one direction. Each sheet does one job, and you can always say where a number came from by walking back along the flow.
Inputs: what you type → Workings: what Excel calculates → Report: what people read
2. IF, AND, OR: teach Excel to decide
The problem: You do by hand what one formula could do.
Write the decision in plain words first. The formula is then just a translation.
By the end of the chapter you can:
- Write IF formulas that make yes/no decisions
- Combine conditions with AND and OR
- Turn written business rules into a reliable calculator
IF: one test, two answers
IF asks a question that is either TRUE or FALSE, then returns one thing if it is true and another if it isn't.
| Test | Means | Notes |
|---|---|---|
| D2>=C2 | Sales at least equal to target | Compare numbers or cells |
| B2="Rural" | The territory is Rural | Text goes in double quotes; case doesn't matter |
| E2="" | E2 is empty | Use "" for blank; ISBLANK(E2) works too |
| F2<>0 | F2 is not zero | <> means “not equal to” |
| A2<=ReportDate | On or before the report date | Dates compare like numbers |
3. VLOOKUP and its four traps
The problem: You spend hours matching two lists by eye.
VLOOKUP works, until it doesn't. Know its four traps and you can fix any file that uses it.
By the end of the chapter you can:
- Read any VLOOKUP and say what it does
- Spot the four traps in inherited files
- Fix or replace a fragile VLOOKUP
VLOOKUP, piece by piece
- Excel looks down the first column of the range for the key in A2.
- When it finds the first match, it moves across to the 4th column of the range.
- It returns that value, or #N/A if the key isn't there (with FALSE).
| Code | Item | Unit | Rate |
|---|---|---|---|
| P-1001 | Paracetamol 650 mg (strip of 15) | Strip | 30.50 |
| P-1002 | Cetirizine 10 mg (strip of 10) | Strip | 18.00 |
| P-1003 | ORS sachet 21 g | Sachet | 21.00 |
| P-1004 | Vitamin C 500 mg (strip of 15) | Strip | 26.00 |
4. Your first pivot
The problem: You wait days for a summary you could make in minutes.
Every pivot answers the same question: how much (or how many) of this, broken down by that.
By the end of the chapter you can:
- Turn a business question into rows, columns and values
- Build, format and sort a pivot
- Choose a layout that reads like a report
Five clicks to a first answer
- Click inside the Table. Any cell of tblSales.
- Insert › PivotTable › From Table/Range. Excel fills in tblSales; choose New Worksheet; OK.
- Tick Region. It goes to Rows, because it is text.
- Tick Amount. It goes to Values as Sum of Amount, because it is a number.
- Drag FY to Filters and pick FY 2025-26.
5. Quick fixes
The problem: You clean the same messy export every month.
For a one-off file, a handful of built-in tools fix most problems in minutes.
By the end of the chapter you can:
- Split, rejoin and tidy text without retyping
- Use Find & Replace without damaging data
- Finish a one-off clean-up in minutes
Text to Columns: split one column into several
- Insert empty columns to the right. Text to Columns overwrites the cells beside it.
- Select the column. Data › Text to Columns.
- Choose Delimited. Tick Comma (or Space, or Other: -).
- Set each new column's type. Text for codes and PINs; Date (DMY) for day-first dates; Do not import for parts you don't need.
- Finish. Check the result before deleting the original.
Column data format: ○ General ● Text ○ Date: DMY
Destination: =$B$2
6. The six charts
The problem: Your charts start arguments instead of ending them.
Six chart types answer almost every business question. Know them well and skip the rest.
By the end of the chapter you can:
- Pick the right one of six charts in seconds
- Build each in Excel with its settings
- Explain why 3D, radar and most pies make reading harder
Column and bar charts
Columns stand up; bars lie down. Use columns for time with only a few periods, such as four quarters. Use bars for ranking, and whenever the labels are long: product names read easily on the left and never need tilting.
| Setting | Where | Use |
|---|---|---|
| Start at zero | Format Axis › Bounds › Minimum | Always 0 for bars and columns |
| Gap Width | Format Data Series › Series Options | 50–80%: bars wider than the gaps |
| Largest at the top | Format Axis (vertical) › Categories in reverse order | Bar charts from a sorted range |
| Data labels | Chart Elements (+) › Data Labels | Then delete the value axis |
7. Pick 5 to 9 numbers
The problem: Your dashboard is full of charts nobody acts on.
Choose a few numbers, define each exactly, and decide in advance what counts as good, watch and bad.
By the end of the chapter you can:
- Choose KPIs that pass four tests
- Write a KPI definition card
- Set green, amber and red thresholds
KPI or just a metric?
Every KPI is a metric, but most metrics aren't KPIs. A key performance indicator passes four tests.
| Test | Question | Fails it |
|---|---|---|
| Decision | Does it change what someone does? | Website visits, if nobody acts on them |
| Owner | Is one person answerable for it? | “Company morale” |
| Target | Is there a clear good and bad? | Number of SKUs |
| Movable | Can the team influence it? | Raw material prices |
8. The standard pack
The problem: The monthly report takes ten days and gets two minutes.
Eight reports cover what most businesses need each month. Start from them and add only what your readers' decisions require.
By the end of the chapter you can:
- Name the eight standard reports and what each answers
- Design a headline page
- Lay out a pack readers can navigate
The headline page
Page one is the most read page in any pack. It should let a reader stop there and still know how the month went.
| Holds | Kesar, March 2026 |
|---|---|
| Net sales vs budget and last year | ₹1,355.6 lakh · -4.6% vs budget · 5.2% vs LY |
| Gross margin % | 36.2% vs 37.0% budget |
| EBITDA | ₹210.9 lakh vs ₹223.0 lakh budget |
| Year to date sales and EBITDA | ₹15,932.7 lakh (-0.9%); ₹2,378.1 lakh (-3.8%) |
| Receivables over 90 days | ₹61.5 lakh |
| Cash at month end | ₹643.0 lakh |
| Three things that need action | Mumbai distributor · snack pricing · freight costs |
9. A first report in an hour
The problem: Your Excel files are too big, too slow and too many.
Load the data, drag a few fields, and see Power BI work, before learning the theory.
By the end of the chapter you can:
- Load an Excel workbook into Power BI Desktop
- Build cards, a bar chart, a line chart and a slicer
- See how visuals filter each other
Install and open Power BI Desktop
- Install. From the Microsoft Store (search Power BI Desktop), which keeps it updated, or Microsoft's download page.
- Open it. Close the start screen; signing in isn't needed for building.
- Find the three views. On the left: Report (pages and visuals), Table (the data) and Model (relationships).
- Find the panes. On the right: Filters, Visualizations and Data.
| View | Like in Excel |
|---|---|
| Report | A dashboard sheet |
| Table | Looking at a Table's rows |
| Model | No equivalent: tables and the lines between them |
10. Record your first macro
The problem: You repeat the same clicks every Monday morning.
The macro recorder writes the code while you click. It's the fastest way to start, and to learn.
By the end of the chapter you can:
- Record and run a macro
- Choose where a macro is stored
- Save macro workbooks correctly
Record, do, stop
- Developer › Record Macro. The Record Macro box opens.
- Name it. FormatReport: letters, numbers and underscores, no spaces.
- Shortcut (optional). Ctrl + Shift + a letter, so it doesn't replace a normal shortcut.
- Store macro in: This Workbook. Next page explains the choices.
- OK, then do the task. Select A1:F1, bold, fill navy, white text, autofit columns A:F.
- Developer › Stop Recording. Or the stop button on the status bar.
Macro name: FormatReport
Shortcut key: Ctrl + Shift + R
Store macro in: This Workbook
Description: Formats the weekly sales headings
11. Spread and risk
The problem: A decision rests on an average that hides the truth.
Two things with the same average can carry very different risk. Spread is the number that shows it.
By the end of the chapter you can:
- Describe spread with range, percentiles and standard deviation
- Compare risk across items
- Present a plan as a range
Range and percentiles
| Measure | Excel | Says |
|---|---|---|
| Range | =MAX(r)-MIN(r) | Distance between extremes; one odd value changes it |
| Interquartile range | =QUARTILE.INC(r,3)-QUARTILE.INC(r,1) | Spread of the middle half |
| 90th percentile | =PERCENTILE.INC(r,0.9) | “9 times in 10, within this” |
This article is adapted from the book. Examples marked “Example” use invented names and numbers; company stories are based on public sources listed in the book. For general learning; use your own judgement for your situation.
