Not everyone is an expert in Excel spreadsheets, and you may not always know how to write the formulas you need for a particular set of data. If you’re having trouble finding the right formula for your data and calculation needs, there’s a new option that can help you write that formula in just a few steps.
That option is ChatGPT, a lively new AI chatbot from artificial intelligence company OpenAI. In addition to everything else ChatGPT can do, it can also write Excel formulas. In this tutorial, we’ll show you how to use it to get the Excel formulas you need. Let’s start.
How to use ChatGPT to write Excel formulas
Let’s see how to use ChatGPT to write Excel formulas. For the purpose of this tutorial, we will use a simple sample data set for basic calculations with formulas generated by ChatGPT.
Don’t forget: you will need an OpenAI account and a Microsoft Excel spreadsheet with the dataset.
Step 1: Go ahead and open the Excel spreadsheet you want to use.
In our example, we have a simple table of products, services, predicted prices and their actual prices. Using Excel formulas written by ChatGPT, we want to calculate the differences between the predicted price and the actual price for each product or service listed (column D), and the subtotals of columns B, C, and D.
screenshot
Step 2: Now open your web browser and go to ChatGPT website. Then log in to your OpenAI account. You should now see New chat screen for ChatGPT.
screenshot
Step 3: On the new chat screen, type your Excel formula request for your data in Send a message… text box at the bottom of the chat screen. Then press input on your keyboard.
In our example, we want to populate the cells of the Differences column (column D) with the answers from subtracting the values of the Actual Price column (column C) from their corresponding values in the Predicted Price column (column B).
So we’ll start by asking ChatGPT to write an Excel formula subtracting the number in cell C2 from the number in B2. You must be careful to formulate your request as clearly and precisely as possible, otherwise ChatGPT may misunderstand you and give you the wrong formula.
For our example, we sent the following message to ChatGPT: Write an Excel formula subtracting the value in C2 from the value in B2.
screenshot
Step 4: ChatGPT’s response to your request should include a formula you can use and a brief explanation of how to use 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 your formula to be. Then paste your 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 when we copied and pasted that formula into the desired spreadsheet cell (D2), the formula gave the correct answer: the difference between the predicted price of product 1 (B2 which equals $50) and its actual price (C2 which equals $22) is 28 USD.
screenshot
Step 6: Next, to fill in the rest of the blank cells in the Differences column (column D) with the correct answers for each product or service, you can simply place your cursor over the lower right corner of the cell you just copied and pasted your formula into. Then once you see black Plus sign icon appears, select a corner and drag the pointer to select the rest of the column to the last row of data.
Then release the cursor hold. Once you’ve done this, those blank cells should automatically fill in the correct answers for their rows of data.
screenshot
Step 7: Let’s do another example, this time with addition. Using the same spreadsheet example, we now want to know the grand total of all the values in column B (Projected Price).
So we’ll go back to ChatGPT and send the chatbot a request for an Excel formula that sums all the values in column B, from B2 to B11.
In this case, our message to ChatGPT was: Write an Excel formula that sums all the values in column B, from B2 to B11.
screenshot
Step 8: ChatGPT should respond with its formula and an explanation of how to use it. You should then copy and paste that formula into the desired spreadsheet cell as we did for the subtraction formula earlier in this tutorial.
After you paste this addition formula, the correct answer should automatically appear in that cell. For our example, ChatGPT provided this formula to sum all the cells in column B, from B2 to B11: =SUM(B2:B11).
This formula gave the correct answer of $5,497. This means that the subtotal of the projected prices of the products and services listed in this spreadsheet is $5,497.
screenshot
Step 9: Finally, since we want to populate the rest of the data set in this spreadsheet with the subtotals of columns C and D (the actual prices and differences of all products and services), we’ll hover our cursor over the lower right corner of the cell where we just copied and pasted our addition formula.
Once black Plus sign icon appears, we will select this corner and then drag it to the right to select the next two cells. Once they are selected, release hold.
After you release the hold, the correct subtotals should appear for the corresponding data columns.
screenshot
Editor’s recommendations
Categories: GAMING
Source: newstars.edu.vn