Excel Analysis Prompts for Cleaner, More Reliable Client Data

Client spreadsheets often look tidy until you filter, sort, or reconcile them. A date column contains text, customer names carry hidden spaces, invoice IDs repeat, and totals no longer match the source system.

Well-written Excel analysis prompts help you spot those issues faster. However, an AI assistant can suggest patterns, formulas, and tests. It can’t certify that a client record is correct or safely rewrite source data without review.

Use the prompts below with a sanitized data sample, then validate every finding in Excel before you report, correct, or remove anything.

Start with a protected working copy

Never investigate directly inside the only version of a client file. Save an untouched original, then create a dated working copy with a clear status in the filename, such as ClientName_Orders_Working_2026-09-15.xlsx.

Keep raw data, cleaned fields, and review notes separate. An Excel Table is usually the simplest structure because formulas, filters, and references expand with new rows.

A practical workbook often has four sheets:

• Raw_Data holds the imported client file without manual edits.
• Working_Data contains helper columns, formulas, and flags.
• Exceptions holds rows that need a decision.
• Change_Log records approved corrections and their reasons.

This setup prevents a common consulting mistake: making an irreversible change, then struggling to explain where a figure came from.

A flagged row is an item for review, not proof that the source record is wrong.

Remove identifying details before using AI

Do not paste a live client export into a public AI chat. Names, email addresses, account numbers, addresses, payroll data, medical details, and internal contract terms can expose people or commercial information.

NIST defines personally identifiable information as information that can distinguish or trace an individual’s identity, alone or combined with other data. Replacing only a name may not be enough when other fields can still identify a person.

Create a safe sample instead of sharing the workbook

Use 20 to 100 representative rows when possible. Keep data shapes and error patterns, but replace sensitive values with realistic placeholders.

For example, convert:

Original field	Safer AI sample
Customer name	Customer_001
Email address	contact001@example.test
Account number	ACCT-0001
Street address	Region_A
Actual revenue	Scaled or rounded amounts
Project code	Project_X

Preserve the columns that matter to validation. If you need to detect duplicate invoices, retain the invoice pattern and the relationship between invoice ID, date, amount, and status.

State what AI must not infer

Tell the assistant that values are masked and that it must not reconstruct identities, invent missing fields, or propose changes to source records. The goal is a test plan, formula, or exception list.

For stronger privacy practices, review NIST’s guidance on de-identifying personal information. De-identification lowers risk, but it does not grant permission to share confidential client information.

Give every prompt a useful data brief

Vague requests produce vague checks. Before asking for analysis, provide enough structure for the assistant to understand what each field means and which values are allowed.

Include the reporting period, column names, expected formats, business rules, and the outcome you need. Also state whether blanks are permitted and whether a repeated value can be legitimate.

Use this reusable context template

Copy and adapt this text before each task:

“Act as a data-quality analyst. I am reviewing a sanitized Excel dataset for [business purpose]. The reporting period is [start date] through [end date]. Columns are: [column name: meaning and expected type]. Business rules are: [rules]. Accepted values are: [list or reference table]. Treat all records as read-only. Do not invent corrections or assume a flagged value is wrong. Return: (1) the checks to run, (2) Excel formulas or features to use, (3) a clear exception reason for each flagged row, and (4) limitations or assumptions.”

Add a small sample only after the brief. For a sales export, explain that Order_ID should be unique, Order_Date must fall within the quarter, and Status can only be Open, Closed, Cancelled, or Pending.

Profile the dataset before cleaning anything

Start with an inventory. You need row counts, column types, blanks, unique values, minimum and maximum dates, and obvious format conflicts before deciding what needs repair. Many of those conflicts trace back to the request itself, which is why a precise consulting data request list that names fields, formats, and date ranges prevents most issues before the file arrives.

This first pass helps separate a real data issue from an unfamiliar client convention. For example, a blank Closed_Date may be valid for an open case.

Prompt for an initial Excel data profile

“Using the data brief and sanitized sample below, create a data-quality profile. For each column, identify the expected data type, likely actual type, blank-rate checks, unique-value checks, format risks, and business-rule tests. Separate confirmed checks from hypotheses. Recommend Excel formulas, PivotTable summaries, filters, or conditional formatting. Do not recommend editing or deleting records.”

In Excel, use filters to inspect outliers and a PivotTable to count categories. For a fast first view, Microsoft 365 users can also try Analyze Data in Excel, then verify any suggested pattern against the table.

Document the row count before and after each cleaning step. If it changes, you should know exactly why.

Find blank fields, invisible characters, and mixed formats

Many spreadsheet failures hide in plain sight. A cell that appears blank may contain a space. A customer code can differ because one value has a nonbreaking space. Numeric-looking values may be text, which breaks sums and lookups.

Add helper columns instead of overwriting the original fields. That gives you a before-and-after comparison for every proposed standardization.

Prompt for field-level cleanup tests

“Review these column definitions and sample values for blank, whitespace, hidden-character, case, and type inconsistencies. Propose non-destructive Excel helper columns that flag each issue. For every formula, explain what a TRUE or FALSE result means. Keep original values unchanged.”

For visible spaces, =LEN(A2) compared with =LEN(TRIM(A2)) can reveal extra spaces. =CLEAN(A2) removes many nonprintable characters, while TRIM removes extra standard spaces. Test a small subset first because source systems may use meaningful spacing in descriptions.

Microsoft’s COUNTIF function guidance also points to CLEAN and TRIM when counts behave unexpectedly.

Flag format mismatches without guessing fixes

Use formulas that describe the problem. For example, =ISTEXT(A2) flags text in a field expected to hold a number. =ISNUMBER(A2) tests whether Excel recognizes a value as numeric.

For email, phone, and postal fields, avoid claiming full validity from a simple formula. A basic pattern check only finds obvious problems. It cannot prove an address belongs to the person listed.

Check duplicates using the right business key

Duplicate analysis fails when the key is wrong. An invoice number might need to be unique, while a customer name should appear many times. A repeated transaction can be legitimate if it has a different line number or reversal status.

Define the grain of the data first. Ask what one row represents: an invoice, an invoice line, a customer, a payment, or a monthly balance.

Prompt for duplicate and near-duplicate analysis

“Assess duplicate risk for this sanitized dataset. The intended row grain is [describe one row]. Test exact duplicates and likely near-duplicates using [candidate key columns]. Identify cases that need human review, such as same invoice number with different amount or date. Provide helper-column formulas and a separate method that does not delete records.”

For exact duplicates based on Invoice_ID, use =COUNTIF([Invoice_ID],[@Invoice_ID])>1 inside an Excel Table. For composite keys, concatenate stable fields in a helper column, such as =[@Customer_ID]&"|"&[@Invoice_Date]&"|"&[@Amount], then count that key.

Excel can filter unique values or remove duplicates. During client work, flag duplicates first. Use Remove Duplicates only after the client or data owner approves the precise columns and rule.

Test client business rules, not only cell formats

A column can have perfect formatting and still contain bad data. A closed project may have no close date. A refund may carry a positive amount. An approved expense can exceed the policy limit.

Translate each policy into an explicit test. Keep the rule beside the formula so another analyst can audit the result.

Prompt for business-rule exception flags

“Convert the following client business rules into Excel validation tests. Use read-only helper columns and return an exception message that names the failed rule. Where a rule is ambiguous, ask a clarification question rather than choosing an interpretation. Rules: [paste rules]. Columns: [paste column names and definitions].”

A useful formula can return a readable result rather than a bare TRUE or FALSE:

=IF(AND([@Status]="Closed",[@Closed_Date]=""),"Closed record has no close date","")

For an approved amount threshold, use a reference cell or named range instead of hard-coding a number. That makes policy changes visible and easier to maintain.

Excel’s data validation controls can prevent future invalid entries. They don’t repair historical records, so keep your audit flags in place.

Validate dates, periods, and numeric logic

Dates cause expensive errors because Excel stores real dates as serial numbers but also accepts text that looks like a date. Regional formats add more risk. 03/04/2026 means different things in different contexts.

State the expected date format and reporting period in each prompt. If the client uses fiscal periods, provide the fiscal calendar rather than assuming the calendar year.

Prompt for date and amount checks

“Review the following date, period, quantity, and amount rules for a sanitized Excel extract. Identify tests for invalid dates, dates outside the reporting period, date-order conflicts, negative values, zero values, and totals that don’t reconcile. Provide formulas that flag exceptions without changing values. Use [date format] and [currency or unit].”

For a date in A2, =OR(A2<$B$1,A2>$B$2) flags values outside a start and end date stored in B1 and B2. Format the helper result as a warning, not as a correction.

Amounts also need context. A negative invoice may be a credit memo. A zero quantity may be valid on a service adjustment. Your prompt should name known exceptions so the AI doesn’t classify every unusual value as an error.

Reconcile related sheets and reference lists

Client files often contain a transaction sheet plus customer, product, or employee reference tables. Reconciliation checks whether every transaction points to a valid master record and whether totals agree across reports.

Use stable IDs when you can. Names change, vary in spelling, and may not be unique.

Prompt for lookup and reconciliation checks

“Create an Excel reconciliation plan for two sanitized tables. Table A is [transaction table and key]. Table B is [reference table and key]. Flag records in Table A with no matching key in Table B, keys with conflicting attributes, and differences between [total A] and [total B]. Return formulas, a summary layout, and possible legitimate explanations for each exception.”

XLOOKUP is a clear option for modern Excel. For example, =XLOOKUP([@Product_ID],Products[Product_ID],Products[Product_ID],"Missing") flags a transaction product that doesn’t appear in the master list. Microsoft’s XLOOKUP documentation explains its row-based lookup behavior and match options.

For older workbooks, COUNTIF can test existence without returning a value. A missing-reference flag should stay separate from any attempt to populate fields automatically.

Use Excel tools that preserve an audit trail

Formulas make individual tests visible. Conditional formatting makes patterns easier to scan. Power Query helps when a repeatable import needs the same normalization every month.

Choose the tool based on the work, not because it looks efficient. A one-time review may need helper columns. A recurring client report may justify a documented Power Query process.

Conditional formatting for review queues

Apply conditional formatting to helper columns or exception labels. Color alone is not enough because filters, exports, and accessibility settings can hide meaning. Include text such as “Missing master record” or “Date outside period.”

Avoid applying a rule to an entire workbook without testing it. A poorly scoped formula can flag thousands of rows for the wrong reason.

Power Query for repeatable preparation

Power Query records transformations as applied steps, which makes recurring cleanup easier to inspect. It can remove duplicates, change types, split columns, and merge reference data.

Still, don’t use a duplicate-removal step as a default cleanup action. Microsoft’s instructions for removing duplicate rows in Power Query describe the command, but your process must define which columns establish a duplicate and who approved the rule.

Review AI findings against the source data

Treat AI output as a draft workpaper. It may overlook a client rule, misunderstand a sample, or offer a formula that references the wrong table column.

Start with a small test set where you know the expected outcomes. Then apply the formula to the complete working table and inspect both flagged and unflagged rows.

Use a repeatable review sequence

1. Compare each proposed test with the written business rule and confirm the logic with the client contact when wording is unclear.
2. Test formulas on known valid, known invalid, blank, and edge-case records before filling them down.
3. Filter each exception type, inspect source values, and check related sheets or source-system evidence.
4. Record the rule, formula version, reviewer, date, and disposition in the Change_Log.
5. Ask for written approval before changing, excluding, merging, or deleting client records.

A proposed correction needs evidence. For example, a missing product code might be confirmed by a purchase order or source-system record. If evidence isn’t available, leave the original value intact and mark the item as unresolved.

Never turn an AI suggestion into a silent client-data correction.

Ask AI to produce a review-ready exceptions report

A useful final output is not a broad claim that the data is clean. It is a prioritized exception report that lets a client owner make decisions.

Ask for categories, record identifiers, rule names, impact, and recommended next actions. Do not request invented root causes.

Prompt for a client-ready exception summary

“Based only on the validated exception counts and examples below, draft a concise data-quality summary for a client. Separate confirmed exceptions from items needing client confirmation. Include the affected rule, count, potential reporting impact, and recommended owner action. Do not claim that data was corrected, do not infer root causes, and do not expose masked values.”

Keep the report factual. “14 transactions have dates outside the agreed reporting period” is useful. “The client entered dates incorrectly” is an unsupported accusation unless the evidence proves it.

Final checklist before sharing results

Use this checklist before sending an Excel file, exception report, or AI-assisted analysis to a client.

• The original file remains unchanged and recoverable.
• Any AI sample removed personal, confidential, and commercially sensitive details.
• The workbook states the data period, source file, row count, and review date.
• Each exception ties to a written rule, formula, or reconciliation test.
• You tested formulas against valid, invalid, blank, and edge-case records.
• Duplicate flags use an approved business key, not a guess.
• The report separates confirmed findings from items needing client confirmation.
• Approved changes appear in a change log with evidence and an owner.
• No correction, deletion, or merge happened silently.
• A reviewer can reproduce the checks from the working file.

Build Trust Into Every Excel Review

The best Excel analysis prompts give AI enough context to suggest useful tests while keeping sensitive client data out of the conversation. They also demand clear outputs: formulas, exception reasons, assumptions, and limits.

Reliable work comes from validation, not automation alone. Preserve raw records, test every finding against the source, and document approved changes. That discipline turns Excel analysis prompts into a dependable part of client delivery.