How to Filter Data Horizontally in Excel

Filtering data horizontally in Excel is not as straightforward as filtering data vertically using the built-in filter feature. However, you can achieve this by using a combination of the TRANSPOSE function and the regular filter feature. Here's how to do it:

1. First, make sure you have enough empty space in your worksheet to accommodate the transposed data. You will need a number of empty rows equal to the number of columns in your original data and a number of empty columns equal to the number of rows in your original data.
2. Click on a blank cell where you want to start the transposed data. This cell should be located in the empty space you prepared in the previous step.
3. Type the formula =TRANSPOSE(range) in the selected cell, where "range" refers to the range of cells containing the original data that you want to filter horizontally. For example, if you want to transpose data from cells A1 to C5, the formula would be =TRANSPOSE(A1:C5).
4. Press Ctrl + Shift + Enter to enter the formula as an array formula. Excel will automatically surround the formula with curly braces {} to indicate that it's an array formula.
5. The selected range will now be filled with the transposed data. In our example, the transposed data will be displayed in the range D1:H3.
6. Select the entire range containing the transposed data, and then click on the "Data" tab in the Excel toolbar.
7. Click on the "Filter" button in the "Sort & Filter" group. Excel will add filter arrows to the top row of the selected range.
8. Use the filter arrows to apply the desired filters to the transposed data. The filtered data will be displayed horizontally.

Example

Let's say you have the following data in cells A1 to C5:

Product  Sales  Profit
A        100    50
B        200    75
C        150    60
D        175    80
E        250    100

You want to filter the data horizontally based on the "Sales" column.

1. Select an empty cell, let's say E1, and type the formula =TRANSPOSE(A1:C5). Press Ctrl + Shift + Enter to enter it as an array formula.
2. The transposed data will appear in the range E1:G6 as shown below:

Product  A      B      C      D      E
Sales    100    200    150    175    250
Profit   50     75     60     80     100

3. Select the entire range containing the transposed data (E1:G6) and click on the "Data" tab in the Excel toolbar.
4. Click on the "Filter" button in the "Sort & Filter" group.
5. Click on the filter arrow in cell F1 (Sales) and apply the desired filter. For example, you can filter the data to show only the products with sales greater than 150.
6. The filtered data will be displayed horizontally as shown below:

Product  B      D      E
Sales    200    175    250
Profit   75     80     100

Remember that the filtered data is linked to the original data, so any changes made to the original data will also be reflected in the filtered data.