Excel provides rich and easy-to-use functions, which are often used for desktop data calculation. However, there are also some special or complex calculations that Excel is not good at, such as splitting a long string and taking out all dates, having VLOOLUP return multiple results that match, splitting and merging multiple cells (array), and grouping and calculating a range of cells that contains a column name (grid data). Theoretically, although such calculations can be implemented with the built-in VBA of Excel, it is not suitable for non-professional programmers as a result of difficult syntax and cumbersome code. In fact, such calculations can be implemented with Excel add-ins, and the code is usually simpler. In this article, we will present some add-ins that are suitable for non-programmers, and can enhance Excel’s computing ability.

Advanced Formula Environment

Advanced Formula Environment (AFE) is a custom function development and runtime environment officially produced by Microsoft. AFE uses Lambda syntax, and can simplify function definition, and process the arrays in a loop manner. When working with AFE, we need to write a custom function first, and then write an expression in a cell and refer to this custom function. In short, AFE is convenient in installation and configuration, low in technical requirements and medium in computing ability.

The advantages of AFE include that it supports one-click installation, we can install it through the add-ins store built in Excel with few steps and without resorting to any third party; normally, we can write a function in Lambda syntax first and then refer to this function in a cell, which is more readable even though it is slightly less convenient to operate, or we can write Lambda syntax directly in a cell, which is more convenient to operate but it makes the readability significantly worse; AFE code is shorter than VBA code; a convenient prompt window is available when entering parameters.

The disadvantages of AFE are reflected in two aspects. First, despite the fact that AFE custom function is simple in structure, easy to code, and has lower technical requirements than VBA, and can increase the computing ability of Excel at a lower cost, simple function structure leads to insufficient computing ability, for example, it does not support neither a full-feature flow processing nor a complex calculation logic. Second, although AFE provides native functions with part of flow processing abilities, it does not improve the computing ability of native functions themselves, especially the calculation of grid data, not to mention the improvement of the fundamental data structure and computing syntax, so the overall computing ability is improved to a limited extent.

https://www.microsoft.com/en-us/garage/profiles/advanced-formula-environment-a-microsoft-garage-project/

esProc Desktop

esProc Desktop is a SPL-based add-in, and provides a self-developed computing engine, allowing us to write expression and refer to SPL function directly in a cell, and the operation is easy and natural. This add-in is convenient to install and configurate, low in technical requirements and powerful in computing ability.

esProc Desktop has many advantages, for example, it supports one-click installation with few steps; it eliminates the need to type commands manually or modify the configuration file; it eliminates the need to install other software or runtime environment additionally; it allows us to write expression directly in a cell and get the calculation result immediately without having to write a custom function or configurate the function interface; it follows the operation habits of Excel, the code is shorter and it is more convenient to operate; it provides a large number of computing functions, such as function for array or set data, and function for grid data, many of which are more powerful than native Excel functions; it provides SPL computing scripts, allowing us to perform flow processing and complex calculations through referencing the script file name in cell.

esProc Desktop does not provide prompt window when entering parameters, which is not less convenient.

http://www.raqsoft.com/esproc-desktop

xlSlim

xlSlim is a Python-based add-in that uses third-party function library as its computing engine. When using xlSlim, we need to write a custom function in Python script first, and then write expression in a cell and call this custom function. This add-in is complex in installation and configuration, medium in technical requirements and relatively powerful in computing ability.

xlSlim can utilize a large number of Python third-party function libraries, including NumPy for array, Pandas for grid data, many functions have more powerful computing ability than native Excel functions. xlSlim is a Python-script-style add-in, having the ability to process the flow and implement a complex business logic. Although Python script is more difficult to code and use than Excel function, and the code is longer, the amount of code is much smaller than that of VBA.

When it comes to its disadvantages, although xlSlim itself supports one-click installation, we need to build Python runtime environment and install third-party function library before installing xlSlim, which involve many steps and manual operations and are difficult for non-professional programmers; xlSlim doesn’t support writing expression directly in a cell, we have to create a Python custom function and register Python script file in a cell with the registration function, and sometimes we need to register multiple files, it is not convenient.

https://www.xlslim.com/

PyXLL

PyXLL is a Python-based add-in that uses third-party library as its computing engine. When using PyXLL, we need to create a custom function in Python, and then refer to this function in a cell, the operation process is less convenient. This add-in is powerful in computing ability, troublesome in installation and configuration and medium in technical requirements.

The advantages of PyXLL include that it provides a lot of computing functions, such as function for array or set data, and function for grid data, many of which have more powerful computing ability than native Excel functions; PyXLL is a Python-script-style add-in, having a powerful flow processing ability; PyXLL can modify Excel menu, allowing us to execute Python script through clicking menu; PyXLL provides a convenient prompt window when entering parameters. Although Python is not as concise as Excel formula, its code is shorter than VBA code, and the technical requirement is not high.

As for its disadvantages, we need to install and configure Python environment and third-party libraries such as Numpy and Pandas before installing it, yet these operations involve many steps and are technically demanding, and in some steps, we need to manually type commands or modify the configuration file, which is error-prone; PyXLL doesn’t allow us to write a calculation code in a cell, but write a Python script, which is inconvenient and technically demanding; the written Python script can't be used directly, we need to define a function interface in configuration file, which is more troublesome.

https://www.pyxll.com/

XLWings

XLWings is a Python-based interactive computing add-in that uses third-party library as its computing engine. When using XLWings, we need to write Python code in IDE (web page form). To be specific, we need to read Excel cell (or a range of cells) first, and then perform calculation, and finally assign the calculation result to Excel cell (or a range of cells). The calculation process is highly interactive, but very inconvenient as it requires operating with code throughout the entire process. In short, this add-in is powerful in computing ability, inconvenient in installation and configuration and medium in computing ability.

The advantages of XLWings include that it provides a large number of computing functions, such as function for array or set data, and function for grid data, many of which have more powerful computing ability than native Excel functions; it implements calculation in Python script, the flow processing ability is relatively powerful.

The disadvantages are: before installing, we need to configure Python environment, and install the function libraries such as Numpy and Pandas, and almost all these preparations need to be done at command line, which involve many steps and are technically demanding; the calculation code of XLWings is shorter than VBA code, but it requires operating with code throughout the entire process, including data read/write before and after calculation, and hence it is a very cumbersome process, and requires high technical skills.

https://www.xlwings.org/

OpenPyXL

OpenPyXL is a Python-based add-in that uses third-party library as its computing engine. When using this add-in, we need to follow the steps: write a Python script, read Excel cell in the script, perform calculation, write the result back to the cell, and execute the script. This add-in is quite powerful in computing ability, very troublesome in installation and configuration and medium in technical requirements.

Python provides a large number of computing functions, such as functions for array and grid data, many of which have more powerful computing ability than native Excel functions; OpenPyXL is a Python-script-style add-in, having powerful flow processing ability; Python code is shorter than VBA code, and requires relatively low technical skills.

The disadvantages are: before installing, we need to configure Python environment and third-party libraries such as Numpy and Pandas, and almost all of these preparations need to be done by manually typing commands, which involve many steps and require high technical skills; OpenPyXL doesn’t allow us to write a calculation code directly in a cell, but write a Python script, which is inconvenient and technically demanding.

https://openpyxl.readthedocs.io/en/stable/

Bert

Bert is a R language-based Excel add-in, and uses the computing engine that comes with R language to perform data calculation. When using Bert, we need to write a custom function in R language first, and then call this function in a cell, the operation process is less convenient. This add-in is medium in computing ability, less convenient in installation and configuration and higher in technical requirements.

The advantages of Bert include that it provides a large number of computing functions of R language, most of which are used for vector or row-column data, and some of which are used for grid data, and some functions are more powerful than native Excel functions in computing ability; since R language can utilize third-party library functions, such as data.table, the computing ability of Bert can be further improved as long asthese libraries are downloaded and deployed in advance; although R is not as concise as Excel formula, R code is shorter than VBA code, and the technical requirement is not as high as that of VBA; Bert provides a convenient prompt window when entering parameters, making it quite convenient to enter Excel expression.

When it comes to its disadvantages, although Bert itself supports one-click installation, we need to install and configurate R environment first, this process is fairly troublesome; since Bert doesn’t support entering an expression directly in a cell, we have to follow the steps: create a custom function in R language first, and then define Excel interface, and next specify the parameter, and finally call this custom function in a cell, this process is cumbersome and technically demanding.

http://bert-toolkit.com/

JADE

JADE is a JavaScript development environment built in Excel, and supports using the third-party function library as computing engine. When we use JADE, we need to write and execute JavaScript script in Excel, which is similar to that of VBA. JADE is inconvenient in installation and configuration, medium in technical requirements and below-medium in computing ability.

JADE itself is quite easy to install, supporting one-click installation, we can install it through the add-ins store built in Excel. However, if we want to improve its computing ability and development efficiency, we need to install the third-party function libraries, such as Danfo-jsor Pandas-js, otherwise the amount of code is large, and it has no advantage over VBA. The installation of third-party library can only be done manually, the installation process involves many steps, and is complex and technically demanding. Although the third-party library provides more computing functions, including functions for array and grid data, these libraries are not as mature as professional library, the computing ability is insufficient, and the code is still complex.

JADE has the flow processing ability, and can implement complex business logic.

https://appsource.microsoft.com/en-us/product/office/wa200003637?tab=overview

Kutools

Kutools is a set of add-ins that enhance the ease of use of Excel, allowing us to use wizard to generate simple expression, and providing us with more convenient operation windows. This set of add-ins is relatively convenient in installation and configuration, low in technical requirements and quite weak in computing ability.

Kutools supports one-click installation with few steps, and without having to type manually and resorting to any third-party platform or component. Kutools performs calculation through wizards and operation windows, and has detailed help instructions and requires very low technical skills.

The disadvantages of Kutools are: although it provides over 100 expression wizards and operation windows, and the number is large, most of them are the simple combination of native Excel functions, the function is not powerful, and there are many restrictions in use; Kutools can only solve specific problem and doesn’t allow us to write expression freely; most of functions are used for array, and only a few are used for grid data, thus the computing ability is weak, and doesn’t surpass that of Excel itself; Kutools doesn’t support script and flow processing, resulting in failure to perform more complex calculations.

https://www.extendoffice.com/download/kutools-for-excel.html

Ablebits Tools

Ablebits Tools is a set of add-ins that enhance the ease of use of Excel, allowing us to use wizard to generate simple expression, providing us with more convenient operation windows, and eliminating the need to code throughout the entire process. This set of add-ins is relatively convenient in installation and configuration, low in technical requirements and very weak in computing ability.

Ablebits Tools supports one-click installation with few steps, and without having to type manually and resorting to any third-party platform or component. It performs calculation through wizards and operation windows, and has detailed help instructions and requires very low technical skills.

The disadvantages of Ablebits Tools are: it provides a dozen of expression wizards and operation windows respectively, the number is not large, and there are many restrictions in use; it doesn’t allow us to write expression freely; although the wizards and operation windows are available for both array and grid data, the computing ability is weak, and doesn’t surpass that of Excel itself; this set of add-ins doesn’t support script and flow processing, and cannot perform more complex calculations.

https://www.ablebits.com/downloads/index.php

SeoTools

SeoTools is composed of search engine management interface, data analysis interface and various functions. Among these functions, over 30 are computing function. When using SeoTools, we need to enter expression in a cell and refer to SeoTools function, the operation process is convenient and natural. In short, this add-in is convenient in installation and configuration, low in technical requirements, less powerful in computing ability, and only supports the old version of Excel.

SeoTools supports one-click installation with few steps and high automation, and without configurating manually and resorting to any third-party platform or component before and after installation; SeoTools allows us to enter an expression directly in a cell, the code is a short and easy to read; the operation process is similar to that of native Excel function, and requires low technical skills.

The disadvantages of SeoTools are that it provides few computing functions, and lacks systematic nature, and cannot provide comprehensive computing ability; its functions are mainly used for array, only a few functions are used for grid data, and hence it is insufficient in computing ability; it lacks the script and flow processing abilities, and cannot implement more complex calculations.

https://seotoolsforexcel.com/download-seotools/

Essential Excel Add-In

Essential Excel Add-In is a set of add-ins that enhance Excel, which is composed of operation windows and custom functions. When using it, we need to enter expression in a cell and refer to custom function, the operation process is convenient and natural. This set of add-ins is convenient in installation and configuration, low in technical requirements, relatively weak in computing ability.

Essential Excel Add-In supports one-click installation with few steps and high automation, and without configurating manually and resorting to any third-party platform or component before and after installation; it allows us to enter an expression directly in a cell, the code is a short and easy to read; the operation process is very convenient and similar to that of native Excel function, and requires low technical skills.

Essential Excel Add-In only provides 16 computing functions, and thus the computing ability is quite weak; all functions it provides are used for array, and no function is used for grid data; it lacks the script and flow processing abilities, and cannot implement more complex calculations.

https://sourceforge.net/projects/essenexceladdin/