x

【提示工程】适用于每个Excel用户的权威ChatGPT提示指南（公式、VBA及其他！）

developer.chat
13 September 2025
SEO Title
The Definitive ChatGPT Prompt Guide for Every Excel User (Formulas, VBA & Beyond!)

category

Microsoft Excel is a powerhouse, but sometimes finding the right formula, debugging a macro, figuring out the best way to analyze your data, or even recalling how to use a specific feature can be time-consuming. ChatGPT can be an invaluable assistant, helping you generate formulas, write VBA code, understand concepts, and troubleshoot issues.

This guide provides a curated list of effective prompts, using placeholders like [your specific need], [cell range], [criteria], etc. Simply replace these placeholders with your actual requirements to get tailored responses from ChatGPT.

Key to Using Placeholders:

  • [Your Specific Need/Goal/Task]: Describe what you want to achieve.
  • [Cell Range, e.g., A1:B10]: Specify the cells or columns involved.
  • [Sheet Name]: The name of the relevant worksheet.
  • [Criteria/Condition]: The logical test or condition (e.g., “greater than 100”, “contains ‘Sales'”).
  • [Desired Output/Format]: What you expect (e.g., “a formula”, “VBA code”, “step-by-step instructions”).
  • [Data Description]: Briefly describe the structure of your data (e.g., “Column A has dates, Column B has sales figures”).
  • [Function_Name]: The name of the Excel function you’re asking about.
  • [Placeholder for specific text, number, option, etc.]: Replace with your specific details.

 

I. Formulas & Functions

Ask ChatGPT to generate, explain, or debug Excel formulas.

  1. Generate a Basic Calculation Formula:
    1. Write an Excel formula to calculate [desired calculation, e.g., the sum, average, count, max, min] of the values in [cell range, e.g., C2:C100] on sheet [Sheet Name].
    2. Write an Excel formula to find the [Nth, e.g., 3rd] [largest/smallest] value in [cell range, e.g., D1:D50].
  2. Generate a Conditional Formula (IF, IFS):
    1. Create an Excel IF formula: if the value in cell [cell reference, e.g., A1] is [condition, e.g., greater than 10], then return [value if true, e.g., "High"], otherwise return [value if false, e.g., "Low"].
    2. I need an Excel IFS formula. If [cell A1] is [condition 1], return [result 1]; if [cell A1] is [condition 2], return [result 2]; otherwise, return [default result].
  3. Generate Conditional Aggregation (SUMIF, COUNTIF, AVERAGEIF, SUMIFS, etc.):
    1. Write an Excel [SUMIF/COUNTIF/AVERAGEIF] formula to [sum/count/average] values in [range to aggregate, e.g., B2:B100] where the corresponding cells in [criteria range, e.g., A2:A100] meet the criteria [your criteria, e.g., "Apples" or >50].
    2. Generate an Excel [SUMIFS/COUNTIFS/AVERAGEIFS] formula. [Action: Sum/Count/Average] values in [range to aggregate, e.g., C1:C100] IF [criteria range 1, e.g., A1:A100] is [criteria 1, e.g., "North"] AND [criteria range 2, e.g., B1:B100] is [criteria 2, e.g., ">200"].
    3. Write an Excel formula to count the number of unique values in [column or cell range, e.g., A1:A200].
    4. How do I write an Excel formula to sum every [Nth, e.g., 3rd] row in [column or cell range, e.g., B1:B100]?
  4. Generate Lookup Formulas (VLOOKUP, HLOOKUP, XLOOKUP, INDEX/MATCH):
    1. Write an Excel XLOOKUP formula to find the value [lookup value, e.g., in cell A1 or a specific text/number] within [lookup array/column, e.g., Sheet2!A:A], and return the corresponding value from [return array/column, e.g., Sheet2!B:B]. If not found, return [value if not found, e.g., "Not Found"].
    2. Provide an INDEX/MATCH formula to look up [lookup value from cell, e.g., E1] in [lookup column, e.g., A:A] on sheet [Sheet Name] and return the corresponding value from [return column, e.g., C:C].
  5. Generate Text Manipulation Formulas:
    1. I need an Excel formula to [desired text manipulation, e.g., combine text from cell A1 and B1 with a space, extract the first 5 characters from cell C1, find the position of "XYZ" in cell D1, convert text in E1 to uppercase].
    2. Write an Excel formula to extract [part of text to extract, e.g., the text between the first and second hyphen] from cell [cell reference, e.g., A1] which contains text like [example text structure, e.g., "ABC-123-XYZ"].
  6. Generate Date & Time Formulas:
    Write an Excel formula to [date/time calculation, e.g., calculate the number of days between date in A1 and date in B1, add 30 days to the date in C1, extract the month from date in D1, get the current date and time].
  7. Explain or Utilize Specific Advanced Formulas:
    1. Explain how to use the Excel SUMPRODUCT function for [specific scenario, e.g., calculating a weighted average for scores in column A with weights in column B].
    2. Show me how to use the Excel OFFSET function with [another function, e.g., SUM or AVERAGE] to [specific goal, e.g., calculate a sum of a dynamic range that expands based on a value in cell C1].
  8. Explain a Formula:
    Explain what this Excel formula does and how it works: [paste your Excel formula here].
  9. Debug a Formula:
    My Excel formula [paste your formula here] in cell [cell reference] is returning [error message, e.g., #N/A, #VALUE!] (or [unexpected result]). I am trying to [explain the goal of your formula, e.g., "calculate the total sales for 'Product X'"]. The data it references is [briefly describe data structure/example]. Can you help me find the error?

 

II. VBA & Macros

Ask ChatGPT to automate tasks, create custom functions, and manage Excel objects using VBA.

  1. Generate a Basic VBA Macro:
    Write an Excel VBA macro that [specific action, e.g., copies data from range A1:B10 on Sheet1 to range C1:D10 on Sheet2, clears the contents of range A1:Z100 on the active sheet, sorts data in column A ascending].
  2. Generate a Loop-Based VBA Macro:
    Create a VBA macro that loops through all cells in column [Column Letter, e.g., A] on sheet [Sheet Name] from row [Start Row] to [End Row]. If the cell value [condition, e.g., is greater than 100 / contains "Urgent"], then [action, e.g., change the cell's background color to yellow / copy the entire row to Sheet2].
  3. Generate Event-Driven VBA Macro:
    Create a VBA macro to automatically [action to perform, e.g., sort column A ascending, log the change to another sheet] when [trigger event, e.g., a new value is entered in column B of Sheet1, the workbook opens, a specific cell is double-clicked].
  4. Generate a VBA Macro for File/Sheet Operations:
    Write a VBA macro to [file/sheet operation, e.g., create a new workbook and save it as "report.xlsx" in "C:\MyFiles\", loop through all worksheets and protect them with password "123", import data from a CSV file named "data.csv" into a new sheet].
  5. Create a VBA User Defined Function (UDF) or Task-Specific Subroutine:
    1. Create a VBA User Defined Function (UDF) named [FunctionName] that takes [number] arguments: [Arg1Name] (as [Arg1Type, e.g., Range, String, Double]) and [Arg2Name] (as [Arg2Type]). The function should [describe calculation/logic] and return [description of return value and type, e.g., the sum as a Double].
    2. Write VBA code (a Sub or Function) to extract unique values from [input cell range, e.g., Sheet1!A1:A100] and output them starting in [output cell, e.g., Sheet2!B1].
  6. Modify an Existing VBA Macro:
    I have this VBA macro: [paste your VBA code snippet]. I need to modify it to [describe the desired change, e.g., also process column B, handle errors by displaying a message box, run on all open workbooks instead of just the active one].
  7. Explain VBA Code:
    Explain what this VBA code does: [paste your VBA code snippet].
  8. Troubleshoot VBA Code:
    My VBA macro [Macro Name or paste relevant code snippet] is giving me a [Error Message, e.g., "Run-time error '9': Subscript out of range"] or [describe unexpected behavior]. What could be wrong and how can I fix it?

 

III. Data Analysis & Manipulation

Ask ChatGPT to help you sort, filter, summarize, and transform your data.

  1. Sort Data:
    Provide steps (or VBA code) to sort data in [cell range, e.g., A1:C100] on sheet [Sheet Name] based on [column to sort by, e.g., Column B] in [ascending/descending] order. [Optional: Add secondary sort level, e.g., Then by Column A ascending].
  2. Filter Data:
    1. How do I filter data in [cell range, e.g., A:D] on sheet [Sheet Name] to show only rows where column [Column Letter/Name, e.g., C] [condition, e.g., equals "Completed" / is greater than 500 / contains "Project X"]?
    2. Generate VBA code to apply an autofilter to [cell range, e.g., A1:E100] on sheet [Sheet Name], filtering column [Column Index or Letter, e.g., 3 or C] for values [criteria, e.g., "Pending"].
  3. Create Pivot Tables:
    1. Explain how to create a Pivot Table in Excel to summarize [data to summarize, e.g., total sales] by [row field, e.g., Region] and [column field, e.g., Product Category] using data in range [data range, e.g., A1:D500] on sheet [Sheet Name]. Show [values field, e.g., Sum of Sales] in the values area.
    2. Generate VBA code to create a Pivot Table on a new sheet using data from [SourceDataRange, e.g., Sheet1!A1:G1000]. Set [RowFieldName] as a row field, [ColumnFieldName] as a column field, and [DataFieldName] as a data field using [SummarizationFunction, e.g., Sum/Average/Count].
  4. Data Cleaning & Transformation:
    1. How can I remove duplicate rows in Excel based on [column(s) to check for duplicates, e.g., all columns / column A and B] in the range [cell range]?
    2. Suggest a formula or method to [data cleaning task, e.g., remove leading/trailing spaces from text in column A, split text in column B (e.g., "First Last") into two columns based on delimiter " "].
    3. Explain how to transpose data in Excel (swap rows and columns) for the range [data range, e.g., A1:D5].
  5. Data Input Control (Data Validation):
    How do I create a drop-down list in cell [cell reference, e.g., B1] on sheet [Sheet Name] with the following options: [option1, option2, option3, ...]?
  6. Statistical Analysis:
    1. How can I calculate [statistical measure, e.g., standard deviation, variance, correlation between column A and B, percentile] for data in [cell range]?
    2. How can I calculate a moving average for the last [N, e.g., 7] periods for data in [cell range containing time series data, e.g., B2:B100]?

 

IV. Formatting, Presentation & UI Actions

Ask ChatGPT to help improve visual appearance, readability, and perform common UI tasks.

  1. Apply Conditional Formatting:
    1. Provide an Excel conditional formatting rule to highlight cells in [cell range, e.g., C2:C100] that [condition, e.g., are greater than 100 / contain the text "Error" / are duplicate values / are blank] with [formatting style, e.g., a red fill and bold text / green font color].
    2. Write VBA code to apply conditional formatting to [cell range] where cells with values [condition] are formatted with [specific formatting, e.g., bold font and yellow background].
  2. Format Numbers/Dates:
    How do I format numbers in [cell range] to [desired format, e.g., show as currency with 2 decimal places, display as percentages, show dates as "dd-mmm-yyyy"]?
  3. Chart Creation:
    1. Suggest the best chart type to visualize [type of data and relationship, e.g., sales trends over time for different products, comparison of market share between competitors, distribution of scores] using data in [data range for X-axis/categories] and [data range for Y-axis/values].
    2. Provide steps (or VBA code) to create a [chart type, e.g., line chart, bar chart, pie chart] using data where [X-axis/Category labels are in column A] and [Y-axis/Values are in column B] from row [start row] to [end row] on sheet [Sheet Name].
  4. Common UI Actions:
    Show me how to [UI action, e.g., freeze panes to keep rows 1 and 2 visible, protect sheet 'Report' with a password, unhide column G] in Excel.

 

V. Troubleshooting & Error Handling (General)

Get help with common Excel problems.

  1. Understand Error Messages:
    What does the Excel error [error message, e.g., #DIV/0!, #REF!, #NAME?] mean? If it’s in my formula [paste your formula here] located in cell [cell reference], what are common causes in that context?
  2. General Troubleshooting:
    1. My Excel sheet is [describe problem, e.g., very slow to calculate, not updating formulas automatically, a specific feature is greyed out]. What are some common reasons and solutions?
    2. I’m trying to [describe your task] in Excel, but [describe what's going wrong]. Can you suggest what I might be doing incorrectly or a different approach?

 

VI. Data Import/Export & Management

Work with external data and manage your Excel files.

  1. Import Data:
    Provide steps (or VBA code) to import data from a [file type, e.g., CSV file, TXT file] named [filename.ext] located at [File Path] into sheet [Sheet Name] starting at cell [Start Cell, e.g., A1]. [Optional: Specify delimiter, e.g., The CSV is comma-delimited / The text file is tab-delimited].
  2. Export Data:
    1. How can I export the data in [cell range, e.g., A1:G500] on sheet [Sheet Name] to a [file type, e.g., new CSV file, existing text file] named [filename.ext]?
    2. Generate VBA code to export sheet [Sheet Name] as a [PDF file / new Excel workbook] named [filename] to folder [Folder Path].

 

VII. Learning & Understanding Excel Concepts

Deepen your knowledge of Excel features and best practices.

  1. Explain a Feature/Tool:
    1. Explain the Excel feature [feature name, e.g., Power Query, Data Validation, Tables, Slicers, Goal Seek, Solver] and provide a simple use case, particularly for [type of task or problem you're interested in, if any].
    2. Explain the Excel function [Function_Name] and provide an example of its use for [specific type of calculation or task].
  2. Compare Features/Functions:
    What is the difference between [Excel Concept 1, e.g., an Excel Table and a regular range] and [Excel Concept 2, e.g., using SUM and SUBTOTAL]? When should I use each?
  3. Best Practices:
    What are the best practices for [specific Excel task, e.g., organizing large datasets, building financial models, creating dashboards, writing maintainable VBA code] in Excel?

 

Tips for Writing Effective Prompts for ChatGPT:
  • Be Specific and Clear: The more detail you provide, the better ChatGPT can understand your request.
  • Provide Context: Mention your data structure, the sheet names, cell ranges, and your ultimate goal.
  • Specify the Desired Output: Do you want a formula, VBA code, step-by-step instructions, or an explanation?
  • Iterate and Refine: If the first response isn’t perfect, rephrase your prompt, add more details, or ask for clarification on a specific part of the response.
  • Break Down Complex Tasks: For very complex problems, ask ChatGPT to help with smaller, manageable parts first.
  • Mention Your Excel Version (if relevant): Some features or functions might differ slightly between Excel versions (e.g., “I’m using Excel for Microsoft 365” or “Excel 2016”).

 

Also, Check These Prompts:

By using these targeted prompts, you can leverage ChatGPT’s capabilities to become more efficient and proficient in Excel. Happy prompting!
 

标签

↑