I looked at the video by Kerry Rosvold on how to make a pipe filter in excel here and MVP Mark Brummel’s alternative here . Both are awesome.
I have also an alternative way.
So this is how I do it.
You copy a few lines to excel like this:
And in Excel you paste, and Remove all other columns except No.
Add pipe (|) in B1 cell and =A1 in B2 cell and Enter
Add below formula in B3 cell and Enter
Now it looks like
Re Select B3 Cell and select B1 value and Press F4 (which makes Pipe value Constant)
Now formula in B3 looks like
B1 is changed as $B$1
Copy B3 cell and paste in all below cells
The last row has the final filter.