Excel formulas and pivots on demand
Listen to this lesson
Nigerian voice narration
Fictional training case. Bello spends hours monthly rebuilding the same expenditure summary by department from a register with columns Date, Department, Item, Amount. This lesson teaches describing the desired result in plain English so AI generates the correct formula, then verifying it manually. Work through the example, identify the evidence needed and practise the method with cleared or synthetic inputs. Any additional figures in the explanation are separate practice examples, not claimed rows in a downloadable file.
Worked example
Bello tells AI: 'I want total spending by department per month' — not 'write me a SUMIFS formula.' AI returns a SUMIFS formula referencing the Department and Date columns with a start-of-month and next-month date range, explains it step by step, and warns that date-month matching requires the Date column to be true dates, not text. Bello tests it on three departments she can total manually: Finance (₦3.4m), the formula also returns ₦3.4m — confirmed. For Procurement, the formula returns ₦0 because that department's dates were stored as text, exposing a mismatch AI's explanation had flagged as a risk. She fixes the date formatting, reruns, and gets the correct ₦2.8m.
The method
Describe the business result you want in plain language rather than guessing function names, then manually recompute 2-3 results by hand or pivot table to confirm the formula works correctly before trusting it across the full dataset.
Worked practice and common mistakes
Use actual date boundaries in monthly SUMIFS: on or after the first day and before the first day of the next month. Do not wildcard-match month names against numeric dates. For a pivot’s amount field, choose Sum rather than Count. Refresh the pivot and confirm that its source includes new rows before presenting the latest monthly total.
Review before using the result
Plain-English goals produce better formulas than guessed function names. Always test new formulas on a copy against manually verified totals before relying on them for a report.
Your workplace practice
Use the recommended free assistant with this lesson's fictional example. I have a register with columns: Date, Department, Item, Amount. Give me Excel formulas or steps to get: total by department per month, top 5 items by amount, and a month-over-month change column. Explain each simply. Keep a copy of the input and draft. Compare the result with the verification steps above, record any correction, and retain placeholders where evidence is missing. The expected result is a checked draft for human review, not an automatically approved decision. Course examples and practice responses are fictional; amounts, dates, rules and thresholds are not official guidance or supplier quotations.
Open Microsoft Copilot (free tier) and paste this prompt there. Explains Excel formulas and pivot-table steps for you to test in your own workbook.
I have a register with columns: Date, Department, Item, Amount. Give me Excel formulas or steps to get: total by department per month, top 5 items by amount, and a month-over-month change column. Explain each simply.
Why this tool: Explains Excel formulas and pivot-table steps for you to test in your own workbook.
Hit a free limit? Switch to ChatGPT or Gemini and continue — the prompt works in any assistant.