As we already explained to you in how to make a function in Excel, the usefulness that functions in Excel acquire is of great importance to be able to work successfully and productively. It is necessary to learn how to use the functions in Excel to be more productive and perform simple and complex operations, depending on the need that arises in each situation; It is important to learn the necessary resources to always be able to get through it successfully, knowing how to reflect in Excel what we want to do or carry out . In this sense, for almost any operation that we want to perform, we are going to have to use a function. And the most important thing is that we are going to explain to you a method to be able to do practically any Excel function , in a simple, practical, and fast way..
As we already explained, functions add a lot of value when we do simple operations such as a sum, especially when the sum affects an entire column. But functions are also used to perform complex mathematical operations, and also to carry out other operations that are not mathematical, but are very useful to be able to do some operations in our Excel sheets, such as concatenating cells or searching for matches in other sheets. to bring us value. These are just a few examples of the many operations that we can perform in Excel through the use of functions.
Learning all the functions of Excel is like learning all the words in a dictionary: a complex task. The important thing is to know which functions are most important, how to execute them, and have the resources to be able to use other functions that we do not know and that provide us with a benefit in our Excel work.
Being able to use any Excel formula is already a task that can be simple and fast if we use new technologies. The use of Artificial Intelligence in Excel can provide us with many benefits in this sense when it comes to advancing in Excel. As?
- Using Artificial Intelligence to ask the name of a function, when we want to perform an operation and we do not know which function we should apply.
- We can use Artificial Intelligence to know what the syntax of a function is like in Excel.
- We can also use Artificial Intelligence to see examples that help us perform the function or formula we need.
- And the most important thing is to make use of Artificial Intelligence to be able to obtain a personalized function or formula, which we can copy and paste into a cell, and we can drag to other cells if necessary.
One of the benefits that Artificial Intelligence can bring us in Excel, or in any other program or application, is to obtain a personalized response . Thus, we can find out not only the name of a function, but also the custom syntax that we must apply in our case. In this way, we will learn the functions that we need, and how to apply them in our case, in each specific case.
One of the complexities that exist when learning from Excel or other programs is precisely in the transition that exists between theory and applied practice. On many occasions the theory is explained in a complex way, or we find courses or tutorials that have been directly translated from English. We find expressions that we do not understand, or formulas that, taken to our field, we are not able to apply. It is, in this paradigm, where AI can provide us with a differential value in learning , and in the use of Excel resources that we would not otherwise learn..
Next, we are going to see an example of how we can use AI (ChatGPT), to be able to obtain a personalized result, which we will be able to use directly by copying the formula that we are going to obtain, and reviewing later, as we should always do, to check that the operation has been a success.
1 How to create any formula in Excel with AI
To be able to create a custom formula, depending on the operation we want to perform, and depending on what our table is like, where the columns are located, etc. We need to be very specific when making the request.
Let's see what aspects we should take into account in our chat request:
- We must indicate what operation we want to perform. Although we don't know the name of the function. If we want to calculate a mean value, an average, or another type of operation, we must be very explicit about what we want to do.
- It is very important that we indicate in which column or row we want to make the request.
- We must indicate, if necessary, from which row to which row we want to perform the operation. If, for example, we want to calculate an average, as we are going to do below the example, of a column that reaches row 217, we must also include it in the request that we are going to make.
- As a good practice, we can also indicate in the request what the name of the Excel sheet we are working on is called, and what the name of the Excel file is. It will be useful if you want to perform an operation that involves cells, columns or rows that are in different files.
- If in your Excel table you have headings in the columns, which is the usual way to indicate what information you find in a column, in many cases it is important to say so that the data ranges are calculated from row , and not from row 1, since the headers would be in row 1.
- On the other hand, in some requests it will be important to indicate the configuration language that we have in Excel.
Important considerations when requesting custom syntax.
These are the considerations that we must take into account to improve the quality of ChatGPT responses.
- As we have said, it is very important that you provide all the necessary information so that the formula that ChatGPT will give you is correct.
- In the same way that, when making a request, it is very important that you correctly write all the names of the files, or of the Excel sheets that you are going to include in the request, taking into account capital letters, lower case letters, accents.
- When referring to a specific name of a column, of an Excel sheet, or of a specific value, we can take as a good practice to write the name in quotes (“name”). If we say, for example, that we want to calculate an average in an Excel sheet called SALES, in the request we will write “SALES”.
- After obtaining our formula, it is very important that we check the result obtained because we may have made a mistake at some point in the request, and we are obtaining a result that is not correct.
Furthermore, as we are going to explain to you below, the formula that ChatGPT can give us may contain some element that we must change, an error, or it may be based on another language. For this reason, we must always review the result that ChatGPT gives us..
Important: Always check the formula
In many tests carried out, especially when we want to develop somewhat more complex and long formulas with AI, we can find elements that we must change in the formula that ChatGPT returns. For example, it is common to have to change the separators of a formula: change the “,” to a “;” to separate the different parameters that are included in the formula.
A good practice to be able to learn more aspects of Excel is to tell ChatGPT, after obtaining a result, to give us another formula or use other functions . But as we have indicated, it is very important that, above all at the beginning, you review the formula and the result you are going to obtain. If you encounter an error message when you paste the formula into your Excel, we recommend that you review the formula, especially the date ranges, to make sure they are correct, and review the formula separators. On occasion, we can ask ChatGPT again or ask them to give us other ways to carry out an operation. In one of the examples that we are going to show you, we will see how to solve this formula error in Excel in a very simple way
2 Steps to create any formula in Excel
In order to create the custom syntax of a formula in Excel, we are going to open the Open AI WEB page where the ChatGPT function is located.
ChatGPT
When we open the Open AI website, we log in to be able to use the Open AI Chatboot; Depending on whether we are already registered or not, we will click on “Log in” or “Sign up”.
Please note that if you are not registered, and do not want to register, you can use your Google, Microsoft or Apple account to sign in.
Once you have logged in, you can now start your conversation with ChatGPT.
Now, we are going to open the Excel file on which we want to perform the query. In our case, as an example, we open our file with fictitious company data of a company.
Example 1: How to make a conditional formula in Excel with ChatGPT
Now, as a first example, we are going to calculate the average value of column “I”, to know what margin we achieve on average with the sale of products. To calculate the average value, we are going to query ChatGPT to give us the specific syntax we need, taking into account that the values to calculate the average are found in a sheet called “SALES”, in column I; And, furthermore, the values in column I reach row 217.
We go back to ChatGPT where we have already logged in, and we write the following, taking into account all the considerations and aspects to take into account described above:
“calculate average value in Excel configured in Spanish from column I found in the "SALES" sheet from row 2 to row 217”.
By writing and clicking on send message, ChatGPT will return a response in which it will give us the name of the function that we must use (Average) and the explanation, along with the steps that we must follow to calculate the average value in Excel; And what interests us most, it will show us the syntax in a frame, which we can copy directly from the “Copy code” button.
Now we paste the formula into our Excel, just below the data in Column I
We check the result, and we will see that the calculation made is correct. In this way we have not only obtained the correct formula, we have also learned that in order to make averages there is a function in Excel called Average and that we can therefore use next time without using AI.
Example 2: How to calculate the maximum value of a column in Excel with ChatGPT
We can also use this method to be able to do operations such as calculating the maximum value, for example, of a column. The request, in this case, must be along the same lines, with the guidelines and recommendations described above. In this case, the request that we are going to make to ChatGPT is the following:
“ Make a formula in Excel in Spanish to find out what is the maximum value of column A found in the "SALES" sheet from row 2 to row 217 ”.
We obtain, as we see in the image below, the explanation of the function (it is the MAX function) that we must use in this case, and the explanation of how to perform the formula, along with the exact and personalized formula that we must use.
We copy the formula with the “Copy code” button enabled, in the frame or window where we see the formula in ChatGPT, after making our query.
As we can see in the image below, by pasting this formula (using the keys on our keyboard (Ctrl + V) in the cell that is right where the column ends, we have obtained the correct maximum value of column A.
Example 3: How to make a conditional formula in Excel with ChatGPT
Now we are going to see another more complex example, using the same Excel file, where we are going to ask ChatGPT for a custom conditional formula, and we are going to see how we are going to make a small change in the formula it gives us. A change that we have already mentioned, which is located in the parameter separators.
This is the request we make to ChatGPT to add a column, but with one condition: that they belong to the year 2022, information that is collected in another column of our Excel.
We therefore go to ChatGPT, and make our request, as we already know, giving details of the operation we want to perform:
“calculate a conditional sum in Excel configured in Spanish, in a file where I have a table with several columns up to row 217 with headers in the first row. I want to add the sales in column "E" only if in column "D" the value that appears is equal to or greater than 2022”
The result we obtain, as usual, is the one we see in the photo, where the function that is going to be used is explained, it gives us the formula or personalized syntax so that we can copy and paste it into our Excel sheet, and it gives us a explanation that helps us improve our knowledge of Excel.
When we copy and paste the formula obtained into our Excel, we will see that we are going to get an error:
How to solve error “There is a problem with this formula”
To solve this typical error in Excel, let's review the formula. As we said before, looking at the separators and the data ranges of the formula. In this case, we only have to change the commas (“,””) to semicolons (“;”) in the formula as you can see in the image below. By editing this aspect of the formula, we will now obtain the expected data, and we will also verify that it is correct.
Also remember that to change an element of a formula, we only have to click the mouse on the cell where the formula is located. When we click on the cell, we will see above in the formula bar, the formula that we can edit using our keyboard and mouse, to position ourselves within the formula, delete one or more elements, and write inside the formula with the keyboard.
These are just some examples of formulas that we can get in ChatGPT, if we know how to give the instructions correctly, and if we know how to review the formula later to correct possible errors.
In this sense, Excel's autocomplete function will always be useful to know what type of function we can use in a formula and the syntax. ChatGPT can provide us with a personalized syntax as we have seen in the examples, we can use it as another tool in learning, paying attention to the explanations it gives us, and why it chooses a specific function at each moment.
We will learn how we can build more complex formulas, what their syntax is, what parameters and how they should be used in the formula. But as we have already explained to you, taking into account the aspects and considerations that we must include in the requests, and always checking the result we obtain to make sure it is correct. Once we have already used ChatGPT to create a formula, it is important that the next time we have to create a similar formula, we try to build it ourselves without the help of Artificial Intelligence . In this way, we will be learning a method that guarantees us having useful support, that allows us to continue advancing in our knowledge of Excel and its functions, that allows us to be a little more autonomous in creating formulas and functions.