Version History is a great feature that can be used by all Microsoft 365 subscribers who store files on a SharePoint or OneDrive repository. Storing files in the cloud brings a lot of benefits, one of which is the overview of all versions and the ability to restore a previous version of an Excel document that was worked on by one or more users.
So far, you could find a lot of texts on the web site “Excel Kitchenette” about data validation. Most of them were about lists, since this functionality is often used, and further the choice of data type, setting the text length, number range or date were mentioned … When activating the Data Validation option, there are also custom rules, where it is possible to enter a formula based on which the validation is being performed.
We often come across Excel documents that have multiple reports found in several worksheets. If you want to find the report you want, click on the tab with the name of the corresponding worksheet. However, this is not always the optimal solution. In order to facilitate positioning on the desired report, we introduce links that we have attached to certain forms. That is, we introduce a navigation panel to navigate within the document.
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.