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.
In the “recipe” Simple sales analysis, you could learn how to get to the value that is in the intersection of the row and column of the sales report. On that occasion, we used the INDEX and MATCH functions. The new XLOOKUP function allows this analysis to be performed in a much simpler way, by combining two instances of this function. Let’s see how it’s done!
Filtering affects the appearance of a report by setting certain criteria. If we create a pivot chart, it will be formed based on the data from the pivot table. If we change something in it, the chart will also change. Is this possible to do in traditional Excel reports? Yes, if you have an Office 2021 or Office 365 subscription, with the new XLOOKUP function. This is a simple method by which you can get effective reports.
Do you like Visio? It is an application that essentially belongs to the Microsoft Office, but is purchased separately. I used it to draw diagrams, but the number of objects it offers indicates that it is applicable in many activities where some type of design is performed. Recently, there is an add-on that allows you to easily create Visio diagrams within an Excel document. It’s called Data Visualizer. Let’s see how it is used!
This is a function that is used to aggregate – group data from a given range, which is done by specifying a parameter that calls a specific function (SUM, AVERAGE, COUNT, MAX, MIN, etc.). We can also perform aggregation using other functions, but this one is specific in that it offers various options that allow you to ignore nested functions, hidden rows, errors …