Jump to content

Filter Out Blanks in Excel


Recommended Posts

Filter Out Blanks in Excel

How can I format a list of data so that zero-value rows are skipped? Let's say I have quantities in column A, followed by an inventory list of items in column B. Many quantities are blank (or zero). On another worksheet I want to summarize the list, skipping items that have a zero quantity. Is there an Excel function or macro that can accomplish this?

You need to use Excel's data filter feature. Simply highlight the list, including the header row, and select Data | Filter | AutoFilter from the menu. A pull-down arrow will appear next to each header cell. To filter out just the rows with blank quantities, pull down the list for quantity and select (NonBlanks). To filter out rows with blank or zero quantities, pull down the list for quantity and select (Custom...). In the Custom AutoFilter dialog, choose is greater than from the pull-down list and enter 0 in the box to the right of this list. When you click on OK, only rows with nonzero quantities will be displayed.

When you copy the filtered data range and paste it elsewhere, the filtered-out rows will be omitted.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...