'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.
Figuring out how to write the right Excel formula to achieve the result you need can be a challenge, especially when you have a lot of data on a spreadsheet and need an intricate formula beyond calculating a sum. That is until ChatGPT arrived.
How to use ChatGPT to write: Code | Resumes | Essays | Cover letters
ChatGPT and other AI chatbots can easily help you create formulas for your Excel spreadsheet for free and without signing up for a specialized website, like ExcelFormulaBot. The great thing about using artificial intelligence like that in ChatGPT or Bing Chat to create formulas for Microsoft Excel (and Google Sheets) is that you can request formulas that are as simple or as complicated as you'd like -- as long as you're crystal clear in your instructions.
What you need: Using ChatGPT to write an Excel formula requires having access to Microsoft Excel or Google Sheets, as you can use formulas for both applications. You will also need an OpenAI account to access ChatGPT.
Keep in mind that, as intelligent as AI chatbots are, they're still not as well-versed in nuances as a person and can make errors or misinterpret prompts.
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: How to use ChatGPT to write code
The spreadsheet below is the example we're going to 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 a formula already).
We're going to use ChatGPT to create a few simple examples of formulas, including calculate sales tax (column G), seller's commission (column H, not pictured), total (column I, not pictured), and the leftover quantity in inventory for the next month (column J, not pictured).
It's now time to lay out the concept of what you're looking to achieve with ChatGPT and ask it for the formula you want. To do this, you have to explicitly explain to the AI chatbot what you want the formula to do.
Also: How to save a ChatGPT conversation to revisit later
It's important that you be very clear, mind-numbingly so, as any errors ChatGPT makes as a result of a misunderstanding can lead to inaccurate values in your table. Channel your inner eleventh-grader asking your computer lab teacher how to create a specific formula.
If we want ChatGPT to create the rest of the formulas, we have to explain it 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".
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.
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".
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.
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 and the new AI are wreaking havoc on cybersecurity in new and frightening ways
The same formatting will be followed, including the subsequent progression of the formulas.
Ideally, you'd be able to easily generate formulas within Microsoft Excel as easily as you can with ChatGPT, by having an AI tool already built into the program. At this time, that's not the case, but Microsoft is rolling out Copilot, a set of AI tools for the Microsoft 365 suite of programs. Though Microsoft Copilot isn't widely available yet, one can hope the new features include a GPT-4-integrated Microsoft Excel.
If ChatGPT gave you a formula that isn't working in Excel or Sheets, then it might've misunderstood your prompt. 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".
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.
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".
ChatGPT is the perfect resource to write formulas for Excel or Google Sheets, whether they're 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 its limits. Remember that the accuracy of ChatGPT's results depends a lot on how clear your prompts are.
Other AI chatbots, like Bing Chat and Google Bard, are also able to create Excel formulas for you by following the steps above.