Listen to this lesson
Estimated reading time: 8 minutes
If you work with spreadsheets — and most people do, whether they enjoy it or not — AI can save you real time. From writing formulas to cleaning messy data to spotting patterns, AI tools are surprisingly capable with tabular data.
You don't need to be a data analyst to benefit. This lesson covers practical techniques anyone can use.
This is the quick win that gets people hooked. Instead of Googling "Excel VLOOKUP syntax" for the hundredth time, you describe what you want in plain English and the AI writes the formula.
Example prompt: "Write an Excel formula that looks up a product name in column A of Sheet2 and returns the price from column C. If the product isn't found, show 'Not listed'."
The AI will give you something like:
=IFERROR(VLOOKUP(A1,Sheet2!A:C,3,FALSE),"Not listed")
Along with an explanation of what each part does.
Real-world data is messy. Names are inconsistent, dates are in different formats, there are duplicates and blanks everywhere. AI can help you sort this out.
Example prompt: "I have a column of phone numbers in mixed formats — some have spaces, some have dashes, some start with +64, some start with 0. Write an Excel formula to standardise them all to the format 02X XXX XXXX."
Both ChatGPT and Claude allow you to upload spreadsheet files. You can upload a CSV or Excel file and ask:
ChatGPT is particularly good at this because it can actually run code on your data — it processes the file using Python and gives you a cleaned version to download.
If you can't upload the file (maybe it contains sensitive data), describe the issue:
Example prompt: "I have a spreadsheet with 500 rows of customer addresses. The city names are inconsistent — sometimes 'Auckland', sometimes 'AUCKLAND', sometimes 'Akld'. What's the best approach to standardise these in Excel?"
The AI will suggest approaches using formulas, find-and-replace, or Power Query — whatever fits your situation.
AI can help you understand what your data is telling you, even if you're not comfortable with statistics.
Upload a spreadsheet and ask questions in plain English:
ChatGPT can generate charts directly from your data:
Example prompt: "Create a bar chart showing monthly revenue for the past 12 months from this spreadsheet. Use a clean, professional style."
It will generate the chart in the conversation, which you can then download and use in presentations or reports.
If you're not sure where to start, ask:
Example prompt: "I have sales data for the past 2 years, broken down by product, region, and month. What are the most useful analyses I could run on this data? I'm trying to understand which products and regions are growing."
The AI will suggest relevant analyses and can then walk you through each one.
Beyond uploading files to a chat-based AI, you can use AI directly within your spreadsheet application:
If your organisation has Microsoft 365 Copilot, you can ask questions about your data directly within Excel. Type natural-language questions and Copilot will create formulas, generate charts, and highlight patterns. It works on your data without it leaving your organisation's environment.
Gemini can help with formulas and analysis within Google Sheets. It's available through the Gemini side panel in Sheets on supported Google Workspace plans.
Upload your spreadsheet to ChatGPT and it will use Python (Code Interpreter) to analyse it. This is the most flexible approach — it can handle complex data manipulation, create visualisations, and even build simple dashboards.
A word of caution: spreadsheets often contain sensitive data — customer information, financial figures, employee details. Before uploading data to an AI tool:
When in doubt, describe the data structure and ask for formulas or approaches — you can apply them to your actual data locally without the data ever leaving your computer.
Get AI to help with your own spreadsheet.
If your data is sensitive, use fictional data or describe the structure without sharing actual values. The learning is in the process, not the specific data.
1. What should you always do after an AI tool writes a spreadsheet formula for you?
Answer: b) Always test AI-generated formulas — they're usually correct but can have edge-case errors.
2. Before uploading a spreadsheet containing company data to an AI tool, what should you do first?
Answer: b) Check your organisation's policy and consider anonymising data — spreadsheets often contain sensitive information that shouldn't be uploaded to consumer AI tools.
3. Which AI tool is most capable at running actual code on your uploaded spreadsheet data?
Answer: c) ChatGPT's Code Interpreter can execute Python code on your uploaded data, enabling complex analysis, cleaning, and chart generation.