X
Home & Office
Why you can trust ZDNET : ZDNET independently tests and researches products to bring you our best recommendations and advice. When you buy through our links, we may earn a commission. Our process

'ZDNET Recommends': What exactly does it mean?

ZDNET's recommendations are based on many hours of testing, research, and comparison shopping. We gather data from the best available sources, including vendor and retailer listings as well as other relevant and independent reviews sites. And we pore over customer reviews to find out what matters to real people who already own and use the products and services we’re assessing.

When you click through from our site to a retailer and buy a product or service, we may earn affiliate commissions. This helps support our work, but does not affect what we cover or how, and it does not affect the price you pay. Neither ZDNET nor the author are compensated for these independent reviews. Indeed, we follow strict guidelines that ensure our editorial content is never influenced by advertisers.

ZDNET's editorial team writes on behalf of you, our reader. Our goal is to deliver the most accurate information and the most knowledgeable advice possible in order to help you make smarter buying decisions on tech gear and a wide array of products and services. Our editors thoroughly review and fact-check every article to ensure that our content meets the highest standards. If we have made an error or published misleading information, we will correct or clarify the article. If you see inaccuracies in our content, please report the mistake via this form.

Close

How to use ChatGPT to write Excel formulas

Have you ever been stumped trying to figure out the right Microsoft Excel formula for your table? Let ChatGPT do the work for you.
Written by Maria Diaz, Staff Writer
Reviewed by Min Shin
ChatGPT and Excel
Maria Diaz/ZDNET

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. But now you can stop struggling and let ChatGPT do the work for you. 

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 you signing up for a specialized website, such as ExcelFormulaBot. The great thing about using artificial intelligence tools like ChatGPT or Copilot 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 Excel formulas

Ask ChatGPT to write Excel formulas
Screenshot by Maria Diaz/ZDNET

What you need: Using ChatGPT to write an Excel formula requires access to Microsoft Excel or Google Sheets, as these formulas can be used for both applications. You will also need an OpenAI account to access ChatGPT with GPT-4o, though you can access GPT-3.5 without one.

Also: Want an AI Job? Check out these new AWS AI certifications

Remember that, as informed as these AI chatbots are, they're not as well versed as a human in nuances 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: ChatGPT privacy tips: Two important ways to limit the data you share with OpenAI

If you're using GPT-4o, upload the Excel spreadsheet and ask ChatGPT to create different formulas for necessary calculations. If you have ChatGPT Plus, the uploaded spreadsheet will be interactive, and you can create customizable charts.

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). 

Also: How Microsoft's new AI Copilot features could transform teamwork and projects

Since GPT-4o isn't yet reliably available for free users, we will use ChatGPT's free tier with GPT-3.5, which doesn't allow file uploads. We'll prompt the AI chatbot 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).

Example formula
Screenshot by Maria Diaz/ZDNET

2. Be very clear with ChatGPT

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

Also: ChatGPT vs. ChatGPT Plus: Is a paid subscription still worth it?

It's important to 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 tenth-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, beginning in F3, at a 6% rate."

Write a clear prompt

Asking ChatGPT to "write an Excel formula to calculate the sales tax of the monetary value in column F, beginning in F3, at a 6% rate."

Screenshot by Maria Diaz/ZDNET

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 sales tax calculation. 

Copy and paste into Excel or Sheets

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

Screenshot by Maria Diaz/ZDNET

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".

Formula for commission
Screenshot by Maria Diaz/ZDNET

5. Complete the table

Once I add my commission formula, I can calculate the total and the leftover inventory in the next two columns to complete the table. I asked ChatGPT to "create a formula for the total cost that adds the values in columns F3, G3, and H3."

Total costs
Screenshot by Maria Diaz/ZDNET

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. Microsoft Copilot vs. Gemini: Which is the best AI chatbot?

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

Populate spreadsheet

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

Screenshot by Maria Diaz/ZDNET

FAQs

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

Yes, you can use Copilot across Microsoft productivity software, including Excel, with a Microsoft 365 subscription. All you have to do is open Excel in Microsoft 365 and select Copilot on the ribbon to open the chat pane. Then, you can ask the AI chatbot to create the formulas you need, like we did with ChatGPT. 

Also: To lead a technology team, immerse yourself in the business first

Why is the formula from ChatGPT not working?

If ChatGPT gives you a formula that doesn't work in Excel or Sheets, it might have misunderstood your prompt. Go through the formula to see where the error could be on ChatGPT's end and 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."

ChatGPT with incorrect formula
Screenshot by Maria Diaz/ZDNET

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 for writing simple or intricate formulas for Excel or Google Sheets. 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: AI phone scams sound scary real. Do these 5 things to protect yourself and your family

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

Editorial standards