To Watch a Video, explaining the ChatGPT Prompt Template for Spreadsheets: Click Here.

1.General Problem Overview.

 

• Task Description:

  Clearly describe the overall task or problem you’re encountering in Excel. Be specific about the action or result you’re trying to achieve.

           Example:

I need to create a formula that calculates bonuses based on monthly sales, with different percentage bonuses applied to different sales ranges.

 

• Goal Clarification (Directional Prompting):

  Provide the ultimate outcome or direction for the solution. This sets a clear objective and focuses the solution in the right direction.

          Example: I want to know which formula can calculate bonuses using a tiered system: 5% for sales up to $1,000, 10% for sales between $1,000 and $5,000, and 15% for sales above $5,000.

 

 2.Dataset Context and Structure.

 

• Dataset Description:

  Give a  detailed description of your dataset(s), including list or Excel Table,  column headers, number of rows, data types, and any relevant cell ranges or Names.

          Example: My dataset contains three columns: “Employee Name” (A), “Monthly Sales” (B), and “Sales Date” (C). Data runs from row 2 to row 100, with total sales amounts in column B.

 

• Conditions and Constraints:

  Mention any specific rules or conditions that apply to your data, such as specific ranges to filter, ignored data, or conditions (this can help Chain-of-Thought problem-solving).

         Example: Sales less than $500 should be excluded from the bonus calculation. Only calculate bonuses for sales recorded in 2023.

 

3. [ Advanced Prompting Techniques:]

 

       3a. [Few-Shot Examples for Guidance]

• Provide a few examples of how the problem should be solved in different situations to guide the prompt towards a more effective  solution . Few-shot prompting helps refine the type of solution generated.

     Example (for a tiered bonus system):

• 1. If “Monthly Sales” = $500, bonus should be 5% of $500.
  2. If “Monthly Sales” = $2,000, bonus should be 10% of $2,000.
  3. If “Monthly Sales” = $10,000, bonus should be 15% of $10,000.

       

       3B. [Chain-of-Thought (CoT) Reasoning]

 

• Ask for step-by-step reasoning in the solution to make sure the logic is broken down clearly and correctly. This is helpful for complex problems like nested formulas, data analysis, or macros.

       Example:

Can you explain the steps for creating a formula that calculates the total commission based on the sales range, starting with how to identify which range the sales fall into?

 

            3c.[Tree-of-Thought (ToT) Exploration]

• Ask for multiple approaches or alternative paths to solve the problem to ensure robustness. This technique explores multiple ways to approach a problem to increase the likelihood of finding the best solution.

    Example: Could you explore different approaches to calculate bonuses?

• • Option 1: Use a single formula for all calculations.
  • Option 2: Use a combination of helper columns to break down the steps.
  • Option 3: Automate the calculation with VBA.

 

 4. Specific Excel Tools, Functions, or Features.

 

• Excel Tools Involved:

  Mention any specific Excel functions or features you think may be involved (e.g., VLOOKUP, INDEX/MATCH, Power Query, VBA).

     Example:

I believe the IF() function or possibly a nested IF with AND() could be useful, but I’m unsure how to set up the tiering system. Alternatively, a SUMIFS might be required.

 

• Formula or VBA vs. Power Query:

  Clarify the preferred method if you have one (e.g., if you prefer using formulas, VBA macros, or Power Query).

      Example:

I would like a formula-based solution, but I’m open to VBA if it simplifies things.

 

 5.Expected Outcome (Precision and Directional Prompting)

 

• Desired Output Format:

  Clearly specify the format or structure of the desired outcome. Precision and direction help in guiding the response towards a solution that fits your exact need.

       Example:

The result should be in a new column (D), with the bonus amount calculated for each employee. If there’s no bonus, the cell should display $0.

 

• Check for Accuracy (Directional Prompting):

  Ask for checks or validations in the solution, like testing on sample data to ensure it’s correct.

          Example:

Please make sure that the solution works with sales amounts from $0 to $10,000, and test it on sample data to verify.

 

 6.Sample Data and Error Details.

 

• Sample Data (Optional):

  Providing a small sample dataset can clarify your problem. You can type out a few rows or reference cells that contain data.

        Example:
| Employee Name | Monthly Sales | Sales Date |
|—————|—————|————|
| Alice | $1,200 | 01/02/2023 |
| Bob | $2,500 | 05/12/2023 |
| Charlie | $7,500 | 08/23/2023 |

 

• Error or Issue Faced:

  If there’s an error message or an unexpected result, include it to provide context (for CoT-style solutions).

        Example:

When using the formula =IF(B2>500, B2*0.05), it calculates the bonus for everyone, even those who have sales below $500.

 

7.Version and Platform Information.

 

• Excel Version and Platform:

  Mention the Excel version and platform (e.g., Excel 365, Excel 2019, Windows, Mac, or Web). Advanced features like dynamic arrays, XLOOKUP, or Power Query may depend on this.

        Example: I’m using Excel 365 on Windows.

 

 8. Directional Nudging For Spreadsheets:

 

          Add these Nudging statements when the situation calls for it.

• We have found that ChatGPT often ignores relative and absolute referencing. So , include the text “Please allow for proper application of relative and absolute references when copying formulas’

• Also Excel tables structures can be overlooked , so ” I want a solution that leverages Excel structured references since these are Excel Tables’   

• And often , this statement is needed.  ‘Please provide a formula using standard cell references (not table references)’.

 

 

 9. [Explore Alternatives (ToT)]

 

• Are there any alternative approaches you’d like to explore?

  Exploring alternative solutions can lead to more efficient or creative approaches. You can ask for a Tree-of-Thought exploration of potential methods.

         Example:

Is there a way to automate this bonus calculation through VBA to avoid manual updating in the future? Also, is Power Query a better fit for cleaning and analyzing this data?

 

 

Common Pitfalls to Avoid (Advanced Level):

1. Over-reliance on specific functions: Request alternative methods if your preferred function isn’t ideal (e.g., “Is INDEX/MATCH better than VLOOKUP here?”).

2. Unclear goal setting: 
   Always ensure you articulate the expected outcome clearly (e.g., avoid vague requests like “help with a formula”).

3. Ignoring version limitations:
   Be mindful of which Excel features are available in your version. Ask for alternatives if your version lacks certain functions.

4. Not considering dynamic or future data changes:
   If your data set or logic will change over time, ask for flexible, dynamic solutions.

 

 

Advanced Prompt Example For Excel Spreadsheets :

Problem: I need to calculate a tiered bonus structure based on monthly sales for each employee.
Dataset: Column A contains “Employee Name,” Column B has “Monthly Sales,” and Column C has the “Sales Date.”
Goal: I need to calculate the bonus:

• 5% for sales up to $1,000
• 10% for sales between $1,000 and $5,000
• 15% for sales over $5,000

The bonus should only be calculated for sales in 2023, and sales under $500 should be excluded.
Tools: I believe an IF or nested IF function could work, but I’m unsure how to handle multiple conditions.
Expected Outcome: The result should appear in column D as the bonus amount, or $0 if no bonus applies.
Excel Version: I’m using Excel 365 on Windows.
Request: Could you use Chain-of-Thought reasoning to explain the steps and suggest alternative approaches using Tree-of-Thought for more efficient options?