The magic spell is AI. AI can be used as a chatbot or a virtual artist, but it can also write program code like Excel formulas. There are many ways. You can use a general-purpose AI like ChatGPT, or you can rely on specialized tools like Excelformulabot, Neural Formula, or Excel Formulator. If your goal is a relatively simple analysis of table values, Excel itself is enough to get you started.
Basic data analysis tools in Excel
Whichever method you choose, one thing to always keep in mind is the language used. Language plays a crucial role in the order of Excel formulas. For example, the German version of Excel does not know the “ROUNDDOWN” command and must say “ABRUNDEN” to understand it, and the German “Arbeitstag” becomes “WORKDAY” in the English version of Excel formulas. AI tools usually offer built-in translation options, but be aware of this.
At least the 365 version of Excel already provides simple data analysis functions that don’t require you to enter formulas. The “Data Analysis” function, available in the “Data” tab, analyzes the data and displays the results in different visualization formats. You can also analyze the data through custom questions. A brief guide to data analysis is here can be found
Automation of Microsoft Excel using ChatGPT
However, when trying to analyze more complex problems, Excel’s data analysis quickly reaches its limits. To go beyond its own functionality, you have to program Excel formulas yourself, and ChatGPT can help you with that. All you have to do is ask the AI for an Excel formula that calculates the median of all cells from R3 to R71. The response ChatGPT gives to the query is “=MEDIAN(R3:R71)”.
The tongue can be a stumbling block. For example, if the formula is displayed in English, such as “=COUNTIF(C13:C43,”K”), you can ask “What is the German version of the Excel formula =COUNTIF(C13:C43,”K”)? Then ChatGPT will give you the correct answer “German version of Excel formula =COUNTIF(C13:C43,”K”) is =ZÄHLENWENN(C13:C43,”K”)” Using ChatGPT in more than 500 Excel commands, you You can find even more. This will make things easier, but it’s a good idea to start learning ChatGPT by doing small, less complex tasks.
ChatGPT can also create Excel macros. This too can be directed to ChatGPT in natural language. For example “…just say “.create a VBA macro for the task”. In addition to writing macros, it explains how to use macros in Excel, i.e. how to insert and modify code in the Visual Basic editor.
However, ChatGPT sometimes uses terms or commands that only work in the English version of Excel. With Microsoft announcing its intention to integrate ChatGPT into its desktop products, this problem should be solved sooner or later.
In addition to language issues, there is another problem that the free version of ChatGPT often becomes unusable due to overload. You can also create a paid ChatGPT Plus account for $20 per month, but you might want to check out professional AI tools before doing so.
Excel formula robot
A typical tool Excel formula botam. As a cloud-based tool, it runs in your browser and can be used with Google Sheets as well as Excel. Excel Formula Bot supports German, English, French, Spanish, Italian and Turkish languages. Along with the formula builder, it also provides a task builder for non-formula related tasks. For example, creating charts, pivot tables or sorting data.
The other two script and regular expression generators are still in beta. Unlike ChatGPT, Excel Formula Bot’s scripting feature does not explain how to use the VBA editor in Excel, only the code of the created script. Descriptions are provided in English.
The second beta, Regex, is for checking, comparing, and finding regular expressions. A regular expression can be a pattern consisting of a sequence of characters (for example, finding all names with Luis, Louis or Lois in an array). Excel Formulabot generates the appropriate code.
All four generators work the same way. First, you need to choose if the code you want is for Excel or Google Spreadsheets. Then you can choose between “Generate” and “Explain”. In the field below, enter the actual task in natural language. Click “Submit” and the tool will generate or explain the code for you.
Overall, I would rate this tool higher than ChatGPT in terms of being able to reach your goals faster. However, there are also disadvantages. It’s free, but there are conditions, and the basic version only allows 5 requests per month, so it runs out quickly. Indeed, the code generation and the description each count as one request. An unlimited account costs $6.99 per month.
neural formula
neural formulais cheaper Free account users can request up to 15 requests per month, and an account with unlimited requests costs $1.99 per month. In terms of functionality, Neural Formula is on par with Excel Formula Bot, and it also provides formula translation and editing options.
The downside is that it only supports English. Of course, it is possible to translate formulas created in English into German Excel format. For users where this limitation isn’t too much of a problem, Neural Formula is a cheaper Excel AI tool.
excel formulator
excel formulator Also, only English is supported. You can create up to 7 requests per month and unlimited access costs $5.99 per month. Compared to the other two tools, it has a narrower range of features, offering only an equation builder and code explaining features, and no other features such as a translator.
AI Excel Support: Final Assessment
Overall, my favorite tool is Excel FormulaBot. Although it is the most expensive of the three professional AI Excel tools, it is a flaw, but the user interface that supports various functions and multiple languages is a big attraction. Moreover, the procedure is more systematic compared to ChatGPT.
Moreover, currently, in the case of the free version of ChatGPT, the server is almost always overloaded except at night, so it cannot be used reliably. Third is Excel’s own “data analysis” feature, which is worth using if you just need data visualization.
editor@itworld.co.kr
