AI for Data Analysis and Spreadsheets

Spreadsheets are one of the most widely used tools in professional life. But writing complex formulas, cleaning messy data, building charts, and interpreting what the numbers actually mean are tasks many people find difficult or time-consuming. AI tools dramatically lower the barrier to working with data — making it possible to analyse a spreadsheet, write a formula, or generate a chart through a simple text instruction.

What AI Can Do With Spreadsheet Data

• Write and explain formulas: Generate Excel or Google Sheets formulas from a plain-language description
• Clean and format data: Identify inconsistencies, duplicates, and formatting issues
• Analyse data: Identify trends, outliers, patterns, and key statistics
• Create charts: Generate visualisations based on selected data
• Interpret results: Explain what the data means in plain language
• Answer questions about data: Ask "which product had the highest growth?" and get an answer instantly

Tool 1 — Microsoft Copilot in Excel

Copilot inside Microsoft Excel is one of the most powerful data analysis tools available for non-technical users. It works directly inside the Excel interface.

What It Can Do

• Write formulas from a natural language description
• Highlight rows that meet specific criteria
• Generate charts from selected data
• Add a new column based on a calculated rule described in plain language
• Summarise what a dataset shows in a text paragraph

How to Use Copilot in Excel

1. Open a spreadsheet in Microsoft Excel (desktop or web)
2. Make sure the data is formatted as a Table (select data → Insert → Table)
3. Click the Copilot icon in the Home tab toolbar
4. The Copilot panel opens on the right
5. Type the request in plain language

Example Requests in Excel Copilot

• "Which sales region had the highest total revenue last quarter?"
• "Add a new column that calculates the profit margin for each row (Revenue - Cost divided by Revenue)."
• "Highlight all rows where the order status is 'Pending' in yellow."
• "Create a bar chart showing monthly sales totals."
• "Show me any rows where the quantity ordered is greater than 500."

Tool 2 — Google Gemini in Google Sheets

Gemini is integrated into Google Sheets for Google Workspace users. It functions similarly to Copilot in Excel.

How to Access

1. Open Google Sheets
2. Click Extensions → Gemini for Sheets or look for the Gemini sparkle icon in the toolbar
3. Type a question or instruction about the spreadsheet data

Example Requests in Google Sheets Gemini

• "Summarise the key trends in this data."
• "Create a pie chart showing the breakdown of sales by product category."
• "Which months show a decline compared to the previous month?"

Tool 3 — ChatGPT for Formula Writing and Explanation

Even without Copilot or Gemini, ChatGPT is an excellent tool for generating and explaining spreadsheet formulas. Describe what is needed and ChatGPT provides the formula ready to paste.

Formula Writing Examples

Example 1 — Conditional Sum
Prompt: "Write an Excel formula to sum all values in column C where the corresponding value in column A is 'North'."
Result: =SUMIF(A:A, "North", C:C)

Example 2 — Finding Duplicates
Prompt: "Write a Google Sheets formula to highlight duplicate values in column B."
Result: COUNTIF($B:$B, B2) > 1 (as a conditional formatting rule)

Example 3 — Date Calculation
Prompt: "Write an Excel formula to calculate how many days are between the date in cell A2 and today."
Result: =TODAY()-A2

Example 4 — Looking Up Data
Prompt: "Write a formula to look up the price of a product in column A and return the price from column C, using XLOOKUP."
Result: =XLOOKUP(A2, A:A, C:C)

Formula Explanation Examples

Prompt: "Explain this Excel formula in plain English: =IFERROR(VLOOKUP(A2, Sheet2!A:C, 3, FALSE), 'Not Found')"

Result: A plain-language explanation of what the formula does at each step.

Analysing Data by Pasting Into ChatGPT or Claude

For smaller datasets, the data can be pasted directly into ChatGPT or Claude (as a table or comma-separated values) and questions can be asked about it.

Steps

1. Select the data in the spreadsheet
2. Copy it (Ctrl+C)
3. Open ChatGPT or Claude and paste it with an analytical question

Example Prompt:
"Here is a table of monthly sales data for five products over six months. Identify the top-performing product, the month with the highest total sales, and any product showing a consistent decline. Present findings as a brief paragraph. [Paste data here]"

Cleaning Messy Data With AI

Data cleaning — standardising formats, removing blanks, fixing inconsistent naming — is tedious but important. AI can either write formulas to automate it or describe the steps needed.

Example Prompt:
"I have a column of phone numbers in mixed formats — some have country codes, some are formatted with spaces, some with dashes. Write an Excel formula to standardise them all to the format +44XXXXXXXXXX."

Example Prompt:
"I have a column of names in all caps (JOHN SMITH). Write a Google Sheets formula to convert them to proper case (John Smith)."
Result: =PROPER(A2)

Key Takeaway

AI makes spreadsheet work accessible to everyone — not just those with advanced formula knowledge. Copilot in Excel and Gemini in Sheets provide direct, conversational access to data analysis inside the spreadsheet. ChatGPT and Claude handle formula writing, data interpretation, and cleaning tasks from a simple text description. The ability to ask plain-language questions about data and receive formula-level answers removes one of the biggest barriers to working effectively with numbers.

The next topic covers AI for Presentations and Slide Decks — how to create, structure, and design professional presentations faster using AI.