AI in Excel and Google Sheets: Prompts for Cleanup, Formulas and Analysis

Spreadsheets run a huge amount of Malaysian business: stock lists, sales records, staff rosters, payment trackers. Most people using them learned just enough formulas to get by. AI changes that, because you can now describe what you want in plain language and get a working formula with an explanation.

Whether you use a built-in assistant such as Copilot in Excel or Gemini in Google Sheets, or simply ask a chatbot in another tab, these prompts will save time. Always test on a copy of your file first.

Cleaning Messy Data

Data exported from marketplaces, POS systems and banks is often inconsistent. Useful prompts include:

• "Column A has names in mixed case with extra spaces. Give me a formula to clean them."
• "Column B has dates in formats like 3/10/26 and 2026-10-03. How do I convert them all to DD/MM/YYYY in Google Sheets?"
• "Column C has phone numbers with and without +60. Standardise them to start with 60."
• "How do I find and remove duplicate orders based on order ID?"

Describe your columns clearly and give two or three example values. The more concrete you are, the more likely the first formula works.

Writing Formulas You Understand

Ask the AI not only for the formula but also for a plain explanation of each part. For example: "Write a formula that totals sales in column D where column B is Shopee and column C is October. Explain each part." This helps you fix it later without asking again.

Common requests that AI handles well include SUMIFS and COUNTIFS for conditional totals, XLOOKUP or VLOOKUP for matching data between sheets, IF statements for status columns, and simple date calculations such as days overdue.

Tip: tell the AI which tool and version you use. Some functions exist in newer Excel or in Google Sheets but not in older Excel versions.

If a formula becomes long and hard to read, ask the AI whether it can be split into helper columns. Several simple columns are easier to check and maintain than one giant formula that only the original author understands.

Quick Analysis and Summaries

For analysis, describe the question you want answered rather than the method. "Which five products had the biggest drop in sales from September to October?" or "Which staff member has the most overtime this month?" The AI can then suggest a pivot table, a formula or a chart.

If you paste data into a chat assistant, remove customer names and personal details first. Often you only need product codes, dates and amounts. For guidance on what is safe to share, see our PDPA-minded checklist for AI at work.

Treat AI-generated insights as hypotheses. If it says sales dropped because of a public holiday, check whether that is actually true.

Automating Repetitive Work

Once you have reliable formulas, AI can help with the next level: simple automation. Ask for an Apps Script in Google Sheets to email a daily low-stock list, or for steps to record a macro in Excel that formats a weekly report. Even if you have never written code, the AI can walk you through it step by step.

Keep automation simple and documented. Add a note in the sheet explaining what each script does and who set it up. When something breaks months later, that note saves a lot of confusion.

If the spreadsheet is becoming a full business system with many users, it may be time to consider a proper app. Our post on moving from spreadsheet to lightweight app explains when.

A Short Checklist Before You Trust a Formula

1. Test on a copy of the file.
2. Check five rows manually against the formula result.
3. Look for blank cells and text in number columns.
4. Check date formats, especially day and month order.
5. Lock the formula cells if other people edit the sheet.

A formula that is wrong for a small number of rows can quietly distort monthly totals. These five checks take a few minutes and prevent the most common mistakes.

Keep a "notes" tab in important workbooks listing the main formulas, what they do and the date they were last checked.

FAQ

Do I need Copilot or Gemini inside my spreadsheet?

No. Any AI chat assistant can write formulas. Built-in assistants are convenient because they can see your sheet directly.

Can AI read my whole spreadsheet?

Built-in assistants can work with the open file. For chat tools, paste only the columns and rows needed, with personal data removed.

What if the formula returns an error?

Paste the exact error message and the formula back to the AI. It can usually find the problem quickly.

AI makes spreadsheet skills that used to take years available in minutes. Describe your data clearly, ask for explanations, test carefully and protect personal data. Your spreadsheets will become cleaner, faster and far less frustrating.