Using AI in Excel: Practical Workflows for Business Data Analysis

Introduction

Excel is the most widely used data analysis tool in Australian SMEs. AI is the most hyped technology trend. The natural question is: how do you combine them in practice?

The honest answer is that AI isn't (yet) built into Excel in a way that fundamentally changes how you use it. But there are practical workflows where AI tools complement Excel beautifully-saving hours on tasks that previously required complex formulas, manual data cleaning, or expensive add-ins.

This article covers four workflows any SME can implement today, using tools that are either free or low-cost.

Workflow 1: Natural Language Formula Generation

The problem: You know what calculation you need ("show me the year-over-year growth percentage for each month") but can't remember the formula syntax. You end up Googling, copying from old spreadsheets, or asking a colleague.

The AI solution: Large language models like ChatGPT, Claude, and Gemini can write Excel formulas from plain English descriptions. Instead of searching for "how to calculate YoY growth in Excel," describe what you want:

"Write an Excel formula for column D that calculates the year-over-year percentage change. Column B has the current year monthly values and column C has the previous year values. Handle the case where the previous year value is zero."

The AI returns: =IF(C2=0,"N/A",(B2-C2)/C2)

When this saves time: Complex nested IFs, INDEX-MATCH combinations, array formulas, conditional formatting rules, and any formula involving dates or text manipulation.

The catch: AI-generated formulas can be subtly wrong. The AI doesn't know your specific spreadsheet structure or data quirks. Always test the formula on a small sample before applying it to your full dataset.

Workflow 2: Data Cleaning Assistance

The problem: Raw data exports are almost always messy. Inconsistent date formats, extra spaces, mixed case, numbers stored as text, duplicate entries with slight variations ("ACME Corp" vs "Acme Corporation").

The AI solution: Describe your cleaning problem to an AI and get a step-by-step process or a formula:

"I have a column of phone numbers in this format: '04XX XXX XXX', '61 4XX XXX XXX', and '(03) XXXX XXXX'. I need them all in a consistent format: '04XX XXX XXX' for mobile and '03 XXXX XXXX' for landline. What formulas should I use?"

The AI returns a strategy using TEXT, SUBSTITUTE, IF, and LEFT/RIGHT functions, tailored to your specific data.

When this saves time: One-off data cleaning tasks where you'd normally spend 20-30 minutes figuring out the formula approach. It's also excellent for generating Power Query M code if you use that for data transformation.

The catch: The AI doesn't see your actual data. It works from your description. If you describe the problem incorrectly or miss a data format variant, the formula won't handle it. Show the AI a sample of your data for better results.

Workflow 3: Pattern Recognition and Anomaly Detection

The problem: Manually scanning hundreds of rows to find outliers, duplicates, or unusual patterns is tedious and error-prone. Excel's built-in conditional formatting helps with obvious duplicates but struggles with subtle anomalies.

The AI solution: Export a sample of your data (anonymised if sensitive) and ask the AI to identify potential anomalies:

"Here's a sample of my transaction data. Each row has: Date, Amount, Vendor, Category. The amounts range from $10 to $15,000. Can you suggest conditional formatting rules or formulas to flag transactions that look unusual?"

The AI might suggest:

• Flag transactions more than 3 standard deviations from the mean
• Highlight any vendor that appears more than 5 times in a single day
• Check for weekend transactions in expense categories where they're unusual
• Compare category averages and flag individual transactions that are significantly above average

When this saves time: Monthly expense reviews, inventory audits, timesheet verification, and any regular data review where you're looking for "the thing that doesn't belong."

The catch: The AI defines "unusual" based on statistical thresholds you specify. It doesn't know your business context. A $50,000 transaction to a new supplier might be perfectly normal if you just landed a big contract. Always review AI-suggested anomalies with business knowledge.

Workflow 4: Report Narrative Generation

The problem: Your Excel dashboard is complete-charts, KPIs, trends. But you still need to write the commentary that goes with it: the monthly report narrative that explains what happened and why.

The AI solution: Summarise the key findings from your dashboard and ask the AI to draft the narrative:

"Our sales dashboard shows: Revenue up 8% MoM to $185,000. Gross margin down 2% to 41%. Customer count up 12% but average order value down 5%. Our top-selling product line grew 22% while two lines declined. Draft the executive summary section for our monthly report, explaining the key trends in plain language suitable for business owners."

The AI produces a draft that you can edit and tailor. It handles the structure and flow, leaving you to add the context and nuances only you know.

When this saves time: Monthly or quarterly reporting where you produce the same type of narrative each period. The AI handles the template; you handle the specifics.

The catch: The AI will invent details that sound plausible but aren't true ("the decline in Product B was driven by seasonal factors" - was it though?). Never trust the narrative section without fact-checking every claim against your actual data.

Choosing the Right Approach

For most SMEs, the free tiers of these AI tools are sufficient. The paid tiers ($20-30/month) offer longer context windows and faster responses but aren't necessary for the workflows above.

A Note on Data Privacy

Before copying business data into any AI tool, check:

• Are you including customer names, addresses, or financial details? If so, anonymise or aggregate.
• Does the AI tool's policy allow it to use your inputs for training? If yes, don't upload sensitive data.
• For sensitive financial data, consider using a self-hosted AI tool or a tool with a clear no-training policy.

When in doubt, describe the structure of your data rather than pasting the data itself. The AI can still generate accurate formulas and approaches from a clear description.

Conclusion

AI won't replace Excel, but it will change how you use it. The most practical benefit for SMEs right now is not automated analysis-it's faster access to expertise. Formula generation, data cleaning strategies, anomaly detection methods, and report drafting are all tasks where AI can take you from "I don't know how" to "here's a working solution" in minutes.

The skill to develop is not using AI-it's describing your problem clearly enough that the AI can help you solve it.

For more practical automation guides and Excel tools, visit ExcelWiz.com.au.