Not everyone is an Excel spreadsheet expert, and you might not always know how to write the exact formulas you need for a given data set. If you’re having trouble figuring out the right formula for your data and computational needs, there’s a new option that can assist you in crafting that formula in a few simple steps.
This option is ChatGPT, the highly buzzed-about new AI chatbot from the artificial intelligence company OpenAI. Among its many capabilities, ChatGPT can also write Excel formulas. In this comprehensive guide, we’ll show you how to seamlessly integrate Microsoft Excel and ChatGPT. Let’s get started.
Difficulty
Easy
Duration
5 minutes
What You Need
-
Microsoft Excel
-
An OpenAI account
-
A PC
How to Use ChatGPT to Write Excel Formulas
Let’s take a look at how to utilize ChatGPT to write Excel formulas. For the purpose of this guide, we’ll use a simple sample data set to perform basic calculations (such as subtracting values) using the formulas generated by ChatGPT.
Remember: You’ll require an OpenAI account and a Microsoft Excel spreadsheet with a data set.
Step 1: Proceed and open the Excel spreadsheet you wish to work with. In our example, we have a straightforward table comprising products, services, projected prices, and their actual prices. Using Excel formulas crafted by ChatGPT, we aim to calculate the differences between the projected price and actual price for each product or service listed (Column D), as well as the subtotals of Columns B, C, and D.
Step 2: Now open your web browser and head to the ChatGPT website. Subsequently, log in to your OpenAI account. You should now see the “New chat” screen for ChatGPT.
Step 3: On the new chat screen, type in your request for an Excel formula for your data in the “Send a message…” text box at the bottom of the chat screen. Then press the “Enter” key on your keyboard.
In our example, we want to fill the cells of the Differences column (Column D) with the answers obtained by subtracting the values in the Actual Price column (Column C) from their corresponding values in the Projected Price column (Column B).
So we’ll start by asking ChatGPT to write an Excel formula for subtracting the number in cell C2 from the number in B2. It’s crucial to phrase your request clearly and precisely to avoid any misunderstandings on ChatGPT’s part. For our example, we sent the following message to ChatGPT: “Please write an Excel formula subtracting the value in C2 from the value in B2.”
Step 4: ChatGPT’s response to your request should include a formula for you to use and a brief explanation of how to apply it in an Excel spreadsheet. Copy the formula from the chat screen by highlighting it and then pressing “CTRL + C” on your keyboard.
Step 5: Go to your Excel spreadsheet and select the cell where you want to insert the formula. Then paste the formula into the cell by pressing “CTRL + V” on your keyboard. The correct answer to your formula should immediately appear in the cell you selected. In our example, ChatGPT generated the following formula for our request: “=B2-C2”. And once we copied and pasted this formula into our desired spreadsheet cell (D2), the formula yielded the correct answer: The difference between Product 1’s Projected Price (B2 which equals $50) and its Actual Price (C2 which equals $22) is $28.
Step 6: Then, to fill in the remaining blank cells in the Differences column (Column D) with the correct answers for each product or service, you can simply hover your cursor over the lower right corner of the cell where you pasted the formula. Once you see the black “Plus sign” icon appear, select the corner and drag your cursor to select the rest of the column until you reach the last row of data. Then release your hold on the cursor. Once you do, those empty cells should automatically populate with the correct answers for their respective rows of data.
Step 7: Let’s do another example, this time for addition. Using the same spreadsheet example, we now want to know the total sum of all the values in Column B (Projected Price).
So we’ll return to ChatGPT and send the chatbot a request for an Excel formula that adds up all the values in column B, from B2 to B11. In this case, our message to ChatGPT was: “Please write an Excel formula that adds up all the values in Column B, from B2 to B11.”
Step 8: ChatGPT should respond with its formula and an explanation of how to use it. Then you should copy and paste that formula into your desired spreadsheet cell, just as we did for the subtraction formula earlier in this guide.
Once you paste this addition formula, the correct answer should appear automatically in that cell. For our example, ChatGPT provided this formula to add up all the cells in Column B, from B2 to B11: “=SUM(B2:B11)”. This formula correctly provided the answer of $5,497. This means the subtotal of the Projected Prices of the products and services listed in this spreadsheet is $5,497.
Step 9: Lastly, since we want to fill in the rest of the data set in this spreadsheet with the subtotals of columns C and D (the actual prices and differences of all the products and services, respectively), we’re going to hover our cursor over the lower right corner of the cell we just copied and pasted our addition formula into.
Once the black “Plus sign” icon appears, we’ll select this corner and then drag it over to the right to select the next two cells. Once they’re selected, release your hold.
Once you release your hold, the correct subtotal answers should appear for their respective columns of data.
While you can create a graph with Excel and perform various other data analysis tasks, having the option to streamline formulas with ChatGPT can potentially transform the way you use this software.