This formula returns the “Group” column plus data for the year in J4, sorted in descending order by the values in that year. The year in J4 is a dropdown menu created with data validation. In this example, the goal is to filter the data shown in B5:G15 by year, then sort the results in descending order. In addition, the result should include the Group column, sorted in the same way. The problem breaks down into two main steps:
Filter by column
To filter the data to select the Group column and data for the matching year, we use the FILTER function. Typically FILTER is used to filter data vertically, selecting rows that match provided conditions. However, FILTER can also select data horizontally. The key is to provide logic for the include argument that will return a horizontal array with the same number of columns as the source data. For example, to return data for the year 2017, we can use a formula like this: The logical expression: returns a one-row horizontal array with 5 columns: When provided to FILTER as the include argument, FILTER returns the values for 2017 only: To add in the Group column, we extend the logic using Boolean logic, a technique for working with TRUE and FALSE values as 1s and 0s. In Boolean algebra, multiplication corresponds to AND logic, and addition corresponds to OR logic. In this case, we want FILTER to return the Group column and the matching year column. This means we need OR logic - i.e. column = “group” OR column = [year]. Using addition for OR logic, we can construct an expression like this: This results in two arrays with TRUE and FALSE values, joined by addition: The math operation of addition coerces the TRUE and FALSE to numbers, and the result is a single array of 1s and 0s: Notice the first and third columns are 1, while the other columns are 0. When this array is provided to FILTER as the include argument, FILTER returns columns 1 and 3 from the data.
Sort by row
Because the FILTER function is nested inside the SORT function. FILTER returns the two matching columns explained above directly to SORT: We want to sort these columns by values in the year column (2017) in descending order, so sort_index is provided as 2, and sort_order is given as -1. With these inputs, the SORT function returns the sorted as shown in the example. Notice that Group E appears first since 27% is the highest value in 2017. When the year in J4 is changed, FILTER selects new columns, and the SORT function sorts the new data in the same way.
Dropdown menu for year
To make the year dropdown menu, you can apply a simple data validation rule to cell J4. The allowed values are based on the existing years in C4:G4, with In-cell dropdown selected:
Once data validation is in place, a dropdown menu with the years 2016-2020 will appear. If you are new to data validation, see our Data Validation Guide.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
