Microsoft Excel is a great tool for anyone who wants to track business inventory or budget for their household. But the formulas, however, are undoubtedly the trickiest part of using the program…
Figuring out how to write these formulas to get the desired result can be tricky, especially when you have a lot of data on a spreadsheet and these formulas are not limited to just a sum. This is how ChatGPT was born.
ChatGPT and other AI chatbots can easily help you create Excel formulas for free and without registering on a specialized website, like ExcelFormulaBot. The advantage of using an AI like ChatGPT or Bing Chat to create formulas for Microsoft Excel (and Google Sheets) is that you can request formulas as simple or as complicated as you want, as long as your instructions are crystal clear.
Screenshot by Maria Diaz/ZDNET.
How ChatGPT can write Excel formulas
What you need: Using ChatGPT to write Excel formulas requires access to Microsoft Excel or Google Sheets, as you can use formulas for both of these apps. You will also need an OpenAI account to access ChatGPT.
Keep in mind that as “smart” as AI chatbots are, they don’t pick up on a person’s nuances and can make mistakes or misinterpret messages.
1. Open ChatGPT and your spreadsheet
Open your Excel spreadsheet or Sheets and go to chat.openai.com to access ChatGPT. If not yet, create an OpenAI account. After login, you will be taken to a chat with ChatGPT.
The spreadsheet below is the example we’ll be using: a device inventory for January 2023. There are 45 products in column A, each with a SKU number (equivalent SKU, Stock Keeping Unit, in column B), the quantity in stock (column C), the quantity purchased in January (column D), the price for each unit (column E), and the subtotal (column F, with already a formula).
We’ll use ChatGPT to create some simple sample formulas, including calculating Sales Tax (column G), Seller’s Commission (column H, not shown), Total (column I, not shown), and Remaining Quantity in stock for the following month (column J, not shown).
Screenshot by Maria Diaz/ZDNET.
2. Be very clear with ChatGPT
Now is the time to express what you are looking to achieve with ChatGPT and ask for the formula you want. To do this, you must explicitly explain to the AI chatbot what you expect from the formula.
It is important that you are very clear, because any mistake made by ChatGPT as a result of a misunderstanding can lead to inaccurate values in your table. Channel your inner high school kid and ask your computer science teacher how to create a specific formula!
Again, if we want ChatGPT to create the rest of the formulas, we need to explain it very clearly and precisely. So we’ll ask the AI chatbot to “write an Excel formula to calculate sales tax from the monetary value in column F, at a rate of 6%”.
I didn’t specify which row of column F we wanted to start the formula with, so ChatGPT defaulted to F2. Screenshot by Maria Diaz/ZDNET.
3. Copy and paste the formula into Excel
Copy the formula from ChatGPT and paste it into the Excel cell you want to populate with the results, making any necessary changes. If possible, confirm that the results are correct before filling in the other cells.
In the example below, I’ve corrected the formula to reflect the correct cell, F3, that should be included in the sales tax calculation.
You can see ChatGPT’s formula highlighted on the left, with the result in the cell to the right. Screenshot by Maria Diaz/ZDNET.
4. Finish adding the rest of your formulas
I’m going to finish adding the formulas I need to my spreadsheet, starting with the commission calculation, asking ChatGPT to “Create a formula to calculate a 10% commission on the monetary total in the cell F3”.
Screenshot by Maria Diaz/ZDNET.
5. Complete the table
Once I’ve added the commission formula, I can move on to the next two columns to calculate the total and remaining stock to complete the table.
Screenshot by Maria Diaz/ZDNET.
6. Complete the rest of your spreadsheet
It’s time to fill out the rest of your spreadsheet with your new formulas. To do this, place your cursor in the lower right corner of the cell containing the formula, then click and drag your cursor over the rest of the cells where you want to insert the formula.
The same calculation will be reproduced, including in subsequent changes to the formulas.
Once you add the formulas to the rest of the column, the spreadsheet will be complete. Screenshot by Maria Diaz/ZDNET.
FAQs
Why is the ChatGPT formula not working?
If ChatGPT gave you a formula that doesn’t work in Excel or Sheets, it may have misunderstood your request. Examine the formula to see where the error lies on the ChatGPT side and see how you can rephrase your request to get the answer you need. In the example below, my message was “Create a formula for total cost that sums the values of columns F, G, and H”.
Screenshot by Maria Diaz/ZDNET.
Since I didn’t specify that the formula should add values from cells F3, G3, and H3, the formula adds values from the entire column range, which is not what we need.
So I corrected my (prompt) prompt to tell ChatGPT to “Create a formula for the total cost that adds the values in cells F3, G3, and H3”.
Can ChatGPT write complex Excel formulas?
ChatGPT is the perfect resource for writing formulas for Excel or Google Sheets, whether simple or complex. We used simple formulas for this example to walk you through the process, but you can ask the AI chatbot to write more complicated formulas and test its limits. Remember that the accuracy of ChatGPT results largely depends on the clarity of your instructions.
Can you use Bing Chat to write Excel formulas?
Other AI chatbots, like Bing Chat and Google Bard, are also able to create Excel formulas for you by following the steps above.
How Bing Chat and Google Bard responded to my invitation to ChatGPT to write a formula that calculates sales tax. Screenshot by Maria Diaz/ZDNET.
Source: ZDNet.com