Python has finally become part of Excel, and from now on you can add pieces of Python code to a sheet to define datasets, process and visualize them. If you have a Microsoft 365 subscription and have downloaded update 2409 (Build 17628.20164), you will be able to use Python normally (no longer reserved for testers only). In this short intro you will learn what Python is and how it can enrich your reports.
Excel has a large number of conversion functions. This text will show the ones used to convert lowercase letters into uppercase, uppercase to lowercase, and capitalize the initial letters of each word in the given cell. We will also mention two features that are used to clean the text, either by removing excessive spaces or unprintable characters that often exist in old applications and end up in the table when retreiving data.
When someone tells you, “I’m left without text” this usually means a state of shock and confusion. Ignoring text related functions will not leave you in shock, but you will certainly be deprived of some useful tools. These functions are numerous, and in the “recipe” that follows I’ll describe these that allow merging of two or more texts into one, converting text to number or number in text, determining the length of text …
In practice, it often happens that we have tables that are in a mutual relationship. Similar to the database, the value of a table column column may be a key by which we can access the columns of another table. In this text, we will discuss the functions used to search through data ranges. If you master them, it’s quite certain that this will quickly become one of your favorite Excel tools.
Data aggregation represents a mathematical operation (summarization, counting, averaging) that we perform over a set of data. Conditional aggregation, as its name implies, is performing data aggregation over a set of data that meets certain condition which is contained within a given data range. In this “recipe” we’ll cover functions that use single condition for performing aggregation.