How to Use ChatGPT Prompts to Write Excel Formulas Easily

ChatGPT and generative artificial intelligence (AI) technology in general have changed the way people search for information. From finding out how many runs Virat Kohli scored in the ongoing calendar year in ODI (275 runs, if you wanted to know) to learning about astronomy and space, typing the right prompt can provide all the information in seconds. Take Excel sheet formulas for example. Those who need to create spreadsheets for complex calculations and filtering large datasets can benefit from asking the AI to write formulas that just work. But what is the right way to ask ChatGPT?

Why Use ChatGPT for Excel?

Excel, or any spreadsheet for that matter, is an easy tool to get started with, but incredibly tricky to master. The learning curve is so steep that even daily users have to occasionally look up syntax. This is because Excel has hundreds of functions and dozens of ways to combine them. This is where ChatGPT comes in.

• Instead of sifting through forums or help pages, you can now just ask ChatGPT directly:
• How do I write a formula to calculate overtime hours if someone works more than 40 hours a week?”
• What formula returns the last non-empty cell in a column?”
• Why is my VLOOKUP returning #N/A?”

ChatGPT can handle these and more, often giving you the exact formula, explaining how it works, and suggesting alternatives if needed.

What to Ask ChatGPT to Write Effective Formulas

Just asking ChatGPT to write a formula for VLOOKUP will not provide you with the right Excel formula for your particular data or needs. The key is giving the chatbot enough context. Here are a few prompt structures that will help you get the correct formula in seconds:

1. Describe the data and your goal.

Example: I have a column of dates in A2:A100 and a column of sales in B2:B100. I want a formula that returns the total sales made in July.

2. Ask for a specific function or logic.

Example: Write an IF formula that checks if a cell is greater than 100. If it is, return ‘High'; otherwise, return ‘Low'.

3, Request help with debugging.

Example: This formula is giving an error: =IF(A2>100, High, Low). What's wrong with it?

Additionally, ChatGPT can also help you choose between similar functions (such as VLOOKUP vs. XLOOKUP) or rewrite formulas to make them more efficient. Here are some specific examples to get the right Excel formulas for your work and projects:

• Calculate working days between two dates: Write a formula to calculate the number of working days between two dates in A1 and B1, excluding weekends and holidays listed in C1:C10.
• Conditional formatting: How do I highlight cells in column B if the value in column A is greater than 100?
• Combine first and last names: Write a formula to combine the first name in A2 and the last name in B2 with a space in between.

When Not to Rely on ChatGPT

While ChatGPT is great for writing and understanding formulas, it's still worth cross-checking its output, especially for critical financial calculations. It can sometimes make syntax mistakes or misinterpret ambiguous instructions. Also, if your spreadsheet involves macros, Power Query, or VBA, the conversation may get more technical. In such cases, ChatGPT can still help, but its answers may need fine-tuning.