Best Way to Filter on Multiple Columns?

**Samuel K** · 02-13-2012, 10:12 AM

I have an Excel project that contains a bit over 100 columns. I need to be able to filter this data in various ways, although it will all be dependent on whether or not various columns have a value of 'X' or not. (Most of the columns have an 'X' where an option is available and is blank where the option isn't.)

What would be the best way to go about doing this? Advanced filters? Pivot tables? Something with VBA? My Googling has produced multiple ways to do what I need but I'm not at all experienced with Excel (at least, not since Office '97) and aren't sure what the best way to go about this would be.

**raystafarian** · 02-13-2012, 10:49 AM

I would use advanced filters and sorting, but that doesn't mean it's better than a pivot table. Do what's most comfortable to you.

**arlu1201** · 02-15-2012, 06:29 AM

I need to be able to filter this data in various ways, although it will all be dependent on whether or not various columns have a value of 'X' or not.

What do you mean by this? Are you talking about having dropdowns and then filtering the data based on what is selected in the dropdowns?

You could use a pivot or you could use some advanced formulae or even some VBA.

**Samuel K** · 02-16-2012, 01:21 PM

Originally Posted by arlu1201

What do you mean by this? Are you talking about having dropdowns and then filtering the data based on what is selected in the dropdowns?

Each row represents a product type. Each column represents a location. If a product is available at a particular location, there is an X in the corresponding cell. Sometimes I might need to filter out anything not available at a particular location. Other times I might need to filter out items that are available at any location, thus leaving a list of products that aren't currently available at all. Sometimes, it might be necessary to filter based on products being available in certain locations but not others.