20 AI Excel Tips for 2026: Formulas, Clean Data and Charts in Minutes (Copilot, ChatGPT, Claude, Google Sheets, Jobbit)

Somewhere in every business there is a spreadsheet nobody fully understands: a quote calculator built by someone who left, a sales sheet with dates in three formats, a VLOOKUP that returns errors on Tuesdays. The most searched Excel questions in 2026 are still "how to do a VLOOKUP", "how to remove duplicates" and "how to make a pivot table", and the new answer to all three is the same: describe what you want and let the AI write it.

This is a practical list of 20 AI Excel tips for people who use spreadsheets for work, not for sport. It covers the tools that now live inside the spreadsheet (Copilot in Excel, the Claude for Excel add-in, Gemini in Google Sheets), the chat assistants you can hand a file to (ChatGPT, Claude, Gemini), the prompts that get correct formulas first time, the checks that stop AI arithmetic going wrong, and the point where a spreadsheet stops being the right tool and an AI agent builds you a small app instead.

How we chose these tips

1. Tested on real files. Each tip was tried on a sales export, a budget and an expense sheet with the usual mess: merged cells, text numbers, blank rows.

2. Tool-agnostic where possible. Most tips work in Excel and Google Sheets, with Copilot, ChatGPT, Claude or Gemini.

3. Priced honestly. Copilot in Excel needs a Microsoft 365 Copilot licence, Claude for Excel comes with Claude Pro at $20 a month, Gemini in Sheets comes with Google Workspace or a Google AI plan, ChatGPT and Claude free plans handle small files.

4. Checked for arithmetic. AI tools are excellent at writing formulas and poor at doing sums in their heads. The tips build in verification.

The 20 tips at a glance

Formulas (tips 1 to 4)

1. Describe it, paste it. "A formula for column F that returns 'Late' if the date in D is more than 30 days before today and the status in E is not 'Paid', otherwise blank" gets you a correct IF/AND/TODAY formula in five seconds from ChatGPT, Claude, Gemini or the Copilot pane. Say which cell it goes in.

2. Ask for XLOOKUP. VLOOKUP breaks when columns move and cannot look left. Ask for XLOOKUP in Excel or Sheets, or INDEX and MATCH if you must support very old Excel. Ask for "if not found, show blank" while you are at it.

3. Paste the headers. Copy row 1 and paste it with the request: "columns are A Date, B Customer, C Product, D Qty, E Unit price". The AI then references the right cells instead of guessing.

4. Formula plus explanation. "Give me the formula and one sentence on what each part does." Six months later you will be glad, and so will whoever inherits the file.

Cleaning data (tips 5 to 7)

5. One prompt for dates and numbers. Upload the file (or paste the column) and ask: "convert every date in column A to real dates in DD/MM/YYYY, turn the text numbers in column E into numbers, trim spaces in B". ChatGPT and Claude return the cleaned file; Copilot does it in place.

6. Duplicates and near-duplicates. "Find rows where the email matches ignoring case and spaces" catches the duplicates Excel's built-in Remove Duplicates misses. Ask for them flagged in a new column first, so you can review before deleting.

7. Split and merge. "Split column B into first name and surname" or "combine columns A to C into a full address with commas" is a single request instead of Text to Columns and a CONCAT formula.

Pivots and charts (tips 8 to 10)

8. Pivot from a sentence. In Copilot's pane: "sales by product by month, with a total row". It builds the pivot on a new sheet. In Google Sheets, Gemini does the same from the side panel. In ChatGPT, upload the file and ask for the summary table.

9. Ask which chart. "What chart best shows whether sales are seasonal?" gets a line chart by month; "which products make up revenue?" gets a bar, not a pie. Ask for the chart and the reason.

10. =AI() in Sheets. Google Sheets has an AI function (also written =GEMINI()) that runs a prompt on each row: "=AI(\"Categorise this expense as travel, software, meals or other\", B2)" tags a thousand rows in a minute. Check a sample; it is very good, not perfect.

Understanding other people's spreadsheets (tips 11 to 12)

11. Explain this workbook. Claude for Excel (included with Claude Pro) reads the live workbook and can walk through the logic sheet by sheet: which cells drive which, where the assumptions are, why a total does not match. Copilot in Excel does a lighter version; both beat clicking through formula precedents.

12. Conditional formatting in words. "Highlight any cell in G that is more than 10% below the target in H, in light red" writes the rule for you. Most people never learn the dialog; they no longer need to.

Building things (tips 13 to 16)

13. Test data. "Generate 200 rows of realistic UK customer orders with dates in 2026, product names and prices between £5 and £120" gives you a safe dataset to test a model without touching real customer data.

14. Scripts without learning VBA. "Write an Office Script that copies this month's rows to a new sheet named with the month" or the Apps Script equivalent for Google Sheets. Paste it, run it, ask for fixes. It is how non-programmers automate the Friday report.

15. Drop-down lists. "Add a data validation drop-down in column C with the values Draft, Sent, Paid, Overdue" replaces a menu most people cannot find.

16. Templates from a description. "A 12-month cash-flow forecast for a café with three revenue lines, fixed and variable costs, VAT at 20% and a closing balance row" gets you a working template from ChatGPT or Claude. Jobbit's agent produces the same as a downloadable Excel file with the formulas live, which is the difference between a plan and a screenshot.

Checking the work (tips 17 to 19)

17. Second formula. Ask for an independent check: if the total is SUM, verify with SUMPRODUCT or a pivot. If the two disagree, something is wrong with the data, not the AI.

18. Formulas, not mental arithmetic. When you ask a chatbot "what is the total of these 40 numbers", it may be wrong. When you ask it "write me the formula", it is right. Keep the calculation in the sheet.

19. Anonymise first. Replace names, emails and account numbers with IDs before uploading to any consumer AI plan. Copilot inside a Microsoft 365 tenant and Gemini inside Workspace keep data in your organisation's boundary; the free consumer chatbots do not promise that.

The tip that replaces the spreadsheet (tip 20)

20. Five users means it is an app. The moment a spreadsheet is shared with the team, has a "do not edit" tab and gets emailed around as v7_final, it has become software. ChatGPT, Claude, Gemini, Microsoft Copilot, Manus and Meta's Muse can all write the formulas and the scripts; none of them turns the sheet into a hosted tool with logins, a form for the team and a report for you. Jobbit's agent does exactly that: describe the process the spreadsheet runs, and it builds and hosts a small app around it, keeps the spreadsheet export, and adds the automation (weekly summary, overdue reminders) that the sheet never had. Our guide to replacing a SaaS stack with one agent shows a real example, and best invoice and business tools covers the quoting and invoicing case.

Mistakes to avoid

• Asking the chatbot for the answer instead of the formula. Arithmetic in the chat is unreliable; formulas in the sheet are not.

• Not pasting your headers. Half of "wrong" formulas are right formulas pointing at the wrong columns.

• Deleting duplicates without a review column. Flag first, delete second.

• Uploading client data to a consumer plan. Anonymise or use a tenant-bound tool.

• Rebuilding the same report every month by hand. One script or one automation ends that.

• Keeping a shared process in a spreadsheet forever. When five people depend on it, turn it into a small app.

Frequently asked questions

Can AI write Excel formulas for me?

Yes. Describe the result you want, name the columns and the target cell, and ChatGPT, Claude, Gemini or Copilot in Excel will write the formula and explain it. Ask for XLOOKUP rather than VLOOKUP, and ask for a second formula to verify important totals.

What is the best AI for Excel in 2026?

Copilot in Excel is the only assistant built into Excel itself and is the natural choice inside Microsoft 365. Claude for Excel is the strongest at explaining and restructuring complex workbooks. Gemini in Google Sheets is best for Workspace teams and adds the =AI() function. ChatGPT is the most flexible for one-off cleaning and scripting when you can upload the file.

How do I clean messy data in Excel with AI?

Upload the file or paste the column and describe the fixes: convert text dates to real dates, turn text numbers into numbers, trim spaces, split names, flag duplicates. ChatGPT and Claude return a cleaned file; Copilot applies the changes in the workbook. Always ask for duplicates to be flagged before they are removed.

Is it safe to upload my spreadsheet to ChatGPT?

For non-sensitive data, yes. For customer, employee or financial records, anonymise the file first or use a tool that stays inside your organisation, such as Copilot in a Microsoft 365 tenant or Gemini in Google Workspace. Never upload passwords, card numbers or anything under an NDA to a consumer chatbot.

Can AI build a whole spreadsheet template?

Yes. Describe the purpose, the inputs and the outputs, and ChatGPT or Claude will lay out a budget, forecast, tracker or quote calculator with working formulas. An agent such as Jobbit returns it as a finished Excel file, or, if several people need to use it, builds it as a small hosted app instead. Start free at jobbit.uk.