Few Excel users know about array formulas and I believe they have found themselves in a situation where they need them at least once. They were mentioned in one of the previous “recipes”, and here you will be able to learn how to use them in combination with constants. Constants are values inside curly braces, separated by a comma (or semicolon), and we use them for arithmetic operations within an array formula.
The Data Table is an old and somewhat neglected Excel option that allows you to see the results of applying different scenarios within a table. For a given formula and criteria, it returns alternative values of that formula. Criteria can be drawn from rows or columns, and there is the possibility of a report containing two groups of criteria. This is perhaps a better tool than a scenario if we perform simpler analyzes …
How to show on the chart whether the goal has been achieved? If we use the Column Chart for this purpose, the simplest way is to add a line that intersects the y axis at the point that corresponds to the value of the given target. We can do this manually, as well as add a series of data that has only one value – the goal, which could be shown in the report using the Combo chart. And we can also create a goal achievement chart by moving the y axis, which will be discussed here …
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.
Traditional Excel has only two data types: text and numbers. All others (date, time, percentage, etc.) are specially formatted numbers. In the second half of 2019, Microsoft added new data types to Excel: Geography and Stocks. First, for a given term, offers an abundance of geographical determinants while the second provides data on stock market. A lot of new types have been announced, and since recently users of Microsoft 365 subscriptions can create custom data types.