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

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

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

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

Hope this helps.

Regards Irfan

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy