ChatGPT Prompts for Excel: 7 Useful Formula Examples

ChatGPT prompts for Excel work best when you describe your columns, the result you need, and the spreadsheet app and version you use. The prompts below cover common tasks, including lookups, text cleanup, conditional totals, duplicate checks, and summaries. Each includes a sample formula plus a practical check before you rely on the result.

The key is to ask for a formula that fits your actual sheet, not just one that looks plausible. AI Excel formulas can save time, and ChatGPT for spreadsheets is most helpful when the prompt includes your real column layout. For ChatGPT Google Sheets requests, name the app explicitly so the model does not return Excel-specific syntax. Think of an Excel formula generator AI assistant as a drafting tool, not a source of guaranteed answers.

For a broader collection of copy-ready instructions, start with our AI prompts for work, then adapt the examples here to your workbook.

How do you get a reliable formula from ChatGPT?

Before prompting, write down the column names, cell ranges, desired output, and any edge cases. Tell ChatGPT whether you use Excel or Google Sheets, and, for Excel, whether you have Microsoft 365 or an older version. Ask it to state its assumptions, explain the formula, and provide a test case.

Use this starter pattern, then replace the details with your own data:

I use [Excel version or Google Sheets]. My columns are [describe headers and ranges]. I need to [state the result]. Write one formula for [cell], explain each argument, and handle [blank cells, missing matches, duplicates, or other edge cases]. Give a small example I can use to verify the result. Do not invent column names or assume a function my version does not support.

Excel and Google Sheets share many common functions, but support and syntax can vary. This quick comparison can help you set the right context in your prompt.

Do not paste private customer, payroll, or financial data into a chatbot just to get a formula. Replace real values with sample rows, or describe the columns without sharing the underlying records. You can also browse our free tools, templates, and guides for other ways to structure routine work.

1. Which formula adds values that meet two conditions?

Use this prompt when you need a total for one region and one product, or any two criteria. It is a good fit for sales reports, expense tracking, inventory totals, and monthly performance sheets.

Copy this prompt

In Excel, my Sales sheet has Region in C2:C1000, Product in D2:D1000, and Revenue in F2:F1000. Cell J2 contains the region and J3 contains the product. Return a SUMIFS formula that totals revenue only when both criteria match. Use fixed ranges and explain how the criteria cells work.

Example formula

=SUMIFS($F$2:$F$1000,$C$2:$C$1000,$J$2,$D$2:$D$1000,$J$3)

This adds numbers in column F only for rows where column C matches J2 and column D matches J3. You can reuse the formula by changing the values in J2 and J3, rather than editing the formula each time.

How to check it

Filter the original table to the same region and product, then compare the visible revenue total with the formula result. Confirm that all three ranges start and end on the same rows and that Revenue contains numbers, not numbers stored as text. If you need a date window as well, ask ChatGPT to add start-date and end-date criteria rather than layering on an untested condition yourself.

2. How do you look up a value by ID or SKU?

Lookup formulas bring a related value from another table, such as a price, department, customer name, or shipping status. They are especially useful when one sheet contains transaction IDs and another contains reference information.

Copy this prompt

I use Excel Microsoft 365. On the Products sheet, SKUs are in A2:A500 and unit prices are in E2:E500. The SKU to search is in H2. Write an XLOOKUP formula that returns the matching price, displays Not found when there is no match, and uses an exact match. Explain what happens when the SKU is missing.

Example formula

=XLOOKUP(H2,Products!$A$2:$A$500,Products!$E$2:$E$500,"Not found",0)

XLOOKUP searches the SKU range and returns the price from the matching row. The final 0 requests an exact match, and the text argument provides a readable result for missing SKUs. Read the official XLOOKUP documentation for the full argument list and version notes. Microsoft lists Excel 2016 and Excel 2019 as versions that do not support the function.

How to check it

Test one SKU you can verify manually, one SKU that does not exist, and one cell with an extra space or a different text format. If a known SKU returns Not found, check for hidden spaces and confirm that IDs are stored consistently as text or numbers. Do not hide a genuine mismatch by switching to approximate matching unless your task specifically requires it.

3. How can ChatGPT clean inconsistent text?

Imported data often contains leading spaces, repeated spaces, or nonprinting characters that make matching and grouping unreliable. Ask for a cleanup formula that preserves the original column, so you can inspect the cleaned values before replacing anything.

Copy this prompt

In Excel or Google Sheets, customer names are in A2. Give me a formula for a new column that removes leading and trailing spaces, reduces repeated spaces between words, and replaces nonbreaking spaces. Keep A2 unchanged and explain any limits of the formula.

Example formula

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

SUBSTITUTE replaces common nonbreaking spaces with regular spaces, CLEAN removes many nonprinting characters, and TRIM tidies ordinary spaces. This is a useful first pass, not a guarantee that every imported character or spelling variation will be fixed. Names that differ by punctuation, accents, or spelling still need a deliberate matching rule.

How to check it

Compare the original and cleaned columns side by side. Look at records that used to appear as separate names and check whether the cleaned result is still readable. Try a few rows copied from the source system, especially if the data came from a web page or PDF. Once the results look right, use the cleaned column for matching, filtering, or duplicate checks.

4. How do you flag duplicate records without deleting them?

Duplicate checks are helpful before sending invoices, merging customer lists, or counting unique orders. A formula lets you flag the rows for review instead of deleting data that may only look duplicated.

Copy this prompt

Order IDs are in A2:A1000. Write an Excel formula for B2 that labels an ID Duplicate if it appears more than once in column A, and Unique if it appears only once. Keep blank IDs blank, use absolute references where needed, and tell me how to fill it down.

Example formula

=IF(A2="","",IF(COUNTIF($A$2:$A$1000,A2)>1,"Duplicate","Unique"))

COUNTIF counts how often the current ID appears in the full range. This formula labels every occurrence of a repeated ID as Duplicate, including the first one. If you want to flag only the second and later occurrences, say so in your prompt because the formula logic is different.

How to check it

Test a known repeated ID, an ID that appears once, and a blank row. Filter column B to Duplicate and inspect the matching records before taking action. Two identical customer names may belong to different people, while two identical order IDs may indicate a real data problem, so confirm the business rule before removing anything.

5. How do you label orders as late or on time?

Date formulas are useful for delivery tracking, project plans, invoice follow-ups, and task lists. The important part is specifying how the formula should treat missing dates, because an empty completion date often means a task is still open rather than complete or late.

Copy this prompt

My worksheet has Due Date in G2 and Completed Date in H2. Write a formula for I2 that returns Missing due date when G2 is blank, Open when G2 has a date but H2 is blank, Late when the completion date is after the due date, and On time otherwise. Use Excel syntax and explain the order of the checks.

Example formula

=IF(G2="","Missing due date",IF(H2="","Open",IF(H2>G2,"Late","On time")))

The formula tests missing due dates first, then checks whether completion is still blank, and finally compares the two dates. This order prevents an open task from being labeled On time simply because the completion cell is empty.

How to check it

Test four rows: no due date, a due date with no completion date, a completion date after the deadline, and a completion date on or before the deadline. Make sure the cells contain real spreadsheet dates, not text that only looks like a date. If the sheet uses different date formats by country, ask ChatGPT to explain how to recognize and convert text dates before relying on the result.

6. How do you create a clean list of unique values?

A unique list is handy when preparing a dropdown, checking which products are present, or creating a list of regions for a report. Combining FILTER, UNIQUE, and SORT can remove blanks, keep one copy of each value, and arrange the result alphabetically.

Copy this prompt

Product names are in D2:D1000. Give me a formula for Excel Microsoft 365 or Google Sheets that returns a sorted list of distinct nonblank product names. Do not change the source data, and tell me what to check if the result spills into cells that are not empty.

Example formula

=SORT(UNIQUE(FILTER(D2:D1000,D2:D1000<>"")))

FILTER removes empty cells, UNIQUE keeps one copy of each remaining value, and SORT orders the result. These are dynamic-array style results, so the output can expand into neighboring cells instead of requiring you to copy the formula down. The exact function availability depends on the spreadsheet app and version.

How to check it

Compare the number of results with the number of distinct product names you expect. Look for variants such as “Widget A” and “Widget A ” with a trailing space, because they may still count as different values until cleaned. If the formula returns a spill or expansion error, clear the output area and make sure merged cells or existing values are not blocking the results.

7. How can you summarize data like a pivot table?

Summaries help turn long transaction lists into totals by region, category, month, or salesperson. In Excel, ChatGPT can suggest a PivotTable layout; in Google Sheets, it can generate a formula such as QUERY for a formula-driven summary. Tell it which field should define the groups and which numeric field should be added.

Copy this prompt

My Google Sheets data is in A1:F1000. Column C is Region and column F is Revenue, with headers in row 1. Give me a QUERY formula that returns one row per region and the sum of revenue, excludes rows with no region, uses the first row as headers, and labels the total column Total revenue. Explain the query in plain English.

Example formula

=QUERY(A1:F1000,"select C, sum(F) where C is not null group by C label sum(F) 'Total revenue'",1)

The query groups rows by Region and adds Revenue within each group. Google documents the Google Sheets QUERY function and its query-language syntax. For Excel, ask for the PivotTable field arrangement instead: Region in Rows and Revenue in Values summarized by Sum. Microsoft's PivotTable guide explains how to create and arrange that report.

How to check it

Pick one region, filter the source rows to that region, and calculate its revenue total independently. Check that Revenue contains numeric values and that the source has a single header row. Mixed text and numbers in a column can cause query results to omit values, so clean inconsistent data before assuming the summary is complete.

What should you check before using an AI-generated formula?

ChatGPT can write a formula that is syntactically convincing but wrong for your actual spreadsheet. Treat the result as a draft, especially when it affects invoices, payroll, forecasts, client reporting, or business decisions. The practical skill is not only getting a formula, but knowing what it should return.

• Confirm the ranges. Check that the formula references the intended worksheet, columns, and rows, and that fixed ranges remain fixed when copied.
• Test edge cases. Include a blank cell, a missing lookup, a duplicate, a zero, and a boundary value where relevant.
• Compare a known result. Calculate a small sample manually or with a second method before applying the formula to thousands of rows.
• Check your version. If the formula uses a newer function, ask for an alternative that works in your Excel release or in Google Sheets.
• Keep the logic explainable. Ask the chatbot to explain each argument and list its assumptions. If you cannot explain what the formula does, do not use it in a report yet.

AI is most useful when it reduces repetitive work while leaving judgment with you. Building that habit is one practical way to future-proof your career against AI: learn to describe a problem, verify an answer, and spot a result that does not fit the data. Save prompts that work well in a personal library, with a note about the workbook layout they were designed for.

For reusable wording beyond spreadsheets, explore our AI prompts library and note which prompts are safe to reuse across projects.