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.
We will start with a simple table report in which you can see sales of several wines during the four quarters of one year. To create an option for selecting criteria, the types of wines whose sales we want to show in the chart, we will use the Data Validation option and make a list in which to select one of the proposed wines.
Then in cell B9 you need to write the following formula:
This formula, based on the selected value from the list (cell A9) searches column A and returns the sales values for the found wine in four quarters. If no data is found, the function returns the message “No results”, which can only happen at the beginning, if you have not selected any item from the list. For a better view, select all sales values and format them as a real number with two decimal places (CTRL + SHIFT + 1).
Next, select the range A9: E9 and create a Clustered Column chart. Place labels that represent quarters on the x-axis, and then format the chart at your discretion. Each time you select a different value from the list, located in cell A9, the chart will be updated automatically and you will see the sales values for the selected wine.