Writing Excel formulas with the help of Name Manager can be a demanding job. When you write simple formulas, you will still somehow manage. With complex formulas or changing existing ones, there is a high chance that you will make a mistake. The LAMBDA feature allows you to create your own functions as names added in the Name Manager, and to make this job easier, Microsoft has created an Excel add-in for creating complex formulas: Advanced Formula Environment.
So far, you have been able to read several “recipes” on this blog which were about Data Validation and lists. Office365 (Microsoft365) subscription users have access to dynamic array functions that easily handle variable-length lists. But what should users with an older version of Excel do? In the text that follows, you will learn how to solve this problem with the help of the named ranges, OFFSET and COUNTA functions.
When creating tables, we often use our own markup, e.g. change the cell background color of the data we want to indicate in some way. How to automatically count them later? If we applied a filter or these cells are within a structured table, it is enough to filter the table by color, so that we can use SUBTOTAL function to find out how many cells are colored in a certain way. Can this also be done using COUNTIF?
Although each cell has its own address, so we know how to find the data it contains, sometimes it would be good if we can access the cell using the term that determines its contents more closely. With this purpose, Excel has the ability to designate a cell or group of cells. The names thus created can be used as operands in formulas, function arguments, or to enable us to selectively select a cell or group of cells.