Maria Diaz/

Figuring out how to write the perfect Excel formula to achieve the desired result can be a challenge, especially when you have a lot of data on a spreadsheet and need an intricate formula beyond calculating a sum -- or you're Excel formula challenged. Now that's all changed, and ChatGPT has become the poster child for the saying 'work smarter, not harder.'

How to use ChatGPT to write: Code Resumes  Essays Cover letters 

ChatGPT and other AI chatbots can easily and quickly help you create formulas for your Excel spreadsheet for free without signing up for a specialized website, such as ExcelFormulaBot. 

The great 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 complicated as you'd like -- as long as you're crystal clear in your instructions. 

How to use ChatGPT to write your Excel formulas

Screenshot by Maria Diaz/

What you need:Using ChatGPT to write an Excel formula requires access to Microsoft Excel or Google Sheets, as you can use these formulas for both applications. You will also need an OpenAI account to access ChatGPT.

Also: Real-time deepfake detection: How Intel Labs uses AI to fight misinformation

Remember that, as informed as these AI chatbots are, they're still not as well versed in nuances as a human and can make errors or misinterpret prompts. 

1. Open ChatGPT and your spreadsheet

Open your Excel file or Sheets spreadsheet and go to chat.openai.com to access ChatGPT. If you're not logged in, this is the time to do so with your OpenAI account -- you can also create an account at this point. After logging in, you'll be taken to a conversation with ChatGPT. 

Also: I've tested a lot of AI tools for work. These are my 5 favorite so far

The spreadsheet below is the example we will use: a made-up inventory of devices for January 2023. It has 45 products in column A, each with an SKU number (column B), amount in inventory (column C), quantity purchased in January (column D), price for each unit (column E), and the subtotal (column F, with the formula already). 

We're going to use ChatGPT to create a few simple examples of formulas, including calculating sales tax (column G), seller's commission (column H, not pictured), total (column I, not shown), and the leftover quantity in inventory for the next month (column J, not displayed).

Screenshot by Maria Diaz/

2. Be very clear with ChatGPT

It's time to lay out what you want with ChatGPT and ask it for the formula you want. To do this, you must explicitly explain to the AI chatbot what you want the formula to do.

Also: Six skills you need to become an AI prompt engineer

You must be very clear -- mind-numbingly so -- as any errors ChatGPT makes due to a misunderstanding can lead to inaccurate values in your table. So, channel your inner eleventh-grader asking your computer-lab teacher how to create a specific formula.

Suppose we want ChatGPT to create the rest of the formulas. In that case, we have to explain things very plainly and specifically, so we'll ask the AI chatbot to "write an Excel formula to calculate the sales tax of the monetary value in column F, at a 6% rate".

I didn't specify which row in column F we want to start the formula with, so ChatGPT defaulted to F2.

Screenshot by Maria Diaz/

3. Copy and paste the formula to Excel

Copy the formula from ChatGPT and paste it into the Excel cell you want to populate with the results, making any necessary edits. To the best of your ability, confirm the results are correct before populating the rest of your cells.

Also: How to create a drop-down list in Excel -- quickly and easily

In the example below, I corrected the formula to reflect the correct cell, F3, that needs to be included in the calculation of the sales tax. 

You can see the formula from ChatGPT highlighted on the left, with the result in the cell on the right.

Screenshot by Maria Diaz/

4. Finish adding the rest of your formulas

I'll finish adding the formulas I need to my spreadsheet, beginning with the commission calculation, by asking ChatGPT to "Create a formula to calculate 10% commission on the monetary total in cell F3".

Screenshot by Maria Diaz/

5. Complete the table

Once I add my commission formula, I can move on to the next two columns to calculate the total and the leftover inventory to complete the table.

Screenshot by Maria Diaz/

6. Populate the rest of your spreadsheet

It's time to populate the rest of your spreadsheet with your shiny new formulas. To do this, place your cursor on the lower-right corner of the cell with the formula, and click and drag your cursor over the rest of the cells you want the formula in. 

Also: ChatGPT vs Bing Chat vs Google Bard: Which is the best AI chatbot?

The same formatting will be followed, including the subsequent progression of the formulas.

Once you add the formulas to the rest of the column, the spreadsheet will be complete.

Screenshot by Maria Diaz/

FAQs

Is there a way to use AI for formulas within Excel?

Ideally, you could generate formulas within Microsoft Excel as easily as with ChatGPT by having an AI tool already built into the program. That's not the case at this time, but Microsoft is rolling out a set of AI tools for many of its products, including the Microsoft 365 suite of programs named Copilot. 

Also: Generative AI is coming for your job. Here are 4 reasons to get excited

Though Microsoft Copilot isn't widely available yet, one can hope the new features include a GPT-4-integrated Microsoft Excel. 

Why is the formula from ChatGPT not working?

ChatGPT might have misunderstood your prompt if it gives you a formula that isn't working in Excel or Sheets. Go through the formula to see where the error could be on ChatGPT's end and see how you can reword your prompt to get the correct answer. In the example below, my prompt was "Create a formula for the total cost that adds the values in columns F, G, and H."

Screenshot by Maria Diaz/

Since I didn't specify I needed the formula to add the values in cells F3, G3, and H3, the formula adds the values in the entire column range, which is not what we need.

Also: How does ChatGPT work?

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

Can ChatGPT write intricate Excel formulas?

ChatGPT is the perfect resource to write formulas for Excel or Google Sheets, whether simple or intricate. 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 their limits. 

Also: Software developers' dance with generative AI is still at that awkward stage

Remember that the accuracy of ChatGPT's results depends greatly on how clear your prompts are. 

Can you use Bing Chat to write Excel formulas?

Other AI chatbots, like Bing Chat and Google Bard, can also create Excel formulas for you by following the steps above. 

How Bing Chat and Google Bard answered the same prompt that I gave ChatGPT to write a formula that calculates sales tax. 

Screenshot by Maria Diaz/

Artificial Intelligence

7 advanced ChatGPT prompt-writing tips you need to knowThe 10 best ChatGPT plugins of 2023 (and how to make the most of them)I've tested a lot of AI tools for work. These are my 5 favorite so farHuman or bot? This Turing test game puts your AI-spotting skills to the test

• 7 advanced ChatGPT prompt-writing tips you need to know
• The 10 best ChatGPT plugins of 2023 (and how to make the most of them)
• I've tested a lot of AI tools for work. These are my 5 favorite so far
• Human or bot? This Turing test game puts your AI-spotting skills to the test