Filtering issue

%3CLINGO-SUB%20id%3D%22lingo-sub-1680044%22%20slang%3D%22en-US%22%3EFiltering%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680044%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20with%20a%20large%20amount%20of%20data%20in%20Excel%20and%20have%20a%20question%20that%20seems%20pretty%20simple%20but%20has%20me%20stumped!%20I%20have%20a%20list%20of%20approximately%20108%2C000%20people.%20Each%20person%20has%20a%20column%20for%20name%2C%20address%2C%20city%2C%20state%2C%20phone%20number%2C%20etc.%20Each%20person%20also%20has%2010%20columns%20of%20data%20(columns%20H%20-%20Q)%20that%20refer%20to%20various%20information.%20A%20lot%20of%20the%20people%20didn't%20participate%20in%20certain%20events%20so%20their%20columns%20H%20-%20Q%20are%20blank.%20I%20would%20like%20to%20eliminate%20all%20of%20the%20people%20on%20the%20list%20that%20have%20no%20data%20in%20their%20columns%20H%20-%20Q%20to%20narrow%20down%20my%20list%20of%20people%2C%20but%20I%20can't%20figure%20out%20how%20to%20do%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1680044%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1680162%22%20slang%3D%22en-US%22%3ERe%3A%20Filtering%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680162%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F797774%22%20target%3D%22_blank%22%3E%40ashlee_baggett%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20easiest%20way%20to%20create%20a%20view%20that%20only%20shows%20the%20ones%20without%20blanks%20is%20to%20use%20the%20Data%20Filter%20capability.%20Assuming%20your%20data%20is%20organized%2C%20as%20it%20seems%20to%20be%20from%20your%20description%2C%20as%20a%20table--no%20blank%20rows%2C%20etc.%2C%20you%20can%20go%20through%20the%20various%20columns%20where%20you%20want%20to%20eliminate%20from%20sight%20the%20ones%20that%20are%20blank%20and%20make%20sure%20that%20the%20only%20ones%20you%20see%20have%20values%20in%20them.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1600365569313.png%22%20style%3D%22width%3A%20422px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219256i80C47F76E3F0E45C%2Fimage-dimensions%2F422x264%3Fv%3D1.0%22%20width%3D%22422%22%20height%3D%22264%22%20title%3D%22mathetes_0-1600365569313.png%22%20alt%3D%22mathetes_0-1600365569313.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20filter%20is%20there%20on%20the%20right.%20You%20may%20have%20to%20highlight%20all%20of%20your%20cells%20before%20selecting%20it...indicate%20whether%20or%20not%20you%20have%20headers%2C%20and%20then%20set%20the%20criteria%20for%20each%20column%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere's%20also%20a%20function%20in%20the%20newest%20versions%20of%20Excel--a%20function%20appropriately%20called%20FILTER--that%20can%20do%20the%20same%20thing.%20But%20since%20you're%20asking%20the%20question%20as%20you%20are%2C%20I'm%20of%20the%20opinion%20that%20that%20first%20feature%2C%20just%20using%20the%20menu%20or%20tool%20bar%2C%20is%20most%20appropriate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am working with a large amount of data in Excel and have a question that seems pretty simple but has me stumped! I have a list of approximately 108,000 people. Each person has a column for name, address, city, state, phone number, etc. Each person also has 10 columns of data (columns H - Q) that refer to various information. A lot of the people didn't participate in certain events so their columns H - Q are blank. I would like to eliminate all of the people on the list that have no data in their columns H - Q to narrow down my list of people, but I can't figure out how to do it!

2 Replies

@ashlee_baggett 

 

the easiest way to create a view that only shows the ones without blanks is to use the Data Filter capability. Assuming your data is organized, as it seems to be from your description, as a table--no blank rows, etc., you can go through the various columns where you want to eliminate from sight the ones that are blank and make sure that the only ones you see have values in them.

mathetes_0-1600365569313.png

The filter is there on the right. You may have to highlight all of your cells before selecting it...indicate whether or not you have headers, and then set the criteria for each column as desired.

 

There's also a function in the newest versions of Excel--a function appropriately called FILTER--that can do the same thing. But since you're asking the question as you are, I'm of the opinion that that first feature, just using the menu or tool bar, is most appropriate.

 

Hello @ashlee_baggett,

 

  • Filter each of the columns (H-Q) to show only (Blanks).
  • Then, press Ctrl+G and select Special. Select Blanks and press OK.
  • With all of the Blanks now selected, right click one blank cell and select delete. Select Entire row and press OK.