How to Filter Horizontal Data in Excel (3 Methods)

This article explains three methods to filter horizontal data in Excel. Filtering data vertically is easier with the default Filter feature, pivot table, and some other tools. But to filter data horizontally needs to follow some techniques and new functionalities into action.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Methods to Filter Horizontal Data in Excel

In this article, we’ll use the following dataset. The dataset contains sales data for 8 products that fall into 3 different categories. We will discuss 3 suitable methods to filter this dataset based on categories.

Excel Horizontal Filter


1. Use of the FILTER Function to Filter Horizontal Data in Excel

The FILTER function can perform filter data horizontally easily based on predefined criteria. This function can filter data both vertically and horizontally.

Introduction to the FILTER Function

Syntax:

=FILTER(array, include, [if_empty])
Arguments:

Argument Required/Optional Explanation
array Required Range of data to be filtered.
include Required A Boolean array has an identical height or width to the array.
if_empty Optional If the criteria don’t match outputs a predefined string.

Now, in our example, we are going to filter the dataset based on three different categories i.e., Fruit, Vegetable, and Fish. Let’s follow the steps below.

Steps:

  • In cell C10, we put the category name “Vegetable”. We’re going to use this as the criteria to filter the dataset. And we also created an output table to store the filtered data.

Excel Horizontal Filter

  • In the cell, C12 put the following formula.
=FILTER(C4:J8,C5:J5=C10, "Not Found")

▶ Formula Breakdown

The FILTER function takes two arguments- data and logic.

  • In this formula, cells C4:J8(Blue colored box ) represent data to be filtered. The cells C5:J5 in row C are the categories in the red-colored box from where we set the criteria.
  • In the formula, C5:J5=C10 checks the value of cell C10 against each of the cell values of C5:J5. This returns an array, {FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, TRUE}. We see that TRUE values are for cells with the category vegetable.

The formula gives a dynamic solution. It means whenever we change cell data the output is going to adjust its value immediately.

Excel Horizontal Filter

  • The result shows only the columns with the category Vegetable.

Excel Horizontal Filter

  • In this step, we changed the value of cell C10 to Fruit, and the data filtered horizontally for that category accordingly.

Excel Horizontal Filter


2. Transpose and Filter Horizontal Data in Excel

We can transpose our dataset and then use the default filter option that Excel provides to filter horizontal data. Let’s dive into the following example!

Steps:

  • At first, select the whole dataset, press Ctrl + C with your keyboard, or rightclick the mouse to choose copy from the context menu.

  • We need to paste the copied dataset with the Transpose option. Select the cell where you want to paste In this example, we selected cell B10, and then from the Home Tab click on the Paste tab to select the Transpose as paste option.

Excel Horizontal Filter

Another Way:

Open up the Paste Special window either from the context menu or from the Home tab. From the Operation options, click the Transpose checkbox and hit OK.

  • Now, select the transposed dataset and from the Data Tab click on the Filter option.

Excel Horizontal Filter

  • The above steps enabled filtering options on each of the columns. Click on the Category Filter option and check the Vegetable.

Excel Horizontal Filter

  • This is the output we got.

By following the above steps, we can filter the dataset based on any criteria.


Similar Readings


3. Create Custom Views to Filter Data Horizontally in Excel

In this method, we are going to filter horizontal data with the help of  Excel’s Custom Views. We’ll create a number of custom views depending on our criteria. We want to filter data based on the product category. So we need to create 4 custom views in this example. Necessary steps are given below.

Steps:

  • At first, we are going to create a custom view with the full dataset. Go to the View Tab in the Excel Ribbon and then select the Custom Views option.

Excel Horizontal Filter

  • In the Custom Views window click on the Add button.

  • We put Dataset in the input box as the name of the Custom View and hit

Excel Horizontal Filter

  • Now, to create a custom view for the Fruit category, hide all the columns other than the Fruit category. Select the columns E, F, H, I, and J that have data for Vegetable and Fish

Excel Horizontal Filter

  • After that, rightclick on the top of the column bar and choose Hide from the context menu.

  • As a result, all the columns other than the Fruit category are hidden.

Excel Horizontal Filter

  • Now, add a custom view named Fruit for the Fruit category.

Excel Horizontal Filter

  • Similarly, add another two custom views for the Vegetable and Fish categories named Vegetable and Fish. Finally, we have created 4 custom views.

  • Now, we can select any of the custom views from the list, and clicking the Show button will show the view for that corresponding product category. For example, we selected the Fish Custom View to show filtered data for the Fish category.

Excel Horizontal Filter

  • Here is the filtered dataset for the Vegetable category.

Excel Horizontal Filter


Notes

  • The FILTER function is a new function that can only be used in Excel 365. It is not available in the older versions.

Conclusion

Now, we know how to filter data horizontally in Excel. Hopefully, it would encourage you to use this function more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Further Readings

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo