Lesson 7 of 10 · 12 min
AI and Excel: formulas, fixes and clean data
এআই ও এক্সেল: ফর্মুলা, সমাধান ও ডেটা পরিষ্কার
Get AI to write, explain and fix Excel formulas such as SUMIFS, XLOOKUP and IF, and to plan how to clean messy accounting exports. Test before you trust.
In this lesson
- How to describe a spreadsheet so AI gets the formula right
- Four Excel jobs AI does well
- How to test a formula before you rely on it
Describe the sheet, not just the goal
AI cannot see your file unless you upload it. Tell it the column letters, what each column contains, where the data starts and which Excel version you use. “Column A has invoice dates from row 2” beats “my dates”.
Four Excel jobs AI does well
- Write: “Write a formula that returns the customer’s credit limit from the Limits sheet using the customer code in A2.”
- Explain: paste a formula you inherited and ask what it does, step by step.
- Fix: paste the formula and the problem (#N/A, #VALUE!, a wrong total) and ask why.
- Plan: describe a messy export from your accounting software and ask for clean-up steps: Text to Columns, TRIM, Power Query.
Test before you trust
- Try the formula on a few rows where you already know the answer.
- Check edge cases: blanks, zero, the last row, dates at month-end.
- After any clean-up, make sure totals still agree with the ledger.
When the data is confidential
You rarely need real data to get a formula. Describe the layout, or paste five rows of made-up data with the same structure. Copilot in Excel works inside your file, but use it only where your organisation allows.
Formula request
Excel 365. Sheet "Sales": A = invoice date, B = customer code, C = region (Dhaka, Chattogram, Khulna), D = amount in Tk. Data is in rows 2 to 5000. Write one formula for cell G2 that totals sales for the region in F2 for March 2026 only. Explain each part in one line.
=SUMIFS(D2:D5000, C2:C5000, F2, A2:A5000, ">="&DATE(2026,3,1), A2:A5000, "<"&DATE(2026,4,1)) • D2:D5000: the amounts to add up • C2:C5000, F2: only rows where the region matches F2 • A2:A5000, ">="&DATE(2026,3,1): dates on or after 1 March 2026 • A2:A5000, "<"&DATE(2026,4,1): dates before 1 April 2026, so all of 31 March is included
A video version of this lesson is coming soon. Subscribe on YouTube to get notified.