Excel Formula Generation and Data Cleansing — Artificial Intelligence in Accounting, Finance and Operations

Gains:

Ability to produce complex Excel/Sheets formulas by accurately describing the version and table structure
Ability to define the steps of cleaning and normalizing messy financial data via prompt
Ability to verify the generated formulas by testing them on a small data set with known results

The financial professional's home is Excel (or Google Sheets). But writing a complex formula from scratch, setting up nested IFs, or cleaning up messy data can take hours. This is where artificial intelligence (AI) comes into play as a "formula assistant": you explain what you want in plain Turkish, and it writes the formula. But with a caveat: the AI-generated formula never makes it into the master file without being tested. In this unit, we will learn how to generate formulas and clean data with the discipline of verification.

Correctly Describing the Formula to AI

The model does not see your table. So you have to give it the following to print the formula: what is in which column, which cell the formula will go into, what you want to calculate, and which program you are using (some functions and brackets are different in Excel and Google Sheets).

Tip: When asking for a formula, explain the column content with an example: Saying "Column A is the date, B is the customer name, C is the amount" is much more accurate than "write this formula". The model establishes references accordingly.

Step by Step: Safe Formula Production

Describe the structure. Columns, data types, cell where the formula will come.
State the purpose clearly. Like "Add the amount of the rows that meet the following condition".
Specify program and region. Excel or Sheets? Is the decimal separator a comma?
Ask for the formula and its explanation. Let him explain what he did, piece by piece.
Test on small data. Try it on 3-5 lines where you know the result, then expand it.

Weak Prompt / Strong Prompt

Weak prompt:Write conditional addition formula in Excel.

This gives a general and often inaccurate formula because it is not clear which column is which condition.

Powerful prompt: Write formula for Excel (Turkish version, decimal separator comma).Table: A=date, B=department, C=category, D=amount. The data is in 2..500 rows. I want: In cell F2, the sum of the amounts in column D for the rows with department "Marketing" AND category "Advertising". Output:1) The formula itself2) A short explanation of what each part of it does3) An alternative formula (if available) that does the same job

This prompt gives an accurate SUMIFS based formula, part description and alternative. Since the Turkish version and the comma separator are specified, the formula works as is.

When requesting a formula, also plan how you will verify the result. The safest method is to set up a test table small enough that you can calculate it by hand: 3-5 rows for which you already know the result. You run the formula in this table and see if it gives the number you expect. If you give it, you spread it; If it doesn't, you can fix the prompt and have it reproduced. This small investment prevents a silent error spread over 500 lines.

Common Formula Families

need

Turkish Excel

English/Sheets

note

Conditional total

SUMIZE

SUMIFS

Multiple conditions

conditional counting

COUNTIFS-TOO

COUNTIFS

How many lines fit

VLOOKUP / INDEX+MATCH

INDEX+MATCH is more flexible

conditional value

IF / IFERROR

For error management

Extract text

FROM LEFT, PIECE, FIND

LEFT, MID, FIND

In data cleaning

Attention: AI may respond with English function names (SUMIFS, VLOOKUP). Turkish Excel does not recognize these; Equivalents such as SUMIFS, VLOOKUP etc. are required. Be sure to specify which version you are using in the prompt, otherwise the formula will give an error.

Data Cleansing: From Messy to Organized

Financial data is often dirty: date formats are mixed up, amounts have thousands separators, the same customer is written differently ("ABC Ltd", "ABC Limited", "abc ltd."). AI can solve these cleaning steps with both formula and step list.

Give the following step-by-step plan and necessary Excel formulas to clean up the messy data: Problems: dates are in both 12.03.2025 and 2025-03-12 format; amounts include text such as "1.234.50 TL"; Customer names have inconsistent uppercase/lowercase letters. Target: date in single format, amount in number, customer name in proper capital letters. Show each step with a separate formula, work in new columns without disturbing the original data.

Explaining Pivot and Summary Logic

AI cannot click the pivot table for you, but it tells you step by step how to set it up and gives the same summary with the formula.

I would like to summarize this table into total expenses per month and department. Give me two ways:1) Pivot table steps (which field to row, column, value)2) Formula set that builds the same summary as SUMIF without using pivot

Understanding the Formula: Opening the Black Box

Using a formula given by AI without understanding it is dangerous in the long run; because one day the input changes, the formula breaks and you can't fix it because you don't know what it does. So don't just take the formula, learn it. AI is a great teacher: you can have a complex formula explained to you step by step.

Explain this formula to me line by line, as if I were just learning Excel: write down what each function does, the order of the arguments, and under what circumstances this formula will fail. Finally "how do I test this formula?" Suggest 3 sample inputs and expected outputs for.=IFERROR(VLOOKUP(A2,List!A:C,3,FALSE);"not found")

Also get into the habit of documentation: write down what the formula does in one sentence next to the cell in which you used the complex formula or in a "notes" tab. You'll thank yourself when you open the file six months later. AI can also generate this description sentence for you.

Tip: When a formula gives unexpected results, you give the entire formula to the AI and ask “why could this be wrong?” ask. Give the model cell samples and the result you expect, too; Most of the time it finds the reference error or type (text/number) mismatch instantly.

Mini Cases

Case 1 — Wrong version trap. An analyst pasted the SUMIFS formula given by AI into Turkish Excel and wrote #AD? got the error. When I updated the prompt to "Turkish version", the model gave SUMIF and the formula worked. Lesson: specifying the version is five seconds' work, skipping is half an hour's trouble.

Case 2 — The test caught an error. AI had referenced the wrong cell for division in the "change over last month" formula. The analyst tested the formula on 4 lines where he knew the result; An absurd result of 900% was obtained in one line. Reference corrected. Without testing, the error would have spread over 500 lines.

Case 3 — Two hours of cleaning into ten minutes. An accountant noticed that the amounts in the 1,200-line bank statement were in text format ("TL 1,234.50") and could not be added together. With the steps SUBSTITUTE + CONVERT given by AI, he converted the column into a number in ten minutes; verified the result on three lines and confirmed with a total check that all 1,200 lines were translated.

Case 4 — The price of using without understanding. An analyst used the nested formula he got from AI without understanding it. Months later, when the column order of the source table changed, the formula silently started pulling the wrong column, but no one noticed; The report went wrong for two months. If he had the AI explain the formula from the beginning and write down what it did in a "notes" cell, the change would be caught immediately. Lesson: understanding every formula you use is to prevent a future mistake.

Common mistakes

Not specifying Excel/Sheets version. The English function gives an error in the Turkish version.
Asking for a formula without describing the table. The model cannot fit references; Give the column content.
Spreading the formula without testing it. Do not enter the main file without trying small data with known results.
Cleaning by corrupting the original data. Perform the cleanup on new columns; Protect raw data.
Not specifying the decimal/thousand separator. Comma-dot confusion leads to silent calculation errors.

In summary

AI is a powerful assistant that translates the logic you describe in plain Turkish into Excel/Sheets formula; but you don't see your table, you describe the structure.
Specifying the version (Turkish/English, Excel/Sheets) and the decimal separator is essential for the formula to work as is.
Asking for a part explanation next to the formula both teaches and makes it easier to catch the error.
Test each formula produced on a small amount of data with known results, then disseminate it.
Perform data cleaning on new columns; Never corrupt raw data directly.

Application task

Select a complex accounting need from your own work (e.g. multi-condition sum or lookup between two tables). Generate the formula, its explanation and an alternative with the powerful prompt template. Try it on a test table of 3-5 rows where you know the result; If there is an error, correct the prompt and have it reproduced. Also take a dirty column (mixed date or text-amount) and fix it with AI's cleaning steps and verify the result.

checklist

[ ] I specified the Excel/Sheets version and the decimal separator.
[ ] I described the column content and the cell where the formula will come.
[ ] I wanted a part description next to the formula.
[ ] I tested the formula on small data with known results.
[ ] I cleaned the data in new columns without damaging the raw data.
[ ] I did at least one absurd consequence check before disseminating.