40 ChatGPT Prompts for Excel Automation and Spreadsheets

Act as an Excel expert with 20 years of experience. I need a formula to [describe what you want to achieve — e.g. 'find the latest sales figure for each customer in column A from a separate data table, return the date of that sale, and flag if it was over 90 days ago']. My data structure: [describe sheets, columns, data types]. Write the formula with: the complete formula, an explanation of each function used, common errors to watch for, and a simpler alternative if my data changes.

You are an Excel formula specialist. Explain how to use [XLOOKUP / INDEX MATCH / SUMIFS / COUNTIFS] to solve this problem: [describe problem]. Include: the syntax with each argument explained, 3 practical examples from a business context, how to handle errors with IFERROR, and when to use this function vs alternatives. Write for someone who knows Excel basics but hasn't used this function before.

Act as a VBA automation expert. Write a macro that: [describe what it should do — e.g. 'loops through all rows in Sheet1, checks if column D is blank, copies those rows to Sheet2, formats them with a specific colour, and shows a message box with the count of rows moved']. Include: fully commented code, error handling, a button I can add to the sheet to run it, and instructions for how to add it to my workbook. I am using Excel 2019 / Office 365.

You are a data cleaning specialist. I have imported a CSV into Excel and the data is messy: [describe problems — e.g. 'dates are text not dates, some names have extra spaces, phone numbers are inconsistent format, there are duplicates']. For each problem: write the formula or steps to clean it, explain why the data is in this state, and suggest how to prevent it on future imports.

Act as an Excel dashboard design expert. I want to create an executive KPI dashboard for [describe business function] showing: [list 6–8 KPIs]. Data is in [describe source sheets]. Build me: the recommended layout and structure, the formulas to pull and summarise each KPI, how to add traffic light conditional formatting, a dynamic chart recommendation for the most important metric, and how to make it automatically update when I refresh the source data.