AI Prompts for ChatGPT for Excel

Write an Excel formula to accomplish this: [DESCRIBE WHAT YOU WANT TO CALCULATE OR LOOK UP. BE SPECIFIC: WHAT DATA IS IN EACH COLUMN, WHAT SHOULD THE RESULT BE, ANY CONDITIONS]. Show: (1) the formula, (2) a plain-English explanation of how it works, (3) the cell reference assumptions (e.g., "assumes your data starts in A2"), (4) any edge cases that might return errors and how to wrap the formula to handle them (IFERROR, IFNA). If there are multiple approaches, show the simplest and the most robust version.

Help me write a lookup formula to [DESCRIBE WHAT YOU WANT TO LOOK UP: FIND A CUSTOMER NAME, MATCH AN ID TO A PRICE, PULL A VALUE FROM ANOTHER SHEET]. My data layout: [DESCRIBE: WHAT COLUMN HAS THE LOOKUP VALUE, WHAT COLUMN HAS THE RETURN VALUE, WHETHER IT IS ON THE SAME SHEET OR ANOTHER]. Write: (1) XLOOKUP version (Excel 365/2021), (2) VLOOKUP version (older Excel), (3) INDEX/MATCH version as the universal fallback, and (4) how to handle the case where no match is found. Explain why XLOOKUP is preferred when available.

Write an Excel formula that handles this conditional logic: [DESCRIBE THE CONDITIONS, E.G., "IF COLUMN A SAYS X AND COLUMN B IS GREATER THAN 100, THEN Y, OTHERWISE IF COLUMN C IS BLANK THEN Z, OTHERWISE RETURN COLUMN D"]. Use: nested IFs, or IFS function (Excel 2019+), or SWITCH if it applies. Show the formula, explain the logic, and note any order-of-evaluation issues I should be aware of if conditions overlap. Also show a CHOOSE-based alternative if simpler.

Write an Excel array formula or dynamic array formula to [DESCRIBE THE AGGREGATION TASK: SUM VALUES MEETING MULTIPLE CONDITIONS, COUNT UNIQUE VALUES, FIND THE NTH LARGEST, AGGREGATE ACROSS FILTERED ROWS]. For Excel 365/2021, use: FILTER, UNIQUE, SORT, SORTBY, SUMPRODUCT, or BYROW/BYCOL where appropriate. For older Excel, provide the Ctrl+Shift+Enter array formula equivalent. Explain when SUMPRODUCT is more compatible than SUMIFS, and show a worked example with sample data.

Write Excel formulas for these date/time calculations: [DESCRIBE WHAT YOU NEED: CALCULATE BUSINESS DAYS BETWEEN DATES, FIND THE LAST DAY OF A MONTH, CHECK IF A DATE FALLS IN A SPECIFIC QUARTER, CALCULATE AGE FROM BIRTHDATE, FIND THE NEXT OCCURRENCE OF A WEEKDAY, ADD WORKING DAYS TO A DATE]. For each calculation, provide: the formula, any NETWORKDAYS.INTL parameters needed for custom work week configurations, how to handle dates that fall on weekends or holidays, and a sample with example dates to verify the formula is working correctly.

Help me design a pivot table to analyze this data: [DESCRIBE YOUR RAW DATA: WHAT COLUMNS IT HAS, WHAT YOU WANT TO UNDERSTAND]. Tell me: (1) which field(s) should go in Rows, (2) which in Columns, (3) which in Values and what aggregation (sum, count, average), (4) which in Filters, (5) any calculated fields I should add, (6) how to sort the pivot for maximum insight, (7) any grouping I should apply (group dates by month/quarter, group numbers by range). Also suggest 2-3 additional pivot table variations from the same data that would reveal different insights.

Write Power Query M code to transform this data: [DESCRIBE THE RAW DATA AND THE DESIRED OUTPUT]. The transformation steps needed: [LIST: FILTER ROWS, REMOVE COLUMNS, SPLIT COLUMNS, MERGE TABLES, UNPIVOT, CHANGE TYPES, REPLACE VALUES]. Provide: (1) the full M query code, (2) a step-by-step explanation of each Applied Step, (3) how to add this to the Power Query editor by pasting into the Advanced Editor, and (4) any performance considerations for large datasets. Also show how to parameterize the query to make the input path or filter values configurable.

Write Power Query steps to clean this messy dataset: [DESCRIBE THE DATA QUALITY ISSUES: INCONSISTENT CAPITALIZATION, EXTRA SPACES, MERGED CELLS WERE IMPORTED INCORRECTLY, NUMBERS STORED AS TEXT, DUPLICATE ROWS, INCONSISTENT DATE FORMATS, NULL VALUES IN KEY COLUMNS]. For each issue, write the Power Query transformation that fixes it, explain why this happens and how Power Query handles it, and note which cleaning steps should be done first (order matters). Also show how to apply the same cleaning transformation to new files as they are added.

I need to create a calculated field in my pivot table to [DESCRIBE THE CALCULATION: GROSS MARGIN %, CONVERSION RATE, YEAR-OVER-YEAR GROWTH, COST PER UNIT]. My base fields are: [LIST THE FIELDS IN YOUR DATA]. Write: (1) the calculated field formula, (2) how to add it via PivotTable Analyze > Fields, Items & Sets > Calculated Field, (3) any limitations of calculated fields vs. adding the calculation in the source data, and (4) an alternative approach using GETPIVOTDATA or helper columns in the source data if the calculated field approach has limitations for my specific calculation.

Help me design an Excel dashboard to show these KPIs: [LIST 5-10 KPIS WITH THEIR SOURCES]. Design requirements: (1) it should update automatically when new data is added, (2) includes a date slicer or filter for time period selection, (3) shows trend lines or sparklines for each KPI, (4) uses conditional formatting to highlight performance vs. target. Describe: the sheet structure I should build, which charts to use for each KPI type, how to connect slicers to multiple pivot charts, and how to protect the dashboard while keeping the data updatable.

Write a VBA macro to [DESCRIBE THE TASK: LOOP THROUGH ROWS AND COLOR-CODE BASED ON VALUE, GENERATE A NEW SHEET FOR EACH ROW IN A LIST, IMPORT AND FORMAT MULTIPLE CSV FILES, SEND AN EMAIL WITH AN EXCEL RANGE AS A TABLE, CREATE A PDF REPORT FOR EACH SALESPERSON]. Include: (1) the full Sub procedure with descriptive variable names, (2) error handling with On Error GoTo and a cleanup section, (3) a confirmation message when complete, (4) comments explaining the logic, and (5) instructions for where to paste the code and how to run it. Make the macro safe to run multiple times.

Write VBA code to create a UserForm that [DESCRIBE WHAT DATA THE USER SHOULD ENTER AND WHAT SHOULD HAPPEN WHEN THEY SUBMIT]. The form should include: [LIST THE INPUT FIELDS: TEXT BOXES, DROPDOWNS, DATE PICKERS, CHECKBOXES]. Include: (1) the form layout description and control setup, (2) input validation that checks for required fields and valid data before submitting, (3) the code that writes the form data to the spreadsheet when the Submit button is clicked, (4) a Cancel button that closes the form without saving, and (5) how to open the form from a button on the worksheet or from a keyboard shortcut.

Add proper error handling to this VBA macro: [PASTE YOUR EXISTING VBA CODE]. Implement: (1) On Error GoTo ErrorHandler at the start of the sub, (2) a labeled error handler at the bottom that shows a user-friendly message (not the raw VBA error), (3) cleanup code that runs regardless of success or failure (close files, restore settings), (4) logging the error details to a sheet or text file for debugging, (5) Exit Sub before the error handler to prevent it from running on success. Explain the VBA error handling model and why On Error Resume Next is usually a bad practice.

Write a VBA macro to loop through [ALL SHEETS IN THIS WORKBOOK / ALL EXCEL FILES IN A FOLDER] and [DESCRIBE WHAT ACTION TO PERFORM ON EACH: EXTRACT A VALUE, COPY A RANGE, RUN A CALCULATION, RENAME SHEETS]. Include: (1) the For Each loop structure for sheets or the Dir() loop for files, (2) logic to skip any sheets or files that should be excluded, (3) error handling for individual sheet or file failures that continues to the next item rather than stopping, (4) a progress indicator (can be a status bar update or a counter cell on a summary sheet), (5) a results log of what was processed.

Write a VBA macro that generates a formatted report from raw data and saves it as a new Excel file or PDF. The raw data is in: [DESCRIBE SHEET NAME AND STRUCTURE]. The report should: (1) extract and summarize [DESCRIBE WHAT DATA GOES IN THE REPORT], (2) apply formatting (fonts, colors, borders, number formats) to match this style: [DESCRIBE], (3) add a header with the report date and title, (4) apply conditional formatting to highlight values above/below threshold, (5) save as a PDF to a specified folder with the date in the filename, (6) send an email with the PDF attached using Outlook automation. Make the output path configurable at the top of the macro.

I am getting a [#VALUE! / #REF! / #N/A / #DIV/0! / #NAME? / #SPILL! / ######] error in this Excel formula: [PASTE YOUR FORMULA]. My data context: [DESCRIBE WHAT THE CELLS CONTAIN]. Diagnose: (1) what this specific error means, (2) the most likely cause in my formula, (3) the corrected formula, (4) how to use the Formula Auditing tools in Excel to trace the error myself next time, and (5) how to wrap the formula to display a meaningful value instead of an error.

My Excel file is running very slowly. The file has: [DESCRIBE: NUMBER OF ROWS, HOW MANY FORMULAS, ANY VOLATILE FUNCTIONS LIKE NOW() OR RAND(), ANY LINKS TO OTHER WORKBOOKS, ANY PIVOT TABLES OR POWER QUERY QUERIES]. Diagnose likely performance causes and suggest fixes: (1) which formulas to replace with less volatile alternatives, (2) how to convert formula ranges to static values where the source data does not change, (3) how to optimize pivot table data sources, (4) whether to use manual calculation mode during editing, (5) any Power Query optimizations, (6) general file cleanup steps (unused sheets, styles, named ranges). Estimate which fix will have the biggest impact.

Set up Excel data validation rules for this spreadsheet: [DESCRIBE THE COLUMNS AND WHAT VALID DATA LOOKS LIKE FOR EACH]. For each column, specify: (1) the data validation type (list, whole number, decimal, date, text length, custom formula), (2) the validation parameters, (3) the error message to display when invalid data is entered (title and message), (4) whether to allow the user to override with a warning or strictly reject invalid input. Also show how to create a dropdown list from a range on another sheet, and how to create dependent dropdowns where the options in column B depend on the value in column A.

Write either an Excel Power Query or a VBA macro to consolidate data from [DESCRIBE: MULTIPLE SHEETS IN ONE WORKBOOK / MULTIPLE WORKBOOKS IN A FOLDER / A COMBINATION]. The data on each sheet has [DESCRIBE STRUCTURE: SAME COLUMNS, DIFFERENT LAYOUTS, DIFFERENT DATE RANGES]. The consolidated output should: (1) stack all data into one table, (2) add a column identifying the source sheet or file, (3) handle any schema differences between sources, (4) remove any header rows from individual sheets except the first, (5) sort the result by [DESCRIBE]. Recommend Power Query for this over VBA where it would be more maintainable.

Help me set up dynamic named ranges in Excel that automatically expand as I add data. I have a data table that starts in [DESCRIBE: CELL A1, HOW MANY COLUMNS, NO DEFINED TABLE YET]. Set up: (1) an Excel Table (Ctrl+T) as the modern approach that handles this automatically, (2) alternatively, OFFSET + COUNTA named range formulas for older Excel compatibility, (3) how to reference these named ranges in formulas and chart data sources, (4) how to use INDIRECT with named ranges for dynamic formula references, (5) the trade-offs between Excel Tables and OFFSET-based named ranges for my use case.