Even if Google is getting ready to introduce its chatbot, Bard, to the general public, ChatGPT is unquestionably the star of the current AI show. The OpenAI multi-purpose dialogue chatbot recently released version 4.0 with a tonne of new features, and despite many flaws, it has started the race to become the finest multi-purpose language AI yet. Data organization, analysis, and presentation are all doable with the spreadsheet program Excel. On the other hand, ChatGPT is a language model that can comprehend and produce natural language text. Nevertheless, Excel can be used to enter data or information that can be used as input for ChatGPT.
Although this tool already has some fantastic capabilities, throughout this early phase it is being further extended and enhanced. The tool has drawbacks, such as the fact that it sometimes provides incorrect information, even if it can certainly be valuable. Make sure to verify the correctness of the data that ChatGPT shows.
Regardless, that does not prevent you from using it as a source of help and making your challenging work much simpler. We’ll go over some of the best examples you can utilize with ChatGPT to elevate your Excel productivity.
We’ll start with simple examples and work our way up to more complex ones as we move forward.
Also Read- How To Use ChatGPT With Siri On iPhone
Example 1: To Calculate Total Revenues
ChatGPT can be used iChatGPT can assist users in comprehending and using Excel formulas by offering explanations and examples
As you can see above, we have created rough data to show you an example of how you can use ChatGPT to calculate Total Revenues. Although, it is a quite simple task to calculate the total on excel. But those who are new to excel can find it very helpful.
To do that, we are going to ask ChatGPT ” How to add values in Excel from cell B2 till B7?”
Following the above instructions provided by ChatGPT, we are going to use the formula in Excel which is =SUM(B2:B7), and find out what answer we get.
As you can see, we have copied the same formula that ChatGPT mentioned and got the results we were looking for. See, how simplified it got because of this tool by OpenAI.
To make your work even simpler, let’s move on to a little more complex example.
Also Read- ChatGPT, Google Bard, Microsoft Bing- How They Are Similar But Yet Different
Example 2: VlookUp & Index Match
In this example, we are going to find out the price of a Banana Milkshake from the table. I already have the table where I can see the price of a Banana Milkshake. But, just for an example if it’s large data and we want to find out the price of the Milkshake. For this, we normally use the Vlookup Formula or Xlookup, and sometimes a combination of functions known as Index Match.
Now, we are going to ask ChatGPT ” Can you give me the formula to find out Banana Milkshake on a table and return a Price?”
You can see the ChatGPT answer provided above. This is the formula it provided ” =VLOOKUP(“Banana Milkshake”,A:B,2,FALSE)”
Now, in the instruction, as you can see our table starts with A1 so we are going to put A1 instead of just A. and with B we are going to write the total number of rows which in my case is 7. so we are going to write B7. We finally get this formula : =VLOOKUP(“Banana Milkshake”,A1:B7,2,FALSE)
Now enter the formula in excel and let’s see if we get the correct answer
Finally, it matched the price in the table, so we know that the formula ChatGPT provided worked correctly. With little changes and following the ChatGPT instruction we found the price of the Banana Milkshake.
You can further ask ChatGPT if it can give you any other way to find out the price. Let’s see what answer it gives.
As you can see, it gave us 2 more ways to figure out the same answer. The first one says that we can use Index Formula with a combination of MATCH and the second one tell us that we can use the Filter function by using the Filter formula. You can see how ChatGPT can turn out to be super useful.
Let’s move forward to some much more complex examples.
Also Read- Why Investors Are Excited About ChatGPT and AI
Example 3: Extract the First Name from the List
In this example, we want to extract the first names from the email addresses. Usually, you can do this using Flash Fill in Excel. By clicking on Data > Flash Fill or just you can just do it by Ctrl + E. But, we want to see what ChatGPT suggests for us for this example. So, now we are going to ask ChatGPT ” Give me the Excel formula to extract the first names before @ character in cell A2?”
Now, look at what chatGPT answers
By looking at chatGPT’s answer, it gives us the LEFT function along with the FIND function. It even gave us an example so we don’t make a mistake. Now, let’s copy the formula onto the excel sheet.
After Selecting the B2 till B5 cell, we pasted in the function that we copied and then pressed Ctrl + enter. See what results we got!
That is exactly what we were looking to do, and we accomplished it by using ChatGPT instructions. We got all the first names that were before “@”.
Let’s move on to the tricky one to see the capability of this Tool.
Also Read- How To Use Bing AI With Siri On iPhone
Example 4: Let’s Try “Unique Count”
Generally, the unique count is little to do in excel. In this example, I want to find the unique count of all the students in the class. You can see there are children with the same names. I just want to find out how many unique names we have in my class. To do that, we are going to ask ChatGPT ” What is the formula to count the number of unique names or values in the list?”
Looking at the answer you can see that it provides us with an explanation by giving steps along with the sample formula to use in excel.
What we do now is we are going to copy the formula and paste the function after selecting the table and making a few changes in the formula accordingly. The following is the formula provided by chatGPT : =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&””))
And after making the necessary changes because I don’t want the header plus in the ChatGPT formula the total rows are 10 and mine are 9. So we get this formula:
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9&””))
This is what happens after applying the formula
Finally, the function gave us the correct unique count which is 6. We have 6 different unique students’ names in my class.
Let’s move on to the final and the most complex one so far.
Also Read- How To Use The AI-Powered Bing Chatbot on The New Bing Search Engine
Example 5: Jot down Macro from Scratch
Let’s directly dive into the example. In this example, we have given an example of people who owe money to an XYZ Chocolate Company. We are just looking to send them a reminder or email about the due amount. In the list, we have the customer’s email and the amount due. Before diving in to ask ChatGPT, make sure that the Developer option is marked checked. Then, click on OK.
Before diving in to ask ChatGPT, make sure that the Developer option is marked checked. Then, click on OK.
Now, you will see the new Tab Developer on top. Tap on that option.
After clicking on the Developer option, on the left side, you will see the Visual Basic option, click on it.
On the Visual Basic window, Go to inset > Module
After that, you have to ask chatGPT ” I want an excel macro to send an email. The subject of the email is “XYZ Chocolate Co. Invoice”. The text for the email is ” You have an amount due of $ X”. Use the Value X given in Column A. Use Columb B for Email Address to send the email.”
Let’s see what ChatGPT recommends
Finally, you can see it produced an excel macro to send emails using the subject and text we provided. Also in the second screenshot, you can see the guidance to use the code. In the first screenshot, you can see there is a copy option, copy the code from there.
First, make sure to enable the Outlook object Library before running it.
Paste the Code On Visual Basic
Now, you have to paste the code you copied earlier from ChatGPT on Visual Basic.
Make sure all values are put correctly in the codes.
After that click on Save and close the window.
Finally, in excel, click on the Developer option > click on Macros then you will see the new macro SendInvoiceEmail.
Simply, select Run and that’s it. You have successfully sent the emails. You can even cross-check through outlook to see if your email is sent or not.
Nonetheless, ChatGPT is a wonderful software that can assist with automating diverse Excel processes, enhancing data analysis, and accelerating the data-working process. It helps you save ample time and improve productivity. Don’t forget to try the examples given above!
