It has been a few years now that analytics is moving away from Excel, dashboard get built in tools such as Tableau, calculations get done in Jupyter notebooks or in OLAP type of cubes.

Excel none-theless is slowing moving towards being a full fledge analytics workplatform, not meant by anymeans to support large scale datasets but to be able to cather to small and medium size in a very quick and extensive manner.

Below, some of the core power features that make excel really efficient and extensive

SUM Product/Array functions

Sum Product and arrays functions allow for the use of more complex datatypes than what is traditionally used with excel. It offers a native way to do operations based on input contained across multiple cells. This allows notably for doing condition based filtering operations, eg average all the values if their row matches some conditions.

Wildcards ? *

Wild cards can be used to generate computation based on partial matches, it is not an as extensive mattching algorithm than a regexp but does sort out all the simple matching use cases — Wildcards documentation

VBA

VBA is the most natural way to extend the functionalities offered by default in Excel, it is a full programming language with which you can define your own functions (UDF), call and update different cells or other Excel entities, make external queries through WinSock … It does suffer from being a fairly clunky programming language but significantly helps automate work being done in excel and to extend its functionalities

Powerpivot

Powerpivot allows for easy slice and dice of dataset, it allows to build pivot table without being constrained by the size limitation of traditional excel. Data is loaded in memory so it does have its challenges but handliing medium size datasets with it is certainly feasible.

Power Pivot

SQL on Excel

You can of course query external data in a database with excel using an ODBC or ADO connection, but what is a bit less known is that is is also possible with some work to do a direct connection to an excel workbook and query the data contained within it,

Excel Automation

Excel Workbooks can be to a certain extent used to provide standard reports that can easily be shared within an organization. In the past I have setup pipelines based on Excel templates and pieces of code using openpxl to provide different types of report automatically. behind the hood the excel workbook format is based on XML and editing or injecting data in it is extremely doable.

JavaScript Functions in Excel

Excel now supports Javascript custom functions, allowing you to write proper code to handle the way operations are setup in your Excel workbook. You can run specific functions when a specific state changes in your workbook. This setup allows for an extremely quick way to setup some application with an already built in number oriented front-end.

Excel JS Documentation / Excel JS Documentation 2

Python On Azure For Excel

Microsoft just started the process to support python through Azure connections on Excel, this is meant to support use cases such as running machine learning models based on data contained in an Excel Worbook. Previously there was some integration with Python possible through third party plugins such as XLwings which was used to mostly automate process, and handle external connections, now the main purpose has shifted to leveraging the power of big data.

Python On Azure Announcement

Power BI Custom Visuals

Microsoft announced that they are now supporting the creation of custom visuals within Excel, significantly improving the type of visualization supported by excel.

Custom Visual Announcement

‍