4

In Excel it is straight forward to apply a filter on a column (mark the region of interest, klick on data>filter). This way you get the handy option to show the content corresponding to, e.g., a value by a "simple click".

For example, say a I have the weekdays repeating over and over in one column and various data in the other columns. By the filtering described above, I can, with a click, show rows corresponding to, say, mondays only.

If we call the filtering described above vertical, how do I accomplish horizontal filtering in the same simplistic manner?

It should be very simple, but I cannot find any way to do this. Annoying!

2

3 Answers 3

4

Another way would be to use Transpose function in an array form. Steps as follows:
1. Select your destination area first.
2. In the formula box type following: "=Transpose(source)"
3. Instead of hitting enter, you must hit ctrl+shift+enter.

What step 3 does is create an array type that links to your source data. This is similar to copy/paste link except it transposes the data, at which point you just filter as usual.

If done properly your code may look like this:
{=TRANSPOSE(A1:Z100)}
In every single destination cell.

0
1

I find the answer to my own question to be: there is no simple way of filtering horizontally with Excel. The (as of now) two answers shows how to go about — and they clearly shows that you have to reshape the data.

1
  • 1
    As of Jan 2021, this is still true. You either have to reshape the data, or implement arcane pivot-table tricks that won't be intuitive to use for end users of the spreadsheet. Jan 22, 2021 at 11:48
0

I have uploaded a video showing exactly how to accomplish horizontal filtering.

https://www.youtube.com/watch?v=icpLC3CqMUs

Hope this helps.

Regards Irfan

1
  • 3
    While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. Aug 14, 2017 at 15:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .