How to use ChatGPT to write Excel formulas? [Graphic tutorial]

Have you ever been unable to find the right Microsoft Excel Are you troubled by formulas? Now,ChatGPT It can help you solve various formula problems in Excel tables quickly and for free.

Use AI tools like ChatGPT or Copilot, and as long as you can clearly tell it what you want, it can help you easily solve Microsoft Excel formulas.

How to write Excel formulas using ChatGPT

Writing Excel formulas with ChatGPT requires access to Microsoft Excel or WPS Office, as you can use these formulas in both applications. Of course, you also need an OpenAI account to access ChatGPT.

If you don't have a ChatGPT account yet, you can register one for free or send me a private message. To register, please see the tutorial: 2024 ChatGPT detailed registration tutorial, super full graphic version of the operation process!

However, as smart as these AI chatbots are, they can make mistakes or misunderstand what you mean.

Below, Situwencheng uses a product inventory spreadsheet as an example to introduce in detail how to use ChatGPT to write Excel formulas.

1. Open ChatGPT and the spreadsheet

Open the Excel file or WPS spreadsheet, then go to https://chat.openai.com to access ChatGPT. After logging in with your account and password, you can start talking to ChatGPT.

Here, I have prepared a fictitious product inventory Excel table.

There are 15 products in column A, each with a SKU number (column B), quantity in stock (column C), quantity purchased in January (column D), price (column E), and subtotal (column F, which already contains the formula).

Now I’ll create a few simple formula examples using ChatGPT, including calculations for sales tax (column G), seller commission (column H), totals (column I), and remaining inventory (column J).

2. Tell ChatGPT clearly what you need

Now it's time to tell ChatGPT the formula you need. You have to tell it very clearly what you want.

If you don't make it clear enough, any errors made by ChatGPT due to misunderstanding will result in inaccurate values in the table.

For example, I want to use ChatGPT to create the sales tax formula in the example. In this case, I have to be very specific and tell it, for example, "Please write an Excel formula to calculate the sales tax on the sales in column G at the tax rate of 6%."

I didn't specify which row in column G I wanted the formula to start in, so ChatGPT automatically used G2.

3. Copy and paste the formula into Excel

Copy the formula from ChatGPT and paste it into the Excel cell where you want to fill in the result, and edit it if necessary. Please check carefully whether the result is correct before filling in other cells.

In the example below, I have adjusted the formula to correctly use the data in cell G3 to calculate the sales tax.

You can see the formula for ChatGPT highlighted on the left, and the result in the cell on the right.

4. Finish adding the rest of the formulas

I will continue to add the required formulas in the spreadsheet and now start calculating the commission. I ask ChatGPT to "Please create a formula to calculate the 10% commission for the total sales in cell H3".

After calculating the commission, the next step is to calculate the total inventory and the remaining inventory, and the formula is added.

5. Fill in the rest of the spreadsheet

Finally, we need to fill in the rest of the table with formulas. Now, hover your mouse over the lower right corner of the cell that has the formula, hold it down, and drag it to the other cells that need to enter the formula.

Then perform the same operation on the remaining columns.

Once you add formulas for the remaining columns, you have a complete spreadsheet.

Frequently asked questions

Is there any way to use AI to generate formulas in Excel?

Yes, you can generate formulas in Microsoft Excel just as easily as using ChatGPT because Microsoft has built the Copilot AI tool into the program.

Why doesn't ChatGPT's formula work?

If a formula given by ChatGPT doesn't work in Excel, it may have misunderstood you. Look at the formula carefully to see where ChatGPT got it wrong, and then see how to rewrite the prompt to get the correct answer.

For example, my prompt is "Create a total cost formula that adds the values in columns F, G, and H."

Because I didn't specify that I wanted the formula to add the values in cells F3, G3, and H3, the formula added the values in the entire column range.

Can ChatGPT write complex Excel formulas?

ChatGPT is a great helper for writing Excel formulas. In this example, I used a simple formula to walk through the whole process, but you can also ask the AI chatbot to write more complex formulas.

Keep in mind that the accuracy of ChatGPT results depends heavily on the clarity of your prompts.

Can you write Excel formulas using other AI tools?

Other AI chatbots, such as Microsoft Copilot and Google Gemini, can also follow the above steps to create Excel formulas.