AI for Spreadsheet Formulas: A Practical Work Guide

AI can suggest spreadsheet formulas, but a formula is useful only when it produces the correct result for your data. For Sri Lankan office work, begin with a small fictional sample, define the expected answer and test exceptions before using the formula in an important workbook.

Do not upload payroll, customer records or other sensitive data merely to explain a formula problem.

Describe the sheet precisely

Tell the tool which columns contain which values, where the headers are and what result you want. Include the spreadsheet application and version when compatibility matters.

An original prompt could be: “In Excel, column A contains category names and column B contains numeric sales amounts, with headers in row 1. Suggest a formula to total the rows labelled Tea. Explain each argument and give a small test case.”

A vague request such as “fix my sales formula” leaves too many assumptions.

Test a simple conditional total

Use this fictional sheet:

Row	A: Category	B: Amount
2	Tea	1200
3	Coffee	800
4	Tea	600

A formula such as =SUMIF(A2:A4,"Tea",B2:B4) should return 1800 for this example. Microsoft's SUMIF documentation explains the condition range, criterion and sum range.

Check that the ranges align. A formula can return a believable number while summing the wrong rows.

Ask for edge cases

Add a blank category, a misspelling and an amount stored as text. Decide what should happen before changing the formula.

If “Tea ” with a trailing space should count as Tea, clean or normalise the input deliberately. Do not hide data-quality problems inside a formula you cannot explain.

For multiple conditions, use the appropriate function and verify the argument order. Similar function names do not always use identical syntax.

Verify lookup behaviour

When asking for a lookup formula, define the key, the return column and what should happen when the key is missing.

Microsoft's XLOOKUP guide explains its matching behaviour and notes that XLOOKUP is unavailable in Excel 2016 and 2019. Check the version used by everyone who needs the workbook.

Test a known key, an unknown key and a duplicate key. If the source table contains duplicates, decide whether returning the first match is acceptable.

Protect the working workbook

Work on a copy while testing. Keep a small set of expected results and compare them after edits.

Check totals independently using a manual sample or a second method. Do not accept a formula simply because it produces no error message.

Document the purpose of important calculations so another person can maintain the workbook. If the sheet supports a financial or operational decision, follow the organisation's review process.

Can AI-generated formulas be wrong even when they calculate?

Yes. They may use the wrong range, matching rule or assumption. A valid formula is not necessarily the right formula.

Should you paste the full workbook into a chat?

Use a minimal fictional example whenever possible. Share real data only through an approved workflow.

Why does a formula work on one computer but not another?

Function availability, regional separators and application differences can matter. Record the environment when asking for help.

What should you do with a complicated suggested formula?

Ask for an explanation and simpler alternatives. If you cannot verify it, do not rely on it for an important decision.

What is the best habit to build?

Write the expected result before asking for the formula. That gives you a concrete test instead of a guess.

Related guides

• Excel Skills for Jobs in Sri Lanka: A Practice Project
• Python Office Automation: First Project for Sri Lanka
• How to Fact-Check AI Answers for Work in Sri Lanka

Browse the Sri Lanka work and technology guides for more practical application, AI and workplace projects.