Filter by column, sort by row

Note: FILTER is a new dynamic array function in Excel 365. In other versions of Excel, there are alternatives, but they are more complex.

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:

1. Filter to select the Group and matching Year column
2. Sort the result in descending order by year values

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:

=FILTER(B5:G15,B4:G4=2017)

The logical expression:

=B4:G4=2017

returns a one-row horizontal array with 5 columns:

{FALSE,FALSE,TRUE,FALSE,FALSE,FALSE}

When provided to FILTER as the include argument, FILTER returns the values for 2017 only:

FILTER(B5:G15,{FALSE,FALSE,TRUE,FALSE,FALSE,FALSE}) // 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:

=(B4:G4="group")+(B4:G4=2017)

This results in two arrays with TRUE and FALSE values, joined by addition:

{TRUE,FALSE,FALSE,FALSE,FALSE,FALSE} +
{FALSE,FALSE,TRUE,FALSE,FALSE,FALSE}

The math operation of addition coerces the TRUE and FALSE to numbers, and the result is a single array of 1s and 0s:

{1,0,1,0,0,0}

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:

=SORT(filter_result,2,-1)

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.