ChatGPT Prompt Template for Building Spreadsheets.

You can download a PDF version of the full template from Here

You can download a Microsoft Word version of the full template from Here.

The goal of the ‘ ChatGPT prompt template for building spreadsheets  ‘  is to help users frame their Excel issues clearly and provide all necessary context so that solutions from AI  are as accurate and effective as possible. But always, always  check solutions carefully . Plug the suggested formulas into your spreadsheet and test them comprehensively.

In this  template  items in square brackets are optional. [Optional ]

When starting off your prompt give the prompt its role, maybe  ‘You are an “Excel Expert “ or a “data analyst expert”.

 

Then start by clearly describing the  overall task or problem you want it to solve , be specific and also include a clear end objective for your prompt. It’s crucial to provide a detailed description of your dataset in your prompt.

Include the following information: column headers, number of rows, data types (numeric, text, or date), and cell ranges. Specify whether the data is organized as a list or an Excel table. Lastly, mention any specific rules or conditions that the prompt should be aware of.

 

For complex problems, you may benefit from advanced ChatGPT prompting techniques.

These methods are optional but can greatly enhance the quality of your results. Here’s a quick recap:

  • Few-Shot Prompting

    Providing a few examples of how to solve similar problems in different scenarios can help guide the prompt toward a better solution. This technique refines the output by setting clear expectations.

  • Chain-of-Thought Reasoning

    Request step-by-step reasoning in the response to ensure the logic is clearly and accurately broken down. This is especially useful for complex tasks like nested formulas, data analysis, or macros.

  • Tree-of-Thought Exploration

    Ask the prompt to explore multiple approaches or alternative methods for solving the problem. This increases the likelihood of finding the best solution by considering different strategies.

 

The next step is to suggest specific Excel functions or features that could  be used in the solution. This guidance can significantly improve the AI’s output. For instance, if you’re comparing lists, specifying functions like VLOOKUP, MATCH, or XLOOKUP will help steer ChatGPT toward a more accurate solution.

Another crucial part of the prompt is specifying the Expected Outcome .Clearly define the desired format or structure of the output. Being precise about what you want ensures the response aligns with your exact requirements.

 

Getting ChatGPT to check its own solutions is often overlooked but I can be very useful, especially for spreadsheets. Request checks or validations in the solution, such as testing with sample data, to confirm accuracy.

For example, please check that  the solution works with sales amounts ranging from $0 to $10,000, and validate it on sample data to ensure its correct.

Providing a small sample dataset can help ChatGPT in clarifying the  problem. Give it the heading and a few rows of dummy  data. Don’t forget to surround them with delimiters.

Always mention what Excel Version and Platform you are using. This can be very important. For example , if you’re using Excel 365,  you have a lot more functions or options available to you like XLOOKUPS, dynamic functions etcetera.

 

After creating your prompt keep in mind the following most common pitfalls.

Try to avoid them

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

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

    • Ignoring version limitations:

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

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

 

 

Finally, we end off this lesson, with an example of a Prompt.

 

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?


0 Shares