Pipe Filter with Excel | Alternative 2

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.

Comment List
  • Hi Mohana,

    I find it faster to use =A2 in field B2, followed by =A3&"|"&B2 in field B3.

    You can then extend the formula to all consecutive rows by clicking in field B3 and double-clicking the lower right corner.

    Don't forget to copy the last row and "Paste as value" to get actual filter usable in NAV.