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…
