--- title: "Excel Formulas With AI: Cleanup, Pivots, Renewal Forecasts" url: "https://goremy.ai/blog/articles/ai-for-excel-formulas-cleanup-and-pivot-tables-without-the-pain" author: Luis Chavez-Mattos editor: Luis Chavez-Mattos editor_title: Director, Product published: "2026-11-11T19:40:04.499Z" updated: "2026-11-11T19:40:04.499Z" topic: AI for Excel description: How to use AI for Excel formulas, data cleanup before a migration, pivot tables and a renewal forecast, with prompts you can paste and checks to run. --- # Excel Formulas With AI: Cleanup, Pivots, Renewal Forecasts > How to use AI for Excel formulas, data cleanup before a migration, pivot tables and a renewal forecast, with prompts you can paste and checks to run. Edited by Luis Chavez-Mattos, Director, Product ยท Published 2026-11-11 ## How do you use AI to write Excel formulas? Describe your sheet and the result you want in plain English, and ask the AI for a formula. Give it your column names, your Excel version, two or three example rows with the answer you expect, and any edge cases (blanks, duplicates, text where a number should be). Paste the formula into one cell, test it against rows where you already know the answer, then fill it down. The AI writes the syntax. You still own the check. That last part matters. AI is very good at formula syntax and weak at knowing what your data really looks like. The rest of this post gives you the prompts and checks for four jobs people search for most: writing formulas, cleaning data, building pivot tables, and building a renewal forecast. ## TL;DR - **Describe the layout, not just the goal.** A prompt that lists column letters, headers and sample rows gets a formula that works the first time far more often than "write a formula to find renewals." - **Test every AI formula on rows where you know the answer.** Three rows with known results will catch most wrong formulas before they reach a report. - **Never paste real customer data into a public chatbot.** Paste headers and five invented rows instead, or use the AI tool your company has approved. - **Cleaning data is a sequence of small formulas** (trim, case, dates, duplicates, lookups against a mapping table), and AI is good at writing each one and explaining why a row failed. - **For pivot tables, ask AI which fields go where**, or ask for the SUMIFS equivalent if you want something that updates without refreshing. - **A renewal forecast is four columns and one summary table.** The formulas are short. The hard part is choosing honest probabilities. ## What should you put in an AI prompt for an Excel formula? Five things: version, layout, goal, examples and edge cases. Leaving out any of them is where most bad answers come from. Copy this and fill in the brackets: ``` I'm using [Excel for Microsoft 365 / Excel 2019 / Excel for Mac]. My data is on a sheet called [Sheet name], with headers in row 1: A: [Customer], B: [Start date], C: [Term in months], D: [ARR], E: [Risk tier] Goal: [in plain English, e.g. "In column F, show the renewal date, which is the start date plus the term."] Examples of what I expect: - Row 2: start 15-Jan-2025, term 12 -> 15-Jan-2026 - Row 3: start 31-Aug-2025, term 6 -> [your expected answer] Edge cases: [blank start dates, terms entered as text, duplicates, etc.] Give me: 1. The formula for row 2, ready to paste and fill down. 2. A one-sentence explanation of what each part does. 3. Anything in my data that could make it return the wrong answer. 4. A version that works in older Excel if you used a newer function. ``` Item 4 saves real time. Functions like XLOOKUP, FILTER, UNIQUE, LET and TEXTSPLIT exist only in newer versions of Excel. If a coworker opens your file in an older version, those cells show errors. ### What to check in the answer - **The function exists in your version.** If Excel says the name is not valid, the AI may have invented a function or used one you don't have. Ask for a version that uses older functions. - **Cell references lock where they should.** A lookup table range needs dollar signs ($K$2:$L$4) so it doesn't slide down as you fill the formula. - **Separators match your region.** Some regional settings use semicolons instead of commas between arguments. If the formula errors on paste, try swapping them. - **Blanks and text.** Add a blank row and a row with a typo to your test rows. See what the formula does. ### Where should you not trust AI with Excel? Don't ask a chatbot to do the arithmetic on data you paste in. Ask it to write the formula, and let Excel do the math. Language models can miscount, skip rows, or return a total that looks plausible and is wrong. A formula is checkable and repeatable. A pasted-in answer is neither. ## How do you clean data in Excel with AI? Treat cleaning as a short sequence of fixes, each with its own formula in a helper column. Leave the original column untouched. Ask the AI to write one formula per problem, and ask it to explain any row that comes out wrong. This works the same whether you use Copilot in Excel (if your company licenses it), a chat assistant you paste prompts into, or no AI at all. Start by showing the AI what is messy. Take five invented rows that mimic your real problems: ``` Here are example rows from my customer export (invented, same patterns as the real data). Columns: Name, Email, Phone, Country, Signup date. john SMITH | John.Smith@Example.com | (415) 555-0142 | USA | 3/4/24 Maria Lopez | maria.lopez@example.com | 415.555.0199 | U.S. | 2024-03-05 ... List every kind of inconsistency you can see. Then, for each one, give me an Excel formula that fixes it into a helper column, and tell me what the formula will NOT catch. ``` That last sentence is the useful one. It makes the AI list its own blind spots. ### Which formulas fix the usual problems? | Problem | Typical fix | |---|---| | Extra spaces and odd characters | `=TRIM(CLEAN(A2))` | | Non-breaking spaces from web or CRM exports (TRIM misses these) | `=TRIM(SUBSTITUTE(A2,CHAR(160)," "))` | | Inconsistent capitalization in names | `=PROPER(TRIM(A2))` (check names like McDonald or van der Berg by hand) | | Emails in mixed case | `=LOWER(TRIM(B2))` | | Phone numbers in many formats | Strip punctuation with nested `SUBSTITUTE`, then check length | | Dates stored as text | `=DATEVALUE(E2)`, or Text to Columns, then format as a date | | ZIP codes that lost a leading zero | `=TEXT(A2,"00000")` for five-digit US codes | | Country written six ways | A mapping table plus `XLOOKUP` or `VLOOKUP` | | Obvious bad emails | `=IF(AND(ISNUMBER(SEARCH("@",B2)),ISNUMBER(SEARCH(".",B2))),"OK","Check")` | Ambiguous dates are the one that causes real damage. Is 3/4/24 March 4th or April 3rd? Neither you nor the AI can know from the cell alone. Check against the source system or ask whoever exported the file. ## How do you clean customer data in Excel for a migration? Moving customer records into a new CRM, billing tool or ERP fails on small things: a duplicate account, a missing required field, a country name the new system doesn't recognize. Work in this order, and keep the original export on its own tab, unedited. 1. **Get the target system's import template or field list first.** Note which fields are required, which have fixed values (such as a country or status list), and which formats it expects. Clean toward that, not toward what looks tidy. 2. **Save a copy and add a permanent ID column.** If your export has a source system ID, keep it. It is how you trace any row back to the original after a problem. 3. **Run the basic fixes** from the table above in helper columns: spaces, case, dates, phones, ZIP codes. 4. **Standardize categories with a mapping table.** Make a two-column tab: "what's in the data" and "what the new system wants." Ask the AI to build the first column from your unique values (`=UNIQUE(D2:D5000)` in Microsoft 365) and propose the second. Then you review every line. The mapping table is the highest-value thing AI drafts in this whole process. 5. **Flag duplicates before deleting any.** Build a match key, for example lowercase email, or lowercase name plus ZIP. Then count: `=COUNTIF($H$2:$H$5000,H2)` Anything above 1 gets a look. Use "Remove Duplicates" only after a human has decided which record wins, because it keeps the first row it finds, which may be the stale one. 6. **Check required fields.** `=COUNTBLANK(A2:E2)` per row, or a filter on blanks in each required column. Put these rows on a separate tab for someone to fix at the source. 7. **Reconcile counts.** Original rows, minus intentional deletions, should equal clean rows. Write down the numbers. 8. **Load a small test batch** (50 to 100 rows) into a sandbox or test environment if the new system has one. Fix what breaks, then load the rest. For files with tens of thousands of rows, or cleanup you'll repeat every month, learn Power Query (Data tab, Get Data). It records each step so you can refresh instead of redoing the work. AI can walk you through the steps and explain its formula language, but check the results the same way. ### What is safe to give AI when the data is customer data? Customer names, emails and phone numbers are usually covered by your company's privacy and security policy. Check it before pasting anything into an AI tool. The safe default is the one used above: invent five rows with the same problems, get the formulas, and run them in your own file. If your company has an approved AI assistant with data protections, ask your IT or security team what you are allowed to put in it. ## How do you build a renewal forecast in Excel? A renewal forecast answers one question: of the contracts coming up for renewal each month, how much revenue do we expect to keep? You need a contracts table, a small probability table, and a monthly summary. Here is a layout that works for subscriptions, service contracts, leases or memberships. **Sheet "Contracts"** (one row per contract): | Col | Header | What goes in it | |---|---|---| | A | Customer | Name | | B | Start date | Real date | | C | Term (months) | Number | | D | ARR | Annual value | | E | Risk tier | Low, Medium or High | | F | Renewal date | Formula | | G | Renewal probability | Formula (lookup) | | H | Expected renewal value | Formula | | I | Renewal month | Formula | | J | Days to renewal | Formula | **Sheet "Tiers"** (the probability table, range K2:L4 on the Contracts sheet works too): | Tier | Probability | |---|---| | Low | your historical rate | | Medium | your historical rate | | High | your historical rate | Fill in those rates from your own history (how many Low-risk contracts actually renewed last year?), not from a guess. The forecast is only as honest as these three numbers. ### Which formulas do you use? In row 2 of the Contracts sheet: - **Renewal date (F2):** `=EDATE(B2,C2)` (format the cell as a date) - **Probability (G2):** `=IFERROR(VLOOKUP(E2,Tiers!$A$2:$B$4,2,FALSE),0)` - **Expected value (H2):** `=D2*G2` - **Renewal month (I2):** `=EOMONTH(F2,-1)+1` (the first day of the renewal month) - **Days to renewal (J2):** `=F2-TODAY()` Optional flag for the next 90 days: `=IF(AND(J2>=0,J2<=90),"Renewing soon","")` Then build the summary on its own sheet. Put the first day of each month in column A, and: - **Contracts up for renewal:** `=SUMIFS(Contracts!$D:$D,Contracts!$I:$I,A2)` - **Expected to renew:** `=SUMIFS(Contracts!$H:$H,Contracts!$I:$I,A2)` - **Gap at risk:** the first minus the second A pivot table gives you the same summary: Renewal month in Rows, sum of ARR and sum of Expected value in Values. SUMIFS updates the moment data changes. A pivot needs a refresh. ### What should you ask AI to do with a renewal forecast? Use it for the formulas and for stress-testing the logic: ``` I'm building a renewal forecast in Excel for [subscriptions / service contracts]. Columns: A Customer, B Start date, C Term in months, D ARR, E Risk tier (Low/Medium/High). I want: renewal date, a probability looked up from a tier table, expected renewal value, and a monthly summary of contracted vs expected renewals. 1. Give me formulas for each column with the exact cell references. 2. Tell me what goes wrong if a contract is month-to-month, has auto-renewal, or has a start date on the 31st. 3. Suggest two checks I can add so the summary total must equal the sum of the detail rows. ``` Question 3 gives you a reconciliation cell, such as `=SUM(Summary!B:B)-SUM(Contracts!D:D)`, which should be zero if every contract falls in a month on your summary. If it isn't, a date is blank or outside your range. ## How do you make pivot tables with AI? A pivot table summarizes rows by categories: revenue by month, headcount by department, tickets by owner. If you have Copilot in Excel, you can ask it to create one from a table. If you don't, use any AI assistant as a planner. Paste your headers and this prompt: ``` My table has these columns: [headers]. I want to answer: [your question, e.g. "How much ARR renews each quarter, by region?"]. Tell me exactly which fields go in Rows, Columns, Values and Filters, which summary type to use (sum, count, average), and whether I need a helper column first (for example a Quarter column built from a date). Then give me the SUMIFS formula that would produce the same number for one cell so I can check the pivot. ``` Before you trust a pivot, make sure: - **Your source is a proper table.** Select the data and press Ctrl+T. New rows are then included automatically. - **Numbers are numbers.** If a Values field shows "Count" when you wanted "Sum," some cells hold text. Fix that first. - **No blank rows or merged cells** inside the data. - **The grand total matches** a plain `=SUM()` of the source column. Spot-check one pivot cell with the SUMIFS formula the AI gave you. If they match, the setup is right. ## What are the common mistakes with AI and spreadsheets? - **Accepting a formula because it returned a number.** A wrong number looks the same as a right one. Test with known answers. - **Overwriting the original data.** Always work in helper columns or a copy. - **Letting AI decide which duplicates to delete.** Matching "J. Smith" with "John Smith" needs a human who knows the customers. - **Hardcoding values inside formulas,** such as `=D2*0.85`. Put assumptions in labeled cells so others can see and change them. - **Not saying which Excel version you have.** This is the cause of most "the formula doesn't work" complaints. - **Skipping documentation.** Ask the AI to write a short notes tab describing each column and formula. Whoever inherits the file will thank you. ## Should a renewal tracker stay in a spreadsheet? For one person and a few dozen contracts, yes. When several people need to update it, when it needs sign-in and role-based access, or when you want reminders sent before each renewal, a spreadsheet starts to strain. That is where a small purpose-built app makes sense. With [Remy](https://goremy.ai) you describe the tracker in plain English (the fields, who can edit, what should trigger an email), and it builds and deploys a full-stack app with a database and sign-in, usually in about an hour for a first build. It costs $99 a month, or $79 billed annually, plus AI usage at cost. Your Excel layout above doubles as the description to start from. ## Frequently Asked Questions ### Can ChatGPT or Copilot write Excel formulas accurately? They write correct syntax most of the time, especially for common functions like SUMIFS, XLOOKUP, EDATE and TRIM. Errors come from missing context: your real column layout, your Excel version, and messy data the AI never saw. Give it examples, test on rows with known answers, and ask it to list the cases where the formula would fail. ### What is the best way to clean data in Excel with AI? Use AI to diagnose and to write formulas, and let Excel apply them. Paste invented sample rows that show your problems, ask for one formula per problem in helper columns, and keep the original data untouched. For repeated cleanups on large files, Power Query records the steps so you can refresh instead of redoing them. ### Is it safe to paste customer data into an AI tool? Not without checking your company's policy. Customer names, emails and phone numbers are often restricted. Paste headers and invented example rows instead, or use an AI tool your company has approved for that kind of data, and ask IT or security if you are unsure. ### How do I forecast renewals if I have no history for probabilities? Start with your best estimate by risk tier, label it clearly as an assumption in its own cells, and compare it to actual outcomes after one or two renewal cycles. Then replace the guesses with real rates. Showing a range (a low and a high case) is more honest than a single number. ### Do I need Microsoft 365 to use these formulas? No. EDATE, EOMONTH, SUMIFS, VLOOKUP, TRIM, CLEAN, COUNTIF and pivot tables work in older versions of Excel. UNIQUE, XLOOKUP and similar newer functions need Microsoft 365 or a recent version, so tell the AI your version and ask for an older-compatible alternative.