Are you looking for the best ChatGPT prompts that can help you automate Excel sheets?

You are in the right place.

Today, I will give you the 15 advanced ChatGPT prompts for Excel.

If you want more Ready-made Advanced ChatGPT prompts, You can check out.

Here are all the ChatGPT Prompts:

1️⃣ Advanced Excel Formula Creation

*”I am working on an Excel spreadsheet and need a formula to automate calculations. The data is structured as follows:

• Column A contains [describe data, e.g., dates].

• Column B contains [describe data, e.g., product names].

• Column C contains [describe data, e.g., sales amounts].

I need a formula that [explain the expected result, e.g., calculates total sales for a specific product within the last 30 days]. The formula should handle [mention any special conditions, e.g., missing values, dynamic date ranges, case sensitivity].

Please provide the correct formula and a step-by-step breakdown of how it works. If there are alternative approaches, suggest them as well.”*

2️⃣ Excel VBA Macro for Automation

*”I need a VBA macro to automate a repetitive task in Excel. My spreadsheet has the following structure:

• Column A contains [describe data, e.g., employee names].

• Column B contains [describe data, e.g., work hours].

• Column C contains [describe data, e.g., overtime pay].

The macro should perform the following actions:

1. [Describe the first action, e.g., check if overtime is greater than 10 hours].

2. [Describe the second action, e.g., highlight cells in red if overtime exceeds 20 hours].

3. [Describe any additional rules, e.g., generate a summary report in a new sheet].

The macro should be optimized for efficiency and should work on large datasets. Provide the full VBA script along with an explanation of how it functions. If possible, include error handling to prevent unexpected issues.”*

3️⃣ Pivot Table & Data Analysis Assistance

*”I am analyzing data in Excel and need help creating a Pivot Table. My dataset includes:

• Column A: [describe data, e.g., customer names].

• Column B: [describe data, e.g., product categories].

• Column C: [describe data, e.g., total sales].

• Column D: [describe data, e.g., purchase dates].

I need a Pivot Table that shows [describe the required insights, e.g., total sales per product category, grouped by month]. The Pivot Table should:

• Summarize data by [e.g., sum, average, count].

• Apply filters for [e.g., date range, specific products].

• Display data in a clear and readable format.

Please provide step-by-step instructions for creating the Pivot Table, including recommended settings and formatting tips. If there’s a way to enhance analysis using slicers or calculated fields, suggest those as well.”*

4️⃣ Conditional Formatting Rules for Data Visualization

*”I want to apply conditional formatting in Excel to highlight key data points. My dataset includes:

• Column A: [describe data, e.g., student names].

• Column B: [describe data, e.g., test scores].

• Column C: [describe data, e.g., pass/fail status].

I need the following formatting rules:

1. Cells in Column B should turn green if the score is above [threshold, e.g., 80] and red if below [threshold, e.g., 50].

2. Column C should automatically change to ‘Pass’ or ‘Fail’ based on the score.

3. A data bar should be applied in Column B to visually represent scores.

Please provide the exact steps to set up these conditional formatting rules, including any formulas needed. If there’s a more efficient way to achieve this, suggest alternative methods.”*

5️⃣ Power Query for Data Cleaning & Transformation

*”I have a large dataset in Excel that needs cleaning and transformation. The dataset includes:

• Column A: [describe data, e.g., customer IDs].

• Column B: [describe data, e.g., names with inconsistent formatting].

• Column C: [describe data, e.g., sales amounts with missing values].

• Column D: [describe data, e.g., transaction dates in different formats].

I need to use Power Query to:

1. Standardize names in Column B (e.g., make all text Title Case).

2. Fill in missing values in Column C with [e.g., the average or previous value].

3. Convert all dates in Column D to YYYY-MM-DD format.

4. Remove duplicate rows based on customer IDs.

Please provide a step-by-step guide on how to do this using Power Query, including any transformations or M code snippets that can automate these processes.”*

6️⃣ Dynamic Drop-Down Lists with Data Validation

*”I need to create a dynamic drop-down list in Excel using data validation. The list should pull values from [describe the data source, e.g., Column A containing product names].

Requirements:

• The list should automatically update when new data is added.

• It should remove duplicates and only show unique values.

• The drop-down should be dependent on another drop-down (if applicable, e.g., selecting a category first and then filtering products).

Please provide step-by-step instructions for setting this up, including any formulas (e.g., using OFFSET, INDIRECT, UNIQUE, SORT) or named ranges. If VBA is needed, provide the full script and explanation."*

7️⃣ Advanced Filtering Using Excel Formulas

*” I need to filter data dynamically in Excel without using built-in filters. My dataset includes:

• Column A: [describe data, e.g., employee names].

• Column B: [describe data, e.g., department].

• Column C: [describe data, e.g., monthly sales].

I want to:

• Extract only the rows where [describe the condition, e.g., sales exceed $5,000].

• Display the filtered results in a separate table dynamically.

Please provide a formula-based solution using FILTER or INDEX/MATCH, and explain how it works. If VBA is required for a more advanced solution, include the script."*

8️⃣ Automating Data Entry with VBA User Forms

*”I need a VBA-based user form to make data entry easier in Excel. My spreadsheet includes:

• Column A: [describe data, e.g., Employee ID].

• Column B: [describe data, e.g., Name].

• Column C: [describe data, e.g., Department].

• Column D: [describe data, e.g., Salary].

The user form should:

1. Allow users to enter new records.

2. Include validation (e.g., prevent blank fields, restrict numbers in certain fields).

3. Have buttons for ‘Save’, ‘Clear’, and ‘Close’.

4. Automatically save the entered data into the spreadsheet without duplicates.

Please provide the full VBA script and a breakdown of how the user form works.”*

9️⃣ Forecasting Trends Using Excel’s TREND & FORECAST Functions

*”I want to analyze and forecast future trends in Excel. My dataset includes:

• Column A: [describe data, e.g., years].

• Column B: [describe data, e.g., sales revenue].

I need to:

• Predict sales for the next [X] years.

• Use FORECAST or TREND functions to generate future values.

• Display a line chart showing historical and predicted values.

Please provide detailed steps on how to do this and explain which function is better for accuracy. If Power BI or other tools would improve forecasting, suggest them.”*

🔟 Automating Report Generation with VBA

*”I need a VBA macro that automatically generates a summary report in Excel. My dataset includes:

• Column A: [describe data, e.g., product names].

• Column B: [describe data, e.g., sales figures].

• Column C: [describe data, e.g., regions].

The report should:

• Summarize total sales by region.

• Format the results in a new sheet with proper headings.

• Include a dynamically generated chart.

Please provide the full VBA script along with an explanation of each part.”*

1️⃣1️⃣ Extracting and Manipulating Text Using Excel Functions

*”I have a dataset where I need to extract and manipulate text in Excel. My data includes:

• Column A: [describe data, e.g., full names].

• Column B: [describe data, e.g., email addresses].

I need to:

1. Extract first names from Column A.

2. Extract domain names from email addresses in Column B.

3. Count how many times each domain appears in the list.

Please provide formulas using LEFT, RIGHT, MID, SEARCH, or TEXTSPLIT and explain how they work. If Power Query or VBA would make it more efficient, suggest those options."*

1️⃣2️⃣ Real-Time Stock Market Data in Excel Using APIs

*”I need to pull real-time stock market data into Excel. The goal is to:

• Retrieve stock prices for [specific stocks, e.g., AAPL, TSLA].

• Automatically update every [X] minutes.

• Display historical trends in a chart.

Please provide a solution using WEBSERVICE functions, Power Query, or a VBA script that connects to an API. Include setup instructions and error-handling tips."*

1️⃣3️⃣ Creating a Dynamic Gantt Chart for Project Management

*”I want to create a Gantt chart in Excel for tracking project tasks. My data includes:

• Column A: [Task Name].

• Column B: [Start Date].

• Column C: [End Date].

The Gantt chart should:

• Automatically adjust based on start and end dates.

• Use conditional formatting to create colored bars.

• Allow for easy updates when dates change.

Please provide step-by-step instructions along with formulas or VBA if needed.”*

1️⃣4️⃣ Splitting and Merging Cells Dynamically

*”I need to split and merge data dynamically in Excel. My dataset includes:

• Column A: [describe data, e.g., full addresses].

Tasks:

1. Split addresses into separate columns (Street, City, State, ZIP).

2. Merge first and last names into a single column with proper spacing.

Please provide formulas using TEXTSPLIT, CONCATENATE, or Power Query, along with a guide on how to implement them."*

1️⃣5️⃣ Automating Email Sending from Excel Using VBA

*”I need a VBA macro to send automated emails from Excel. My spreadsheet contains:

• Column A: [Recipient Email].

• Column B: [Subject].

• Column C: [Message Body].

The macro should:

• Send emails using Outlook or Gmail.

• Allow attachments (if specified).

• Provide a confirmation once an email is sent.

Please provide the full VBA script and explain the setup process.”*