From our experiments creating prompts for Excel spreadsheets , here is a checklist for you.

1. Supply very detailed context of both the data and the problem.

   Present information clearly , logically in a step by step manner. In particular, supply a  clear description of any datasets:  Also specifying the type and structure of the data (e.g.,  list, table, number formats, types of data like text, numbers, dates)

2. Breakdown of Requirements:

   Instead of a block of text, list the requirements step by step for better clarity. Prioritise the order of operations if the task has multiple steps.

3. Use directional nudging.

   ChatGPT often needs to be nudged in the correct direction, often many times.

4. Paste in a copy of your headers and at least 3 rows of your data at end of prompt with delimiters like “”” data “”
5. We have found that ChatGPT often ignores relative and absolute referencing and does not always recognize Excel tables .So we always include, when it required in the solution , the text: ‘ Please allow for proper application of relative and absolute references when copying formulas’. And if using tables ,  ‘I want a solution that leverages Excel structured references since these are Excel Tables’

6. If possible give the prompt an example or examples to follow  as in few shot prompting , which is very effective for excel type prompts.

7. Emphasize and repeat the verbal action you want and we have found it best to start the sentence with the verb. Compare the lists, Count the items, Extract, Calculate etc. They  not only sets direction but avoids ambiguity.

8. If you want the prompt to use certain functions , let the prompt know. The more directions it gets, the better. This helps it overcome one of its biggest weaknesses, not supplying  the most efficient optimal solution.

9. Don’t be surprised that you get slightly different answers from using the same prompt. Remember ChatGPT is large language model. This means, basically , it is just matching patterns, it does not understand the meaning of what you are doing.

10. Also remember AI models like ChatGPT are not search engines – they’re designed for dialogue. If an initial prompt doesn’t yield the expected result, refine the prompt.
    Evaluate the clarity of your instructions. Check it against our template and this checklist. Has the model had enough time to “reason ”. Try to  identify any potentially misleading elements in the prompt. The key to success is to iteratively refine your prompt, improving its effectiveness with each iteration to best suit your task.

11. Ask for Testing Instructions

       Once a solution is provided, ask for instructions on how to test or verify its effectiveness. For example: “How can I test if this SUMIFS formula works correctly with different inputs?”

12. Highlight Potential Limitations or Ambiguities

     If the task involves ambiguous data (e.g., inconsistent text, missing values), ask ChatGPT to account for this. Example: “Please account for inconsistent text in the data before applying formulas.”

13. Clarify Edge Cases

      It’s important to prompt ChatGPT to consider edge cases like empty cells, zero values, or invalid data entries. For instance: “Include a solution that handles empty cells or invalid inputs gracefully without causing errors in the spreadsheet.”