Figuring out how to write the ideal Excel formula to accomplish the desired result can be difficult, particularly if you have a large amount of data on a spreadsheet and require a complex formula that goes beyond simple summation -- or if you're Excel-formula-challenged. ChatGPT is now the embodiment of the adage "work smarter, not harder."

How to use ChatGPT to compose code, resumes, essays, and cover letters

ChatGPT and other AI chatbots can assist you in creating formulas for your Excel spreadsheet without requiring registration on a specialized website, like ExcelFormulaBot.

The wonderful thing about using artificial intelligence tools like ChatGPT or Bing Chat to create formulas for Microsoft Excel (and Google Sheets) is that you can request formulas to be as simple or as complex as you'd like -- provided that your instructions are crystal clear.

ALSO READ:How to compose code using ChatGPT

How to utilize ChatGPT to compose Excel formulas

What you need: To use ChatGPT to create an Excel formula, you must have access to Microsoft Excel or Google Sheets, as these formulas are compatible with both programs. Additionally, you will need an OpenAI account to access ChatGPT.

Keep in mind that, regardless of how knowledgeable these AI chatbots may be, they are still not as well-versed in subtleties as humans, and they may make mistakes or misinterpret commands.

1. Launch ChatGPT and the spreadsheet

Launch Excel or Sheets, then navigate to chat.openai.com to access ChatGPT. If you haven't already logged in with your OpenAI account, now is the moment to do so; you can also create an account at this point. You will be taken to a conversation with ChatGPT after logging in.

The spreadsheet below is the example we will use: a fictitious device inventory for January 2023. It contains 45 products in column A, each with an SKU number (column B), quantity in inventory (column C), quantity purchased in January (column D), price per unit (column E), and the subtotal (column F, which already contains a formula) in column F.

Calculate sales tax (column G), seller's commission (column H, not shown), total (column I, not shown), and the remaining quantity in inventory for the following month (column J, not shown) will be created using ChatGPT.

2. Use ChatGPT with clarity

It is now time to outline the concept of what you wish to accomplish with ChatGPT and request the desired formula. To accomplish this, you must explain explicitly to the AI chatbot what the formula should do.

It is crucial that you are extremely clear -- to the point of obfuscation -- because any errors ChatGPT makes due to a misunderstanding can result in inaccurate values in your table. So, channel your inner eleventh-grade student who asked their computer lab instructor how to construct a particular formula.

If we want ChatGPT to generate the remaining formulas, we must provide very clear and precise instructions. Therefore, we will instruct the AI chatbot to "write an Excel formula to calculate the sales tax of the monetary value in column F, at a 6% rate."

3. Copy the formula and paste it into Excel.

Copy the formula from ChatGPT and paste it into the Excel cell that will contain the results, making any necessary modifications. Before populating the remaining cells, verify that the results are accurate to the best of your ability.

In the example provided below, I updated the formula to reflect the correct cell, F3, that must be included in the sales tax calculation.

ALSO READ:How to use ChatGPT to build an application

4. Finish incorporating the remaining formulas.

I'll complete adding the necessary formulas to my spreadsheet, starting with the commission calculation, by requesting ChatGPT to "Create a formula to calculate 10% commission on the monetary total in cell F3."

5. Fill in the blanks in the table

As soon as I add my commission formula, I can continue on to the next two columns to calculate the total and the remaining inventory to finish the table.

6. Fill in the remaining cells of your spreadsheet

It is now time to populate the remaining cells of your spreadsheet with your brand-new formulas. To accomplish this, position the cursor in the lower-right corner of the cell containing the formula, then click and drag the cursor over the other cells where you want the formula to appear.

The same formatting will be used, including formula progression.

FAQs

Excel: Can AI be incorporated into formulas?

Idealistically, you would be able to generate formulas in Microsoft Excel as simply as you can with ChatGPT, thanks to an AI tool that is already integrated into the program. Copilot is a set of AI tools that Microsoft is rolling out for many of its products, including the Microsoft 365 suite of programs. This is not the case at this juncture.

Even though Microsoft Copilot is not yet widely available, one can expect that the new features will include a GPT-4-integrated version of Microsoft Excel.

Why isn't the ChatGPT formula working?

If ChatGPT provides you with a formula that does not function in Excel or Sheets, it may have misinterpreted your prompt. Examine the formula to determine where the error may have occurred on ChatGPT's end and determine how you can modify your question to obtain the correct response. "Create a formula for the total cost that sums the values in columns F, G, and H" was the instruction in the example below.

Since I did not specify that I needed the formula to sum the values in cells F3, G3, and H3, the formula sums the entire column range, which is not what we need.

Then, I modified my prompt to instruct ChatGPT to "Create a formula for the total cost that adds the values in cells F3, G3, and H3."

ChatGPT is capable of creating complex Excel formulas.

ChatGPT is the ideal resource for writing formulas for Excel or Google Sheets, regardless of their complexity. In this example, we used basic formulas to demonstrate the process, but you can ask the AI chatbot to generate more complex formulas to test its capabilities.

Keep in mind that the veracity of ChatGPT's results relies heavily on the clarity of your prompts.

ALSO READ:How To Earn $1,000 Per Month Using AI and ChatGPT.

Can you write Excel formulas using Bing Chat?

By following the steps outlined above, other AI chatbots, such as Bing Chat and Google Bard, are also able to generate Excel formulas on your behalf.