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.
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.
Excel has, within the VBA Editor, a form design tool that helps you easily fill in structured data tables. However, some of you still do not know the VBA and are unable to create the form yourself. Some others will find themselves in the situation that their job is overwhelmed by entering data one time. For all of you, in the “recipe” that follows, I will show how to enter data in a table using generic Excel forms.
Excel offers you to add controls to documents, the same elements that you can find in windows and dialog boxes, and they enable choosing values, checking options, adding labels, choosing from list objects… Adding control to a worksheet increases interactivity between user and reports. In this way, you can easily create different polls and save the results in a form suitable for further processing.
Although it has not been initially made as a translation tool (let’s face it, most of us use Google Translate) Excel has several features that allow you to translate table names from one language to another, check the spelling and grammar, and, if possible, replace one of the labels with a synonym. It’s rare for anyone to know that these options exist, or at least never used them, so it is not bad to mention them.