2023-08-21 14:30:05
A large number of Excel users are content to create tables, in order to take advantage of the good alignment of information, which the boxes of this software allow. However, Excel is first and foremost a software dedicated to facilitating calculations. In fact, his documents are called “spreadsheets”.
Thus, the essence of Excel lies in the formulas. A formula is a mathematical expression that operates on certain cells. For example, we place an amount in A1, another in A2, and further on a formula that will perform calculations on these two values: averages, sum, etc.
The concern is that said formulas are not necessarily easy to implement. However, ChatGPTChatGPT can be of great help in this regard. Provided you follow a few rules…
Clearly specify to ChatGPT what you want to obtain
The quality of the answers one gets from ChatGPT depends on how one addresses it. To use ChatGPT well, it is important to formulate your requests well and this is a subject that we have covered in detail in this article: “How to use ChatGPT well? »
As for using ChatGPT with Excel:
it is important to specify to him that you work with the French version of Excel; it will sometimes be necessary to repeat it to him. If the formula he offers you does not work, remind him that we need a formula that works with the French version of Excel; be as explicit as possible in your request and therefore clearly describe the problem to be solved, but also the spreadsheet.
Here we will see some examples. In the following examples, we have used the free version of ChatGPT and therefore the one operating on GPT-3.5, but it goes without saying that the answers will be even better with the paid version operating on GPT-4GPT-4.
Generate random numbers within a given range
We submitted the following request to ChatGPT:
“For the needs of an Excel course, I need to generate a table displaying the sales of a shop in three cities: Lyon, Montpellier, Juan-les-Pins.
These cities are placed horizontally.
In vertical we have the months from January to June.
I would like to display random values but respecting a certain logic: the differences should not be too high. So values between 300 and 600. What formula can I use for this? »
Here is ChatGPT’s response:
“To generate random values in Excel while maintaining consistency and avoiding too large deviations, you can use the RANDBETWEEN function (or RAND.BETWEEN, in English) in combination with the “ROUND” function to limit the values in the desired range. Here’s how you can do it:
Suppose you want to generate the random values in cells in the range B3:D8 (with months in column and cities in row).
In cell B3, enter the following formula to generate a random value between 300 and 600:
=ROUND(RAND.BETWEEN.TERMINALS(300; 600); 0)
Press Enter to generate a random value for the first city (Lyon) and the first month (January).
Now copy the formula from cell B3 to the other cells in the range B3:D8. This will generate consistent random values for other months and cities while keeping values within the range of 300-600.
Make sure you understand that these values will be random, but each time you recalculate the spreadsheet, new values will be generated. »
As we can see, ChatGPT does not just tell us the formula to copy into Excel, it explains to us which formulas it uses (ALEA.BETWEEN.BORNS and ROUND). He then gives us the step-by-step procedure. In fact, this formula did create an array of random numbers between 300 and 600.
A formula for calculating loan repayment
Other situation. We gave ChatGPT the following problem:
“I would like to borrow 5,000 euros over three years. The borrowing rate is 3.85%. Could you give me the Excel formula to display the monthly repayment amount? »
Here we have another good example of the help that ChatGPT can bring because in itself, spotting such a formula is not easy. This time, ChatGPT first gave us a formula adapted to the English version of Excel and so we finally had to remind it that we were on the French Excel. He then gave us the right solution.
“In the French version of Excel, the function names are in French. The function you should use to calculate the monthly repayment amount of a loan is the “MPV” (Monthly Payment Value) function. Here’s how you can do it:
Suppose the loan amount is in cell A1, the interest rate is in cell A2, and the loan term in years is in cell A3.
In a cell, enter the following formula to calculate the monthly repayment amount:
=VPM(A2/12; A3*12; -A1)
The syntax with semicolons instead of commas is specific to the French version of Excel.
Press Enter to get the monthly repayment amount.
Make sure cells A1, A2, and A3 contain the correct values (5000, 3.85, and 3 respectively, in your case).
The cell containing the formula will then display the monthly repayment amount for a loan of 5000 euros over 3 years at an interest rate of 3.85%. »
In this example, ChatGPT asks us to place:
in A1, the loan amount: 5,000; in A2, the interest rate: 3.85%; in A3, the number of years: 5.
After applying the formula indicated by ChatGPT, Excel returned a monthly amount of €91.74.
Ask ChatGPT to create a macro for Excel
Creating a macro is on another level of complexity since it involves using language instructions Visual Basic. However, here again, ChatGPT will prove to be very useful. However, it is necessary here to indicate to him the maximum of details so that the problem is as clear as possible for him. Here is the request we made to ChatGPT:
“I have a sheet with a list of first and last names. The first names are in column A and the last names in column B.
These first and last names were entered without concern for presentation.
I would like you to write a macro that will go through each line filled in (from line 2) and rewrite the first and last name in the form:
. capitalize the first letter
. tiny for others. »
Following this request, ChatGPT provided the following response:
“Of course, I can help you create an Excel macro to format the first and last names according to the desired presentation. Here’s how you can do it:
Open the Excel workbook where you want to apply the macro.
Press ALT+F11 to open the VBA editor.Click “Insert” in the VBA editor menu, then select “Module”. This will add a new module to your workbook.In the module, you can paste the following VBA code:
Sub FormaterPrenomsNoms()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
‘ Specify the name of the sheet where the first and last names are
Set ws = ThisWorkbook.Sheets(“NomDeVotreFeuille”)
‘ Find last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
‘ Iterate each line from line 2
For i = 2 To lastRow
‘ Format first and last name
ws.Cells(i, “A”).Value = StrConv(ws.Cells(i, “A”).Value, vbProperCase)
ws.Cells(i, “B”).Value = StrConv(ws.Cells(i, “B”).Value, vbProperCase)
Next i
End Sub
Modify “NameOfYourSheet” by the actual name of the sheet where the first and last names are.Close the VBA editor.To run the macro, press ALT+F8 to open the “Macro” dialog box. Select “FormatFirstNamesNames” from the list of macros, then click “Run”.
This macro will iterate through each line starting from line 2, format the first and last names as you requested (first letter uppercase, other letters lowercase) and save the changes in the same sheet.
Remember to save your workbook before running the macro in case you want to go back. »
As we can see here, ChatGPT gives us its step-by-step instructions. It starts by telling us how to open the VBA editor (Visual Basic) and then makes us insert a module.
ChatGPT then gives us the complete VBA code that must be copied and pasted into the VBA editor. You just need to change the name of the sheet to that of the tab used, usually “Sheet1”.
Once out of the VBA editor, all that remains is to launch the macro. And we get the desired result.
Unexpected access to advanced Excel functions
As we can see in these examples, ChatGPT allows us to exploit advanced Excel services that would normally be inaccessible to the majority of users. It provides us with the procedure to follow, and the code – sometimes very complex like here – that must be given to Excel to obtain a given result. So, as much to say, ChatGPT opens the way to a much more advanced use of Excel than during normal use.
Thus, the main thing is to present the problem to be solved to him in as much detail as possible, then let him operate. And, if it doesn’t work as expected, tell him about the error messages displayed by Excel so that he offers the solution. You will thus be able to use Excel at a level usually reserved for experts in this software.
1692810449
#ChatGPT #Excel