Power BI visualizations often have a Tooltip section with which is a feature that enables focus on specific attributes. For example, if you hover an item shown in the report, you can see information about the manufacturer, number of brands, average price, etc. Something like this does not exist in Excel, but if we can mark items in some way, it is possible, with a little improvisation, affect the display of attributes. To do this, we will use the Checkbox option…
Arranging data sets in Excel reports can sometimes get complicated, for example when you are editing a list of visitors to your stand at a business fair or congress. Then we usually manually copy the lists, merge them, and further remove duplicates. Excel has some new functions that allow you to literally do this procedure in a couple of steps, and in the following “recipe” you will find out how to apply them in practice.
Imagine this scenario: you register customers in an Excel spreadsheet, specifying their ID and then the products they use. Which customer uses which products? One way to get this answer is to create a pivot table, and then display customers, products and the number of products. The disadvantage of this report is that pivot tables always aggregate data. What if we only need a table with customer ID in one column and a consolidated list of products in another?
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.
The FILTER function can also contain multiple criteria, and if we want to consider them all at the same time, they must be in parentheses, separated by an asterisk. This is a convenient way to filter sales results by date, and set the criterion by changing the year and month values from the drop-down list we have created by using Data Validation option.