Today, I’ll show you how to filter horizontally in Microsoft Excel.

The default tool used in Microsoft Excel to sort and filter data works along the vertical columns. But can you filter horizontally in Excel using the same tools? That’s a big no because the Sort & Filter tool doesn’t allow that. So, here steps in the expert Excel data filtering skill to sort horizontally in this popular spreadsheet app.

Follow along with the horizontal filtering methods and workarounds outlined in this guide to find the best-suited approach for your dataset. Let’s begin!

Using Custom Views

Microsoft Excel doesn’t offer any command or tool to filter horizontally. You must use workarounds, like using the Custom Views command. It captures a quick snapshot of the active worksheet. It can efficiently save hidden rows or columns, print settings, cell styles, formatting, and so on.

All you need to do is save the worksheet as different views with unique names as you slice and dice your dataset. Now, without actually changing the structure of the dataset, you can use the saved Custom Views to go back to different views for the same worksheet.

Suppose your dataset looks similar to the above dataset. You wish to apply a horizontal filter in the ID row header to uncheck all IDs except the X123 ID.

Firstly, click on the Custom Views command in the Workbook Views block of the View tab in the Excel ribbon menu.

The Custom Views dialog box will open. Click on the Add button.

The Add View dialog box will open. Click on the Name field and type in a name for the full table view, like Full Table.

Click on the OK button to save the view.

Now, hide all the columns from D to F to keep only column A.

Now, again bring up the Custom Views dialog box, click on the Add button, and type in a name for the modified view of the original table. For example, you can call it First Column.

Click OK to save the view.

To swap through the views, bring up the Custom Views dialog box and select a saved view from the list given. Now, click Show to switch to a different view for the same worksheet.

Using the FILTER Function

You can use the FILETR function to actually filter datasets horizontally instead of creating snapshots, like you’ve seen in the previous method. This is an array formula. It returns an array of values that meet a specified condition. When entered in a single cell, it automatically spills results into adjacent cells.

Let me walk you through below how to use this function using a number or a text string to filter data horizontally.

Use FILTER With a Text String

Suppose you’ve got business expenses budgeting data, as shown in the above screenshot.

Let’s say you want to filter by a text string entered in the Dept row in the dataset.

Create another row header named Dept and keep its adjacent cell empty.

Copy and paste the row headers from the source dataset below the source table or in a new worksheet.

Select the adjacent cell to the Region row header and enter the following formula into it:

 =FILTER(B1:E4,A2:E2=B6,"N/A")

Find below a quick explanation of the formula syntax and its arguments:

• array: The cell range reference B1:E4 is the array where the function will apply the filter. When working on your own dataset, change the reference accordingly.
• include: The formula understands which item to display after filtering through the cell range reference A2:E2=B6. This is translated to Dept > Sales in the filtered dataset.
• [if_empty]: The filtering configuration will show an error message when the formula doesn’t match any include criteria. To avoid that, add a custom message in this argument.

Now, type in the parameter by which you wish to filter the source data table. For example, you can use Sales.

When done configuring the formula syntax, hit Enter to execute the formula.

Excel will filter the data table and only show the allowed values.

If the formula doesn’t calculate the values after pressing Enter, try pressing Ctrl + Shift + Enter. This should fix the issue.

Sometimes, the array formula might still not work. Go to the Formulas tab and click on the Calculation Options drop-down menu in the Calculation block.

Select the Automatic option if it were Manual or Partial.

If the current selection in the Calculation Options menu is Automatic, switch to Manual and select Automatic again to force Excel to calculate all formula cells in the worksheet.

Use FILTER With a Number

Filtering a dataset horizontally is also possible using a number as the reference in the FILTER function. Let me show you below how:

In the same data discussed above, suppose you’d like to filter by any of the values in the Estimated Budget row.

So, create a row header named Estimated Budget and keep the adjacent cell to the right empty. In this cell, you’ll later enter the value of the Estimated Budget row header by which you wish to filter the source dataset horizontally.

Create these row headers separately where the filtered table will be populated:

• Region
• Dept
• Estimated Budget
• Actual Cost

Click on the blank cell to the right of the Region row header and enter the following formula into it:

=FILTER(A1:E4,A3:E3=B6,"N/A")

The formula arguments are the same as the one described earlier for the FILTER function above in this Excel tutorial.

In this specific formula, the difference is the cell range reference A3:E3=B6, which refers to one of the Estimated Budget values from the source data table.

Hit Enter to calculate the FILTER function.

Excel should now horizontally filter the source table according to the number entered in the Estimated Budget row.

Using the TRANSPOSE Function

You can use the TRANSPOSE function to quickly change the horizontal table into a vertical table for easier sorting and filtering using the Sort & Filter tool.

Suppose your input dataset looks like the one shown in the screenshot above.

Select a destination cell where you’d like to transpose the horizontal table into a vertical table.

Enter the following formula into the selected cell and hit Enter:

=TRANSPOSE(A1:E4)

In the above formula, you only need to change the cell range reference according to the input dataset that you want to transform into a vertical table.

Select the column header row and press Ctrl + B to apply a bold typeface style to distinguish the header row from the data rows.

Now, press Ctrl + Shift + L to activate the Sort & Filter feature for all the selected columns in the newly created table.

Click on the Sort & Filter drop-down button on any of the columns and choose your filters from the context menu that shows up.

Hit the OK button at the bottom of the Sort & Filter drop-down menu to apply the changes you’ve made.

Conclusions

Now you know how to filter horizontally in Microsoft Excel using various methods.

You can start with the Custom Views method, which doesn’t require you to use any Excel function. The method is easy to use and follow. Once you create a view, you can reuse it as many times as you want in the same worksheet.

If you’re looking for a bit of an automatic method, then start with the FILTER function. It’s suitable for filtering the source dataset by both a numerical value and a text string.

Finally, if you’d like to transform the horizontal dataset into a vertical one for easy sorting and filtering, you can go ahead with the TRANSPOSE function.

Did you find this Microsoft Excel tutorial useful? You can share it with your friends, colleagues, and family members to help them excel in their data analytics skills in Microsoft Excel. If you’ve got any suggestions or feedback, please use the comments box given below.