Automate Excel with AI: Get Formulas and Cleanup Done for You

Describe the Result You Want and a Formula Comes Out

The most important thing when asking AI for a formula is telling it concretely what your data looks like and what you want to get. The more you specify column positions and data forms—like “dates in column A, amounts in column B”—the more accurate the formula that comes back.

Here are prompt examples you can copy and use right away.

• Conditional sum: “Column A has department names and column B has expenses. Give me an Excel formula that sums the expenses for the 'Sales Team'.”
• Count after removing duplicates: “Column C contains customer names with duplicates. I need a formula that counts how many distinct customers there are.”
• Merging two tables: “I want to pull names from Sheet2 using employee IDs in Sheet1. Show me two versions, one with VLOOKUP and one with XLOOKUP.”

When you get a formula, ask again about any part you don't understand. Asking back “why did you put a 0 at the end in this formula?” gets you an explanation of what each argument does. Once you know the principle, you can apply it yourself next time. It's also useful when you want to improve an existing formula. Asking “this formula I'm using is too long. Is there a simpler or more up-to-date function to replace it?” can get you better alternatives like SUMIFS or XLOOKUP.

Have It Clean and Classify Messy Data

Real-world data is mostly untidy. In these cases, asking AI how to clean it gets you the functions, features, and steps all at once.

• Splitting text: “I want to split one cell joined by slashes, like 'Hong Gil-dong/010-0000-0000/Seoul', into three columns for name, phone, and region. Tell me how.”
• Unifying formats: “My dates are all over the place, like 2026.7.1, 2026-07-01, 7/1. How do I unify them into one date format?”
• Classifying into bands: “I need a formula that grades a score column as A for 90 and up, B for 80 and up, and C for the rest.”
• Filtering out unwanted values: “The name column has stray spaces and special characters around the entries. Tell me how to clean it up.”

If you paste in table-shaped data whole and request “rebuild this table into a monthly sales-total table,” it may handle the pivot concept for you and return an organized result. That said, if the volume of data is large, it's safer to be guided toward using Excel's built-in feature (the pivot table).

Ask the Cause of an Error, and Have Macros Interpreted Too

When errors like #N/A, #REF!, or #VALUE! appear, it's hard to guess the cause. In that case, paste in the error message and the formula you used exactly as they are and ask.

• Diagnosing an error: “This formula throws a #N/A. Tell me in what cases this error occurs and how to fix it: =VLOOKUP(A2,D:E,2,0)”
• Interpreting a macro: “Explain, line by line, what this VBA code left by my predecessor does. And let me know if there's any dangerous part.”
• Suggesting automation: “I make the same report every week and there's a lot of repetitive work. Give me ideas for what feature or macro could reduce it.”

Having AI interpret the code when you inherit an unfamiliar macro cuts your learning time dramatically. Just be sure to always test a macro AI suggests in a copy first before running it.

You Can Have Google Sheets Do the Same

Everything so far works the same in Google Sheets. The function names or argument separators can differ slightly, so when you make a request, just specify “tell me for Google Sheets”. For instance, asking with the tool name in front, like “in Google Sheets, I want the sum of column B for rows where column A is 'Done',” gets you an answer matched even to the slightly different syntax from Excel. Asking about powerful features unique to Google Sheets, like the QUERY function, often solves things more simply than in Excel. Conversely, an Excel macro (VBA) has to be replaced with Apps Script in Google Sheets, and you can leave that conversion to AI too.

Common Mistakes

The most common and dangerous mistake is applying an AI-given formula to all your data with no verification. AI sometimes confidently puts out formulas that are plausible but wrong.

• Trusting without verifying: Always apply it to a few rows whose answers you know and compare against a hand calculation before scaling to the whole set.
• Not stating the data's form: Leave out column positions and data examples and you get the wrong function back.
• Entering sensitive information as-is: Ask by swapping in fictional examples for real names, national ID numbers, and client information.
• Ignoring version differences: The latest functions like XLOOKUP may not work in older Excel, so state the version you're using.

Frequently Asked Questions

Can I trust an AI-given Excel formula as-is?

No. Because AI can make mistakes, verifying the result is the user's job. You need the habit of first plugging it into a few samples whose results you already know, checking it matches your hand calculation, and only then applying it to the whole.

Can I automate with AI even if I don't know Excel well?

You can start without the basics. However, you do need the ability to clearly explain “what you want to get.” If you can put into words which columns your data is in and in what form, you're halfway there. Ask back about the principle of the formulas AI gives you and pick them up little by little, and next time you'll be able to request more accurately and judge the results yourself.

Is it okay to paste company data into AI?

The principle is not to put sensitive information in as-is. Swap parts like names, contacts, and amounts for fictional examples and pass along only the structure and format you need. If your company has data-security rules, following those guidelines first is the safe course. Once you grasp just the formula's structure, you only have to reapply that framework to your real data, so there's rarely a reason to hand over the original wholesale.