MMOOZZGG.com

AI solution map

AI for Excel: write formulas, clean data and build reports

7 min read3 sources

AI for Excel writes formulas from a plain description, cleans exports, builds pivot tables and finds what the eye misses. Since 17 September 2024, such an assistant has been generally available inside Excel with a Microsoft 365 Copilot license. Tables with customer data can be analyzed by a model on your own computer. Below are formula examples, five ready prompts and rules for personal data.

On this page · 6 sections

What AI can do in spreadsheets

AI does not replace Excel knowledge, but it removes the main difficulty: remembering the right function and the order of its arguments. The functions below use their English Excel names, and arguments are separated by commas. Some regional versions of Excel use semicolons instead.

1. A formula from a description. The request “look up the price from the Price sheet by item code” gives =XLOOKUP(A2,Price!A:A,Price!B:B,”not in price list”), the XLOOKUP function. In versions without it, AI offers =IFERROR(VLOOKUP(A2,Price!A:B,2,FALSE),”not in price list”), that is VLOOKUP inside IFERROR.

2. Fixing an error in a formula. If VLOOKUP returns #N/A although the item code is in the price list, AI names a common cause: extra spaces in the cell. The fixed formula is =VLOOKUP(TRIM(A2),Price!A:B,2,FALSE), where TRIM removes spaces at the edges.

3. Cleaning and splitting text. To take the city from the address “Kazan, Bauman street, 5” in cell B2, use =LEFT(B2,FIND(“,”,B2)-1): LEFT takes the characters on the left up to the first comma, and FIND finds the comma.

4. Pivots and totals. The request “sum of sales for Moscow” gives =SUMIF(B:B,”Moscow”,D:D): SUMIF adds the amounts from column D in the rows where column B says “Moscow”. For a report by manager and month, AI describes a pivot table and a chart for it.

5. Insights from data. The formula =MATCH(A2,A:A,0)<ROW() with the MATCH and ROW functions returns TRUE for each repeated order number: the same number already appears above. AI then writes the conclusion in words, but you must check it against the original rows.

Built-in AI in Excel

On 17 September 2024, Copilot in Excel became generally available. From a plain-language request, it writes formulas, sets conditional formatting, and builds charts and pivot tables.

Python analysis inside Excel was in preview at launch. Copilot needs a separate Microsoft 365 Copilot license.

Before you connect it, check two things: whether your company has this license and which tables staff will send to the assistant. The second point is covered below, in the section on personal data.

AI that keeps tables inside the company

If a table must not leave the company, run the model on your own computer or server. Ollama does this: a program under the MIT license that runs open models locally.

A local model writes the same formulas and explanations, but the file goes nowhere. For large tables, another method is handy: ask the model to write a Python script with the pandas library and run it yourself. The model sees only the column names, and your computer does the math.

Which model and hardware to choose is covered in the section on sovereign AI. A comparison of price and risk is on the page “local or cloud AI”.

Step by step: 5 tasks with ready AI prompts

1. A lookup formula. Prompt: “On the Orders sheet, column A has item codes. On the Price sheet, column A has item codes and column B has prices. Write a formula that shows the price in column C and writes ‘not in price list’ if the code is missing. Explain each argument.” Check: fill the formula down, pick three codes, find their prices in the Price sheet with a filter and compare.

2. Cleaning an export. Prompt: “Column B has phone numbers in different forms: with brackets, spaces, starting with 8 or +7. Write a formula that brings them to +7 and ten digits, and list the formats it will not handle.” Do not paste the numbers themselves into the prompt. Check: COUNTA before and after cleaning gives the same number of rows, and review the rows from the exception list by eye.

3. A pivot table. Prompt: “The table has the columns Date, Manager, Customer, Amount. Describe step by step how to build a pivot table with revenue by manager and month: which fields go to rows, columns and values, and how to group dates by month.” Check: the grand total of the pivot matches =SUM(D:D) on the original table, the SUM function.

4. Comparing two tables. Prompt: “The 1C sheet has invoices from the books, the Bank sheet has payments from the statement, and the invoice number is in column A on both sheets. Write a formula for the 1C sheet that marks invoices with no bank payment.” Expected answer: =IF(ISNUMBER(MATCH(A2,Bank!A:A,0)),”paid”,”not in bank”), with the ISNUMBER and MATCH functions. Check: look up three invoices marked “not in bank” in the statement; they should not be there.

5. Finding outliers. Prompt: “Column D has order amounts. Suggest a rule for when an amount counts as an outlier and a formula to mark such rows.” A common answer: =ABS(D2-AVERAGE(D:D))>3*STDEV.S(D:D), where AVERAGE gives the mean and STDEV.S the spread. Check: open the documents for the marked rows; an outlier can be an input error or a large customer.

What you must not upload to a public service

Tables with names, phone numbers, emails and passport data of customers and staff contain personal data. If you upload such a table to a foreign chat service, the data leaves the country.

Under 152-FZ, since 1 March 2023 an operator must notify Roskomnadzor before a cross-border transfer of personal data (Part 3, Article 12). Data of Russian citizens is collected and stored in databases in Russia (Part 5, Article 18).

You can remove personal details from a table before AI in four steps.

1. Replace names with the customer code from the CRM.

2. Delete the columns with phone numbers, emails, addresses and passports.

3. Keep the table that matches codes to names separately, inside the company.

4. If you need AI only for a formula, send not the data but the column names and a couple of made-up rows.

How data protection works in our projects is described on the page about data security.

When it is time to move beyond Excel

Excel is good for one-time calculations. It stops coping when a table turns into daily manual work. Three signs:

1. Every morning someone builds a summary from 1C, CRM and bank exports, and by lunch it is already out of date.

2. Payments from 1C are matched with the bank statement by hand, line by line.

3. Several versions of one file travel by email, and their numbers differ.

In these cases, give the work to an AI analyst agent: it takes data from the systems itself with read-only access and sends a report. The ready solution for the owner is called the manager dashboard, and daily accounting is described on the page about management accounting with AI.

How AI answers questions about company figures without manual tables is covered on the page about AI for data analytics. You can start with a pilot: one agent on a morning report from one source in 14 days.

Questions and answers

Which AI can work with Excel?

There are three options. Microsoft’s built-in assistant in Excel needs a separate Microsoft 365 Copilot license. Chat assistants take an uploaded file. Open models run on your own computer, for example through Ollama. For tables with customer data, only the option that sends no file outside fits.

Can I upload a customer table to an AI chatbot?

Not to a public service without consent and a check of its terms. This is processing of personal data, and under 152-FZ, Russia’s personal data law, an operator notifies Roskomnadzor, the Russian data regulator, before a cross-border transfer. Remove personal details from the table: replace names with codes and delete contacts. Or use a model on your own computer or company server.

Sources and fact-checking

Checked by the editors on 16 October 2026.

  1. SumProduct, 17 September 2024 (sumproduct.com)
  2. Alpine Linux packages, 2026 (pkgs.alpinelinux.org)
  3. Brace Law Firm, checked 16 October 2026 (brace-lf.com)

Found a mistake? Tell us

Publications

Contact us