auto filter

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Auto Filter

Auto Filter

resolvedResolved · Urgent Priority · Version 2003

Amy has attended:
No courses

Auto Filter

I have a spreadsheet set up with auto filter set up along the top row. Below this are several rows with infomation in the cells. There is then one row left as a spacer and again several rows below this with information. When i select the 'show non blanks' option on the filter, it only applies this to the cells in the first block of rows and not those further below. Do you know why this may be?
I have another very similar spreadsheet set up with the same blocks of rows, and the filter is applied down through all rows.

Please advise.

Many thanks

RE: Auto Filter

Hello Amy

Thank you for your question.

The reason that the autofilter is only filtering the first block of rows is because of the blank row. If you can do without the blank row (e.g. you could create the space simply by making a row wider/higher to create the effect of a gap), then I would delete them. Otherwise you will have to select all the data before you use autofilters so Excel will filter all the data and not just down to the point where the first blank row occurs.

I hope this helps - let me know if you have any further questions.

Amanda

RE: Auto Filter

Dear Amy

Thank you for your question.

It is important to understand that when you enter data in Excel it is a range which means it is a continuous flow of data.

In Excel you normally would have a blank row or a column to separate the different groups of data.

I believe that is what is happening with your data.

I am sure there must be a reason for having a blank row for the spacer. But unfortunately because of this spacer it is treating the second block as a totally different range and not the same.

If you definitely don

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Applying and removing border from cell in Excel 2010

Did you know the shortcut key for applying and removing the outline border for a cell?

CTRL+SHIFT+& Applies the outline border to the selected cells.
CTRL+SHIFT_ Removes the outline border from the selected cells.

View all Excel hints and tips


Server loaded in 0.08 secs.