You will take a spreadsheet task you do by hand and rebuild it with formulas and checks produced with an assistant.
Open your task audit from lesson 1.4 and pick one recurring spreadsheet task, one where you already know what the right answer looks like. Then make a copy of that file and do all of the work below on the copy. The original keeps running your normal process until you are confident the new version works. That way, nothing you try here can affect a report someone is waiting for.
This exercise uses three things you can already do: get a formula from a description, fix a formula you inherited, and have an assistant run an analysis you can check. Together, they let you turn a manual task into one that mostly runs itself, with a check that tells you when something has gone wrong. Allow about forty minutes for the rebuild, plus the time of one real cycle of the task afterwards.
Good candidates are a weekly or monthly report that pulls figures together, a reconciliation of two lists, or a clean-up you repeat each time a new export arrives. Whichever you choose, you need to know what a correct result looks like, because that is how you will test the rebuilt sheet.
Before you change anything, note your baseline time. This is how long the task takes by your current manual method, taken from your log. You will compare the new version against it.
Work through the task one step at a time. For each manual step, describe the layout of your data and the result you want. Then ask the assistant for a formula with a one-line explanation of each part, as in lesson 5.1, "Describe the result and get the formula". Put the formula into the copy and test it on a case where you already know the answer. If it does not match, look for causes using the lesson 5.2 method before you ask for a replacement.
Once a formula works, give it a short plain-language comment. A cell note or a line of text beside the column is enough, for example "Total paid orders for this region in the report month." Comments make the sheet usable by someone else. They also help you in six months, when you have forgotten how you built it.
A check cell compares a result from your new formulas with a figure you trust from somewhere else, and shows clearly whether they agree. It is the single most useful thing you can add to a spreadsheet that people rely on. The trusted figure can be a total from the source system, a bank statement balance, a count of rows in the original export, or the same figure calculated a different way. The cell shows OK if the two figures match within a small tolerance, and CHECK if they do not. Ask the assistant to write the check cell and to explain how it works.
Put the check cell at the top of the report and make it stand out, because a check nobody looks at protects nobody.
You can also add a second kind of check: a formula that counts rows whose value, such as region, is not on an approved list.
Priya produces a report every Monday. She measured her baseline first: 125 minutes one week and 115 the next, an average of 120 minutes (these are example figures). On a copy of the file, she replaced her manual filtering with one SUMIFS formula per region, built as in lesson 5.1. She also added formulas for the order count and the average order value, and commented each one.
Her check cell compared the regional totals added together with the total of all paid orders in the month. She calculated that total with one SUMIFS that ignores region. The two figures should be equal, so the cell showed either OK or CHECK.
On her first real run it showed CHECK, because the regional totals were a few hundred dollars short. She found an order where the region had been typed as "Nort", so it fell into no region at all. Her old manual method had been missing the same order for weeks. She fixed the entry, asked the assistant for a formula that counts rows whose region is not on the approved list, and added it as a second check.
The rebuild took her 90 minutes, a one-off cost. Her first real cycle with the new sheet took 45 minutes, from opening the export to sending the report, including reading the check cells and writing her commentary. That is 120 - 45 = 75 minutes saved per report. She saved 75 minutes in week 1 and 150 minutes in total by week 2, so the 90-minute rebuild was paid back by the second week. She logged both figures in her sheet, with setup time recorded separately for module 9.
Run the new version on one real cycle of the task and log its time next to your baseline. Record setup time separately from per-cycle time so that module 9 can count it properly. Keep the old method running alongside until the new sheet has run cleanly for a cycle or two, then retire it.
You are finished when you have all of the following. Your rebuilt copy uses formulas in place of the manual steps, and each formula has a plain-language comment. At least one check cell compares the result with a trusted figure and shows clearly whether they agree. The new version has run on one real cycle. Your log shows that cycle's time next to the baseline, with setup time noted separately.
Choose your task now and make the copy of the file before you do anything else.
Deliver the rebuilt sheet with commented formulas, a check cell, and a note comparing time taken with your baseline.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).